Conditional Formatting: Using the Icons

Quick reference

• 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.