Locked lesson.
About this lesson
This lesson illustrates the basic options of conditional formatting with 4 different examples.
Quick reference
Conditional Formatting
Conditional formatting is used in this example to visually compare data, conditional formatting is dynamic and will change as your data changes.
When to use
Conditional formatting can be used where ever you need to dynamically and visually emphasize a value(s).
Five examples
Highlight values that is greater than, smaller than, etc to a set value
- Highlight your values
- Home, styles grouping, conditional formatting, highlight cells, greater than (example), select your value used for comparison and choose the highlight fill and text color
Top or Bottom values
- Select your values
- Home, styles grouping, conditional formatting, top/bottom rules, top 10, change to top 3
Data Bars
- Select your values
- Home, styles grouping, conditional formatting, data bars, and select
Icons
- Select your values
- Home, styles grouping, conditional formatting, icon sets
- To modify the rule - Home, styles grouping, conditional formatting, modify rule
Highlight duplicates
- Select your values
- Home, styles grouping, conditional formatting, highlight cells, duplicate values
- 00:04 Conditional formatting applies formatting to a cell or
- 00:08 a range of cells based on a condition to make the cells standout visually.
- 00:13 For example, if a cell value is larger than a set value, it can make the cell
- 00:18 background and font color different than the rest of the cells and the range.
- 00:23 It can highlight the top three values in a range, or
- 00:27 the bottom five if you like, or it can visually compare a range of entries.
- 00:32 The big advantage of conditional formatting is that it's dynamic, and
- 00:36 it will change with your data.
- 00:38 So let's look at the menu options for conditional formatting.
- 00:42 Conditional formatting lives on the Home tab in the Styles grouping right at
- 00:46 the top.
- 00:47 The three bottom options are the core options where you can set,
- 00:52 clear, and manage rules.
- 00:54 The available icons above are preset rules for easy and popular options.
- 00:59 In this video, we can play with the easy options to get a feel for how conditional
- 01:04 formatting works, and to let you think about how it can work for you.
- 01:08 In the next video, we'll try to customize some of the options and
- 01:11 see if we can sort out slightly more complex situations.
- 01:14 So let's begin.
- 01:16 This sheet contains four examples for the same monthly revenues for a business.
- 01:22 In example one, let's say we want to emphasize all months where the revenue
- 01:27 exceeds the average revenue, and
- 01:29 the average has already been calculated at the bottom.
- 01:32 First we select the cells that need the formatting, that's our revenue column.
- 01:37 We highlight the cells, go to the Home tab > Styles grouping >
- 01:41 Conditional Formatting, and we want to highlight.
- 01:44 Select the greater than option and click on this arrow to select
- 01:48 the average that we've calculated as the acceptable value.
- 01:53 When we click on the field, we already see some of the cells in that column have
- 01:57 turned red because they're greater than the average.
- 02:00 But I don't like red,
- 02:01 let's say we want to format those with yellow fill and dark yellow text.
- 02:06 When we click OK, the revenue greater than average now has the proper formatting.
- 02:12 In example two, we want to emphasize the top and bottom three values.
- 02:17 We need to apply two rules to accomplish this.
- 02:21 So let's go back to Conditional Formatting > Top and Bottom rules, and
- 02:25 click on Top 10 items.
- 02:27 But don't worry, we can change that number to be the top three,
- 02:31 we just type the three into the box.
- 02:34 And let's change the highlight style to green fill with dark green text.
- 02:38 Hit OK and now we have our top three.
- 02:41 But let's repeat the process for the bottom three.
- 02:43 Conditional Formatting > Top and Bottom rules > Bottom 10 items.
- 02:50 Change the 10 to a 3, we can leave the formatting
- 02:55 option as red with red text, hit OK, and there we go.
- 03:00 We have our top three and our bottom three values.
- 03:03 In example three, let's try out some data bars.
- 03:07 Select the range, go to Conditional Formatting and Data Bars.
- 03:11 If you have the mouse pointed over the options,
- 03:15 you can watch the cells below to preview.
- 03:18 We have gradient fills and solid fills.
- 03:22 I'm going to choose the blue gradient.
- 03:25 And now you can see by the data bars how each month compares to the other.
- 03:29 December had the biggest sales, June had the lowest.
- 03:32 So those bars reflect the amounts, and that's nice to see at a glance.
- 03:38 And now example four, I'd like to show you icon sets.
- 03:42 Let's highlight the values, go to Conditional Formatting and Icon Sets.
- 03:47 We have directional, shapes,
- 03:51 indicators, let's choose directional.
- 03:56 Now at first, these arrows might not make sense to you, but
- 04:00 let's check the rules that control why each icon appears.
- 04:05 Go to Conditional Formatting > Manage Rules, and
- 04:08 you'll see that we can click Edit rule to access the rules.
- 04:13 We've got three icons, the green up arrow, the yellow right arrow, and
- 04:18 the red down arrow.
- 04:19 Right now, the rules state that we show a green up arrow if the value is in the top
- 04:24 third of the values, show a yellow if it's in the middle third, and
- 04:29 a red arrow if it's in the bottom third.
- 04:31 Okay, this makes more sense to me now.
- 04:34 And you can change the rules if you want to reflect different kinds of
- 04:37 benchmarks for your own data.
- 04:39 Now one last thing I'd like to show you is over here, we have two April's,
- 04:43 and that's clearly a mistake.
- 04:45 Conditional formatting can be used to indicate such mistakes.
- 04:50 Highlight your values, go to Conditional Formatting, and
- 04:54 we want to highlight duplicate values.
- 04:57 It will quickly show you if there's something wrong.
- 05:00 And it turns out that that first April is supposed to be March.
- 05:04 So when you fix it, that duplicate highlight disappears.
- 05:08 And if any duplicates occur again, the value will highlight,
- 05:12 so you can fix the problem.
- 05:13 So you've got a variety of ways to apply conditional formatting to your data.
- 05:18 Happy formatting.
Lesson notes are only available for subscribers.