Conditional Formatting to Dynamically Add Color to Reports

Quick reference

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