Color scales display a range of cell color backgrounds based on cell value—making your data easy to spot. From a Green - Yellow - Red Color Scale to more subtle tones, there are 12 color combination options available.
- [Instructor] Another Conditional Formatting option that lets your numbers really stand out prominently is what we call Color Scales. Now let's imagine we want to give some emphasis to the sales numbers that we see in Column E, select the entire column, Conditional Formatting, Color Scales. As I slide over the first option, you'll notice almost immediately the lower numbers are redder, the higher numbers are greener, and lots of color in between. When you slide over these, off to the right you'll see the description, Green Yellow Red Color Scale.
There's more verbage below that about why you might want to use that. Now if we use this, by the way, notice that red yellow green is a common scheme, a traffic-light pattern, many times we associate green, green light's go, that's better, that's good, that's high, that's what we see here. The button just to the right of it is the reverse. Sometimes that makes sense. And we've got some other options out here. Sometimes the color differences are pretty subtle and so in some cases and in some environments, maybe not all these are going to work best for you.
But as we slide over the choices, Excel's so-called Live Preview capability gives you an idea of what these are going to look like. I'm going to use the first option there, the traffic-light pattern where green is highest and red is lowest. And just to show how this might look and to point out that it's really not three colors you're seeing here but actually a continuum of colors from dark red to green or the reverse, dark green to red, I'll sort the data here simply by clicking anywhere in Column E, the data's all in one solid cluster, and I'll go to the Data tab and simply choose ZA, high to low, and there we see the greens.
And as I start to scroll down the list, you see how the greens are getting lighter and we're approaching yellow, getting into yellow, they get a bit brighter, we're looking at orange, and we get down to the reds. So if you were displaying the list this way, I don't think using Color Scales is going to be that effective, in other words, if it is sorted by this content. So I'll undo that with Control + Z, go back to this, I think it's more effective in a situation where the data's not sorted on that field. But nevertheless, lots of options there.
Now, the data to the right represents numbers related to if you want to borrow this amount of money and here are the various interest rates, maybe this is for a house. Here are the monthly payments based on a 360-month loan so every formula here relates to that 360 as well as to one of the numbers in Column J and one of the numbers across Row 2. And we see the numbers here and there. But let's make these stand out a bit better by using Color Scales.
And what we're about to show you is sometimes referred to as a heat map. So I've got the numbers highlighted. And here, too, I'll probably use, in this case, green for low, in other words, let's say it's better to pay less, so we'll have green being on the good side, so to speak. And once again, we go to the Home tab, and this is not, of course, for a specific column, it's for a range of cells across multiple columns and rows. Conditional Formatting and the preview will pretty much tell us how things are going to look.
Color Scales, there's the first option. Notice that the more expensive entries, or in other words when we're borrowing more and/or the interest rate goes up, green is highest, I'll use the opposite right there. And again, of course the numbers are more important than the colors, but on the other hand, it's easier to read than that boring set of numbers and now I have a rough idea, we can compare numbers in different categories there, the so-called heat map. That's not an official term in Excel, but you hear that used sometimes when data's being displayed this way.
Perhaps a more creative use of Conditional Formatting using the example here as we showed earlier, Color Scales.
LinkedIn Learning (Lynda.com) is a PMI Registered Education Provider. This course qualifies for professional development units (PDUs). To view the activity and PDU details for this course, click here.
The PMI Registered Education Provider logo is a registered mark of the Project Management Institute, Inc.
- Conditional formatting based on content
- Formatting based on formulas
- Using data bars to display length or volume
- Using color scales to display relative differences
- Highlighting PivotTable results
- Applying banded rows