Cell Highlighting

Quick reference

• 00:05 We're now going to jump into working with tools, and
• 00:07 the first one we're going to reach to is conditional formatting.
• 00:10 Conditional formatting can be super useful if you have tabular data that you want to
• 00:15 put on your dashboard, and you want a rule that dynamically updates to change
• 00:20 the fill colors or different things to show whether values are good or bad.
• 00:24 We're going to start by setting up a top 10% rule
• 00:29 on prices to see which are the top 10% of prices in our data set.
• 00:33 So I'm going to scroll down and select my prices from B8 to B31, and
• 00:36 then I'm going to go up to the Home tab to Conditional Formatting.
• 00:40 All conditional formatting is set up from the Home tab.
• 00:43 I'm going to go pick Top/Bottom Rules, Top 10%.
• 00:47 It automatically picks up a rule for red fill with dark red text.
• 00:52 Now I don't know about you, but if you had to choose between red and green for
• 00:55 something that's in the top, I generally go with green.
• 00:58 So I'm gonna grab this and
• 00:59 say let's go with a green fill with dark green text, and now we'll say okay.
• 01:03 And notice that if I scroll through my data set, there are two values for 644.00.
• 01:09 Both are highlighted because they are in the top 10% of all the items in
• 01:12 the categories.
• 01:13 If I were to go back and change this value up to say 780.00,
• 01:16 notice that there's now three values that are going to show in the top 10%.
• 01:23 And if I were to go back and say hey let's recalculate this to make this 1500,
• 01:27 again, the same deal.
• 01:30 Now, I'm going to roll these guys back.
• 01:33 I want to move on to a top five rule.
• 01:36 So let's go pick up for gross sales.
• 01:43 I'll select my gross sales, I'll go to conditional formatting,
• 01:47 top bottom rules, and there's no top five rule, but if I go to top ten items,
• 01:52 you'll notice that I can dial this down to say just give me the top five items.
• 01:58 Again, do I want light red fill with dark red text?
• 02:01 Probably not.
• 02:02 that's okay for negative numbers, but in this case here,
• 02:05 I'm not really sure that that's what I want.
• 02:07 Now, the other thing that's probably worth throwing out there,
• 02:10 is the most common form of color blindness is red/green.
• 02:15 So if you're distributing your information to a wide variety of people and you don't
• 02:20 know whether or not you have someone who is in your audience that is color blind,
• 02:23 you may want to prepare for that.
• 02:24 So in this case here what I'm going to do is I'm going to use blue for good and
• 02:28 yellow for bad.
• 02:29 So at this point I can go and say let's go and grab, no there's no blue rule.
• 02:34 Well in that case I guess I'll have to go custom and build it myself.
• 02:38 So I'm going to go to the Font tab.
• 02:40 I'm going to choose to use a nice dark-blue font.
• 02:43 And I'll go to the Fill tab, and I'll chose to use a nice light-blue fill.
• 02:48 And at this point, when I say okay,
• 02:50 you'll notice that it changes my rule out to use a custom format.
• 02:54 And here we go.
• 02:55 Everything is good.
• 02:56 These are the bottom five items, or
• 02:58 sorry the top five items that are in my data set.
• 03:00 What if I wanted to grab the bottom five items and discounts?
• 03:04 Those are the largest negative numbers.
• 03:06 We'll go an we'll select our discounts.
• 03:10 We'll go to conditional formatting.
• 03:12 Go with the top and bottom rules.
• 03:14 We'll pick up the bottom ten items in order to get to the bottom five.
• 03:21 And I'll use the light red fill with the dark red, no I won't,
• 03:23 got to change that up and we'll go with the yellow.
• 03:27 And here we go.
• 03:28 You can say okay and now we've got a nice little rule.
• 03:30 Blue for good, yellow for bad.
• 03:32 There we are.
• 03:34 Now, next rule, greater than.
• 03:37 I want to highlight all records that are greater than $10,000.
• 03:41 So this one, I'm going to go and I'm going to grab my entire data set again,
• 03:47 conditional formatting, highlight cells.
• 03:50 So this is a little bit different.
• 03:51 And here's greater than.
• 03:53 What's different about this?
• 03:55 Well, we actually have a little drop down area or an arrow here.
• 03:59 This is actually a rough edit box, which allows me to click on it and
• 04:02 choose a cell value instead.
• 04:04 And this is really cool, because now I can come back and say,
• 04:07 you know I'd still like to use a custom format.
• 04:09 Just for grins, let's do something different.
• 04:11 Let's go with the green fill, and I'm going to go with a font that is white,
• 04:16 and we'll say okay.
• 04:17 And here we are, the rule is nicely done, and we'll say okay.
• 04:23 What's really nice about this one is that I can
• 04:26 now dynamically change this to say give me everything that's greater than $7,500,
• 04:30 actually let's go with 6,500, because that way we can see something on screen change.
• 04:35 And notice that the 7182 updates automatically.
• 04:39 This is not a feature, being able to link to cells for the top and bottom rules, but
• 04:43 it is for the greater than values.
• 04:46 What if I want to go and say, you know what?
• 04:48 I'm not worried about one of these rules right now.
• 04:50 This guy here on column B, I'm not worried about him at all.
• 04:53 Well I can go up to conditional formatting and I can go and
• 04:56 say clear, I can clear the rules from selected cells or the entire sheet.
• 05:00 The other thing that I can do is I can actually get in here to manage this rule.
• 05:05 If I decide that I don't like the color, here is the rule.
• 05:08 We can go back and say you know what?
• 05:10 That one really should have been, under format,
• 05:15 should have been my dark blue font on my light blue background.
• 05:19 And we'll say okay, and okay.
• 05:23 We can apply it from this window or it will automatically apply when we say okay.
• 05:27 So now you know how to actually create some rules.
• 05:29 We know how to dynamically update the value rules.
• 05:32 We can also clear them and
• 05:33 we can manage them to change them once they're already in place.

Lesson notes are only available for subscribers.