Conditional Formats with Built-In Rules

Quick reference

• 00:04 In this worksheet I have some fictitious production and
• 00:07 revenue figures for some Scottish whiskey distilleries.
• 00:10 And what I would really like to do here is I'd like to go and
• 00:13 do some visual analysis of this to try and pull out some key information around it.
• 00:18 The first thing that I'm interested in is figuring out which are the oldest three
• 00:22 distilleries in my list.
• 00:24 So what I'm going to do is I'm going to grab this column of values here,
• 00:28 and I'm going to go to Conditional Formatting, top bottom rules, and
• 00:32 I'm going to choose to grab the bottom 10 items, even though I only want three.
• 00:37 And what you'll notice is that it comes up with 10, but
• 00:40 this is a dialable area here where I can change the number of values.
• 00:43 And what you'll see is, as I start dialing this down, we're getting to six to five,
• 00:47 we can see that the number of items that has been highlighted is decreasing.
• 00:51 So if I get down to three, we can see our earliest three distilleries.
• 00:55 Now typically with Scotch whiskey, what we assume is that older is generally better.
• 00:59 So this red on red is kind of not really the message I want to send, so
• 01:03 I'm just going to reconfigure this to use green fill with dark green text, and
• 01:07 we're going to go and say, OK, and there we go.
• 01:11 Now, the cool thing about this stuff though,
• 01:13 is that all of this is dynamic based on the data range we're working with.
• 01:17 So for example, if we assumed that maybe Ardbeg, the data was wrong here and
• 01:22 they were actually founded in 1822, when I hit enter,
• 01:25 what you'll see is that that dynamically says, well,
• 01:28 Ardbeg is no longer one of the oldest distilleries, but Lagavulin is.
• 01:33 So that's kind of neat.
• 01:34 If I press Ctrl+Z, it goes back to where it was.
• 01:36 So all of this conditional formatting is formatting based on specific conditions in
• 01:41 your data set.
• 01:43 All right, for the next one, let's highlight all of our productions where we
• 01:47 actually have more than 90,000 bottles in a year.
• 01:50 So I'm going to grab this, go to Conditional Formatting, and
• 01:53 this time we're going to use a highlight cell rule, and
• 01:55 I'm going to choose specifically the greater than rule.
• 01:58 Now you'll notice that it actually goes in here and it puts in a value for me, and
• 02:02 that's great.
• 02:02 And it highlighted those off suggested based on an algorithm.
• 02:06 But what I'm actually more interested in is if I can click over here and
• 02:10 grab cell C7.
• 02:11 This will allow me to go back,
• 02:13 I'm going to change the color again to green fill with green text, and say OK.
• 02:19 And what's really nice about this is that I can now come back here and say,
• 02:22 show me all the production that's greater than 75,000.
• 02:26 And right from a cell, I can dynamically control what we're actually looking at.
• 02:30 So that's pretty darn cool.
• 02:32 All right, so we've seen a top and bottom rule and
• 02:35 a highlight cells rule, the next one I want to show you is Data Bars.
• 02:39 I'm going to select all of the values in this range here for our annual revenues.
• 02:43 We're going to go to Conditional Formatting, Data Bars,
• 02:46 and we're going to choose the color that speaks to us the most.
• 02:49 You know what for today, I feel like I'm going to go with yellow.
• 02:52 And what you'll notice is when I apply this, it immediately goes and puts bars in
• 02:57 the cell that are representative based on the data that's here.
• 03:01 So we can see very, very quickly that the largest number is 11,917,000, or at least
• 03:06 it's very, very close to this 11,819, because the bars go all the way across.
• 03:12 But this one here, Bunnahabhain,
• 03:13 is actually the lowest seller from an annual revenue standpoint.
• 03:17 Again, these are all fictitious numbers,
• 03:19 I have no idea what their real sales actually are.
• 03:22 Finally, the last one I want to take a look at the rankings that I have here.
• 03:26 So we assume that somebody has done a bunch of whiskey drinking and
• 03:29 they've come back, and one of these whiskies is very plainly their favorite,
• 03:32 so let’s figure out which one this is.
• 03:35 So what we're going to do is we're going to apply another type of data or
• 03:39 conditional format, which is called an Icon Set.
• 03:42 And it just seems like this would be crying out for a star rule.
• 03:47 Now, what you'll notice about this is that it immediately goes and
• 03:50 scans the dataset and comes back with some rules, and it's identified our top two.
• 03:54 So we've got Caol Ila at 10, and we've got Laphroaig at 9.8.
• 03:58 So those are right near the top of the dataset.
• 04:00 We have a couple of half stars at 8.9, and
• 04:03 then we have some empty stars at 8.6 and lower.
• 04:07 But I want to actually change this, I only want to give a half a star to anything
• 04:11 that's over 9, anything below that it's going to get an empty star.
• 04:16 My standards are pretty high here.
• 04:18 So what I'm going to do is I'm going to go and I'm going to modify this rule.
• 04:22 So I don't actually have to select all of these, I only need to select one,
• 04:25 and then I'm going to go to Conditional Formatting, Manage Rules.
• 04:29 For reference, if you ever need to wipe out rules, you can do that from clearing
• 04:33 from the entire sheet or from selected cells right here.
• 04:36 For me right now I'm going to go Manage Rules, and it pulls up this rule here.
• 04:40 I'm now going to say Edit Rule, and
• 04:41 what you'll notice is that these are formatted based on percentages.
• 04:45 I'm going to change both of these two numbers.
• 04:49 And what I'm going to do at this point is I'm going to link these to say,
• 04:53 we're going to put in a half star if it's greater than 9, and
• 04:56 you only get a full star when you're greater than or equal to 10.
• 05:00 So I can take very explicit control over my dataset rules here.
• 05:05 And we can say OK, and apply, and OK.
• 05:09 And at this point, you'll notice the 10 gets a full star, the 9.8 gets a half, and
• 05:13 anything 8 or lower gets no stars at all.
• 05:16 The other thing you should know about conditional formatting rules is you can
• 05:20 apply multiple rules to an individual cell.
• 05:23 So if I wanted to go and say, hey, you know what, let's do a highlight on this
• 05:26 one here where the values are greater than 7.3, or 7.6 million.
• 05:30 You can see that we've got some red rules showing up in behind these as well
• 05:34 combining with the data bar, so
• 05:36 multiple rules on a given cell are actually permitted.

Lesson notes are only available for subscribers.