Color Scales

Quick reference

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