Locked lesson.
About this lesson
Applying dynamic heat maps to your data in order to show outliers and trends.
Exercise files
Download this lesson’s related exercise files.
Color Scales.xlsx29.8 KB Color Scales - Completed.xlsx
30 KB
Quick reference
Color Scales
Using Color Scales.
When to use
To show a heat map on your data.
Instructions
Creating Color Scales
- Select your data and go to the Home tab --> Conditional Formatting --> Color Scales
- Choose the Color Scale set you find most attractive
Modifying Color Scales
- Select any cell that holds the Color Scale
- Go to the Home tab --> Conditional Formatting --> Manage Rules
- Select your rule --> Edit
- Make your modifications
Hints & tips
- These rules are useful for showing trends or outliers
- You can set up rules that blend up to three colors
- Rules can be customized to have the blend occur earlier or later in the scale
- Try not to use obnoxious color combinations when building your scales!
- 00:04 The next additional formatting technique that we're going to look at is
- 00:08 what we call color scales.
- 00:10 This is something that isn't always on a dashboard, but
- 00:13 is often used to highlight things that are outliers and whatnot.
- 00:17 However having said that, if you're trying to put out a report and you're trying to
- 00:21 figure where your outliers are, this could be a useful technique there as well.
- 00:25 Now, I'm going to show you what happens here when we go and select an entire
- 00:30 range of data and we got to conditional formatting and color scales.
- 00:35 The nice this about this is it gives us live preview and
- 00:37 you can see what happens as we sort of mouse over something.
- 00:40 You'll notice that it gives us a heat map that actually shows us where the lowest
- 00:45 values are, in this case in red.
- 00:46 I'm going to move this actually to red and blue.
- 00:50 So, the lowest values here in red the highest values in blue.
- 00:54 And you'll notice that the highest value in blue is in the bottom right hand corner
- 00:56 because, it has the totals on it.
- 00:58 So, that's not really what we want here.
- 01:00 What I'm going to do is, I'm going to go an actually re-highlight
- 01:03 this range in the center, so ignoring my totals and subtotals.
- 01:08 I'm going to go to conditional formatting, color scales, and I'd like to actually go
- 01:12 and pick a green and white rule here that actually shows me the highest
- 01:17 values highest values highlighted in green and the lowest values in white.
- 01:21 And when I hit okay on them, what you'll notice is that this gives us a pretty good
- 01:25 indicator of which our best products are.
- 01:28 We can see that Product Six we've done very well with.
- 01:31 And Product Seven looks like it starts low, but it's starting to pick up speed.
- 01:35 What's really interesting about this particular data set for me is that if I go
- 01:39 back and say let's apply conditionally formatting rule with our top three items.
- 01:46 So we'll go and we'll say, let's do the top three, and
- 01:49 I'm going to highlight this in green fill with dark green text.
- 01:54 You'll notice that if I were to just pick off based on my totals,
- 01:57 my top three items, I would say hey, look at this.
- 02:00 Product five six seven, these are my killers,
- 02:02 these are the ones that I definitely want.
- 02:05 But by looking at this color scale, I can also see that there's something very,
- 02:09 very bad going on in this particular case.
- 02:11 If I've decided that I'm going to really focus on product six,
- 02:14 we can see that something has really tailed off in the last few years by
- 02:18 looking at this heat map.
- 02:19 So this is a really interesting way of looking at our data.
- 02:22 Now what else do we actually have in here that we can work with?
- 02:25 Well you know what, we can also go and
- 02:27 we can set up a separate color scale on the bottom, on the total.
- 02:33 We don't want to pull it into the overall data set because it pollutes these numbers
- 02:37 and it's going to make some of them look, well, basically it picks these guys up at
- 02:41 the highest which isn't really going to work for us, so it skews the entire set.
- 02:44 But if we do it individually, we can come back and says lets go and
- 02:47 put in a different color scale role, maybe we'll use something like red and blue.
- 02:52 So we've got our nice little reds down here that are showing really not so good,
- 02:56 and our blues here.
- 02:58 But again, remember the concept that we talked about.
- 03:01 Do we really want red and green also being used in our dashboard?
- 03:05 Maybe I want to change this to that yellowish orange.
- 03:09 So again, conditional formatting.
- 03:10 You can go into manage rules.
- 03:13 Even if I don't see the rule that I want, I can edit it.
- 03:16 I can go back and change the color choices out.
- 03:20 So now, I've got a rule that's going to look a little bit different.
- 03:26 What it does is it blends the data points.
- 03:28 Now one caution I would throw you out here, you can go into edit rule.
- 03:32 You can also pick off another color for the center.
- 03:36 So let's say that we wanted to put in something like, I don't know,
- 03:38 let's put in purple.
- 03:41 This is going to make some pretty horrendous color choices.
- 03:43 When we go back and we start looking at this,
- 03:45 it's going to be very difficult to read something that looks like this.
- 03:48 So you want to be really careful with your colors.
- 03:50 Colors should be used generally as an accent, not as the entire,
- 03:55 you don't want to use the entire rainbow spectrum on every spreadsheet you do.
- 03:58 That's just crazy.
- 03:59 It has the same.
- 03:59 Most of the time you want to go minimalist with white and
- 04:02 a little bit of color to really pop the individual components.
- 04:05 Certainly going in and making changes to the individual rules to get them suited to
- 04:10 what your company colors are, no problem with that whatsoever, that makes sense.
- 04:14 The other thing again, if you're working with these and
- 04:17 you're trying to work with them, notice that you can set your lowest values,
- 04:20 your midpoints, and your maximum values, you can set them to cells,
- 04:24 you have that option here in order to pick those things off.
- 04:27 Although, you would want to go in say formula in order to do that,
- 04:30 but you do have the ability in order to change those up and get these things so
- 04:33 that they split in the right places as well.
- 04:36 So, I'll just say okay and that looks pretty good for this particular case.
Lesson notes are only available for subscribers.