Locked lesson.
About this lesson
Learn to create top/bottom and highlight cell rules, as well as apply icon sets and data bars.
Exercise files
Download this lesson’s related exercise files.
Built In Conditional Formats - Begin.xlsx28.2 KB Built In Conditional Formats - Complete.xlsx
28.8 KB
Quick reference
Conditional Formats with Built-In Rules
Creating top/bottom and highlight cell rules, as well as applying icon sets and data bars.
When to use
Conditional formatting is used to highlight key pieces of data that meet certain conditions. It reacts dynamically, updating when the underlying data is changed.
Instructions
Creating Top/Bottom Rules
- Select C6:C11, go to the Home tab > Conditional Formatting > Top/Bottom Rules > Top 10
- Change the 10 to 2, and change the format to “Green Fill with Dark Green Text”
- Click OK and the top 2 items will be highlighted
- Go back to Conditional Formatting > Top/Bottom Rules > Bottom 10
- Change the 10 to 2, and change the format to “Light Red Fill with Dark Red Text”
- Now the bottom 2 items will be highlighted
- Change C9 to 10,000 and notice the formatting updates
Creating Highlight Cells Rules
- Select D6:D11
- Go to the Home tab > Conditional Formatting > Highlight Cells Rules > Greater Than...
- Set address value to $D$4 and apply Yellow Fill with Dark Yellow Text
- Change D4 to 25,000 and notice only one cell is highlighted
Creating Icon Sets
- Select E6:E11 and go to the Home tab > Conditional Formatting > Icon Sets
- Pick the stars
- Notice the top 2 will have full stars, one will have a half star, and the others have empty stars
Creating Data Bars
- Select E6:E11 and go to the Home tab > Conditional Formatting > Data Bars
- Choose your favorite color
- Notice that the bars show the numbers in proportion to the others in the selected range
- 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.