Conditional Formats with Built-In Rules

Locked lesson.

Upgrade

About this lesson

Learn to create top/bottom and highlight cell rules, as well as to apply icon sets and data bars.

Lesson versions

Multiple versions of this lesson are available, choose the appropriate version for you:

2013, 2016, 2019/365.

Quick reference

Topic

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 meets 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 formats update

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 empty stars

Creating Data Bars

  • Select E6:E11 and go to the Home tab > Conditional Formatting > Data Bars
  • Choose your favourite colour
  • Notice that the bars show the numbers in proportion to the others in the selected range
Login to download
  • 00:04 In this example we're going to look at built in Conditional Formatting rules. Conditional Formatting allows us to go and apply colour
  • 00:11 rules or different types of rule sets to our data to help get some business insights out of it.
  • 00:17 They're also dynamic and they react to changes in the data always updating for us which is really cool.
  • 00:23 so the first thing I want to do is on the section from C6 through C11 I'd like to
  • 00:27 highlight the top two items. That's really easy to do. On the Home tab under Conditional Formatting
  • 00:32 we go to Top/Bottom rules. We are going to grab the Top 10 Items.
  • 00:37 What we're going to do now is we're going to change the 10 to a 2
  • 00:42 and you can see that the live previews are already showing what this will look like, it has
  • 00:45 highlighted the top two items. Personally though I would prefer to have my top two
  • 00:49 in green. Because I think green is a good thing and red is generally not as much.
  • 00:54 I will say OK to that. What if I want it to highlight the bottom two items? I can just go and apply another
  • 00:59 conditional formatting rule right over top, I'll grab the Bottom 10.
  • 01:04 And again we'll go and we'll pick up two
  • 01:07 and this one we'll leave as red. As you can see they're both highlighted so we can now see the top two and the bottom two and the really
  • 01:14 cool thing about this is that if I went and changed the rule here to something else
  • 01:19 let's go with 10,000 you can see the rules dynamically change to reflect that which is awesome.
  • 01:25 We can also set up rules to see if cells are greater than a certain value. So if we go to
  • 01:31 Conditional Formatting and we go to Highlight Cells greater than.
  • 01:36 In this particular case it will say well I'm going to feed a hard coded value here. What I'm going to do is click the RefEdit box
  • 01:42 and I'm going to go and select this cell so I can actually get whatever the value is in this cell instead. And maybe I want to go and change
  • 01:49 this one to yellow with the dark yellow text and get something slightly different.
  • 01:54 Again if I wanted to go in and change this,
  • 01:57 maybe I want to have where it's greater than 24,000.
  • 02:01 Well, it's going to give me the same values what about 25,000?
  • 02:04 So I can do some scenario modeling to try and figure out what I'm actually looking for and find the values that are outside of my range.
  • 02:13 We also have icon sets and icon sets are really cool little things that they give us a little bit more flair and pizzazz for our
  • 02:20 conditional formats. As you can see as I'm trying to apply these here there's arrows that are going in on the data right now.
  • 02:27 Unfortunately I wish we could get a few more rules from these things but they only give us these built in rules there is no way to upload our
  • 02:33 own. But maybe we want to put in and try and figure out who's our real stars. We can do that, ther you go.
  • 02:37 We have some nice stars, these are our top two performers
  • 02:41 this one's next and these other guys are at the bottom of the overall list.
  • 02:46 The last conditional formatting trick that I want to show you here is Data Bars and Data Bars are also a really handy way to visualize data.
  • 02:54 Again under Conditional Formatting if we go to Data Bars you can pick up one say with a blue fill here.
  • 02:59 And when we apply that you can see the numbers as they are in
  • 03:03 proportion to the other ones in the dataset. So you can see that we've got three values
  • 03:07 here that are relatively low compared to this value which has got the longest bar and its obviously the biggest value in the set at 26,570
  • 03:15 the next closest is 24,877
  • 03:17 and 24,465. So some handy little tools there that can really rock up that spreadsheet,
  • 03:26 make it much prettier but also give it some incredible business insights in the process.

Lesson notes are only available for subscribers.

Gift this course