Focus video player for keyboard shortcuts
Auto
- HD
- 720p
- 540p
- 360p
1.00x
cc
- 0.50x
- 0.75x
- 1.00x
- 1.25x
- 1.50x
- 1.75x
- 2.00x
We hope you enjoyed this lesson.
Cool lesson, huh? Share it with your friends
About this lesson
Conditional formatting can highlight items based on rules you set and, as the data changes and other data meets the requirements, your highlights will change automatically.
Quick reference
Conditional Formatting
Conditional formatting is a useful tool to visually compare data to one another or to set values.
Icon sets
- Select your data
- On the HOME tab, styles grouping, conditional formatting button, icon sets and choose your icon style
- To make changes to the default, select your data, go back to conditional formatting and click on manage rules.
Data bars
- Select your data
- Home tab> Styles grouping > Conditional Formatting button, Data Bars and choose an option from here
Top 10
- Select your data
- Home tab> Styles grouping > Conditional Formatting button, Top/Bottom rules and choose an option from here
- Effects on the slicer
- Notice as you change options on the connected slicer, the conditional formatting shifts to dynamically highlight the options.
- 00:04 Nothing makes information stand out like a little bit of color, and
- 00:08 Excel has a tool that automatically helps you out with that.
- 00:12 It's called conditional formatting.
- 00:14 A conditional formatting is somewhat self-explanatory.
- 00:17 It's a tool that applies formatting to your data depending on the conditional
- 00:22 rules that you lay out.
- 00:23 It can be used in a number of ways, including visualizing your data and
- 00:28 checking for specific information.
- 00:31 You can generate rules for the following factors.
- 00:34 Is something empty or not empty?
- 00:37 Does text contain or not contain something?
- 00:40 Does text start with or end with something?
- 00:43 Is it exactly equal to something?
- 00:45 You can work with dates too.
- 00:48 If a date is, is it before, is it after something else?
- 00:52 Is a value greater than or equal to, less than or equal to?
- 00:56 Is it equal to or not equal to?
- 00:58 And does a value fall between or not between other values?
- 01:02 There are a whole bunch of factors that you can use.
- 01:06 And in this video,
- 01:07 we're going to use the traffic data from Google Analytics to demonstrate this.
- 01:12 So I've prepared a pivot table,
- 01:14 and I want to eventually only look at the top 15 countries.
- 01:18 I'm not going to use the row labels now.
- 01:20 I want to show you a nice trick that would further enhance your data, and
- 01:24 that is conditional formatting.
- 01:26 Now, what conditional formatting does is it will highlight top values,
- 01:31 it'll highlight differences, right?
- 01:33 So let's look at three examples.
- 01:36 On the variance column, for example, if I select that,
- 01:39 I go to the Home tab, Conditional Formatting, and
- 01:43 I can use an icon set to show whether my variance has increased or decreased.
- 01:49 There's all different shapes that you can use.
- 01:51 Let's use the arrows.
- 01:53 If I look at my data, it doesn't seem to quite behave like I think it should.
- 01:57 -50%, that's definitely a decrease, but
- 02:02 12% is an increase and 100% should not be a stay in value.
- 02:06 So let's see if we can change this rule a little bit.
- 02:10 If I select the data again, right, we go back to Conditional Formatting,
- 02:16 and the bottom option is Manage Rules.
- 02:20 I can click on the rule I just implemented, and
- 02:22 it implemented on the default value.
- 02:24 If we click on Edit Rule, we can see what's going on here.
- 02:28 So indeed, it has the icon set when the value is greater than or equal to 67%.
- 02:35 So the top third is green, the middle third is yellow, and
- 02:39 the bottom third is red.
- 02:41 That's not what we want.
- 02:42 I want the green arrows to show me all increases.
- 02:46 So we need to do a little trick here.
- 02:48 I'm going to use 0.01,
- 02:52 not percent, but a number.
- 02:56 Now, when it's between 0.01 and 0,
- 03:00 which is also a number, I just want it to show me the yellow arrow,
- 03:05 and then the red when it's smaller than 0.
- 03:08 This will give me what I want.
- 03:10 So now that we have these set, let's hit OK.
- 03:13 And then you can click Apply before we exit this screen,
- 03:17 see if it works, and it does.
- 03:20 Yes, there are two zero percents with yellow arrows, reds with negatives,
- 03:25 green with positive.
- 03:26 So that seems to be working.
- 03:28 Great, so let's hit OK.
- 03:30 As these numbers update, your conditional formatting will change.
- 03:35 Now, let's look at two more, and
- 03:37 then I'll show you how the whole thing looks a little bit compressed.
- 03:42 There are two other options we can use in a normal column like this.
- 03:45 For example, we can use the data bars.
- 03:48 So if I choose one with a gradient fill,
- 03:50 you'll see that these data bars appear in the column.
- 03:54 The bigger the data bar, the bigger the value.
- 03:57 So let's look around 3,018.
- 03:59 That's a pretty big value there, right?
- 04:01 So let's go up to the top, select the top value, Shift,
- 04:06 and then down until we've selected the whole column.
- 04:12 Now we go back up to Conditional Formatting.
- 04:15 We can choose Top Bottom Rules, and I can choose the Top 10 Items.
- 04:20 Let's say I don't want the top ten, I want to top three items, and
- 04:24 I want the top three to be displayed in green text with a green fill.
- 04:31 So we hit OK.
- 04:34 And here's one of the top values here, the United States.
- 04:37 But this list is kind of big.
- 04:39 There's a lot of countries.
- 04:40 Maybe I just want to make this a little bit smaller.
- 04:43 So on the row labels, we can go down to Value Filters and choose the Top 10 there.
- 04:49 Maybe I want to look at the top 15 countries.
- 04:52 Let's see how that looks.
- 04:53 That's nice, that's much better.
- 04:55 See, now I can hide my row label and column label inside the pivot table.
- 05:01 On the Pivot Table Analyze tab, I can just click Field Headers, make that disappear.
- 05:08 That looks a lot better.
- 05:09 And there's my data bars.
- 05:11 And there's my top three countries in green and all my increases and decreases.
- 05:16 The nice thing about conditional formatting is, as you change your slicer,
- 05:21 you can see that the data changes, and so does the conditional formatting.
- 05:27 So you can use conditional formatting to visualize month over month marketing
- 05:31 statistics, highlight link-building opportunities by difficulty, color code,
- 05:36 content calendars, and so much more.
Lesson notes are only available for subscribers.