How to use color scales with conditional formatting

When using color scales in conditional formatting, Excel assigns one color to the lowest value, and another to the highest value. Other values are assigned a weighted blend of color. This makes it easy to see general patterns in data, especially with a large data set.

Let's take a look.

Here we have a large table that shows  average monthly temperatures for Salt Lake City from 1949 through 2012. Let's use a color scale to help visualize the numbers.

There are twelve presets in the Color Scales category of the Conditional Formatting menu. The first six presets are 3-color color scales, and next six presets are 2-color color scales.

For all presets, the color at the top is applied to the higher values in the selection, and the color at the bottom is applied to the lower values in the selection. Excel builds a live preview on the worksheet to help you understand which preset is the best fit for your data.

For temperatures, the red-yellow-green preset is a good starting point. Once we select this option, the format is applied immediately to all values in the selection.

Like other conditional formats, the color scale will appear as a rule that can be edited when selecting Manage Rules.

Color scales can be set using Numbers, Percents, Formulas, or Percentiles. By default, Excel will assign the lowest value in the selection to the Minimum, and the highest value in the selection to the Maximum.

If we override the default, and use the number 35 for the Minimum, and the  number 75 for the maximum, we'll see much more solid green for colder temperatures, and more solid red for warmer temperatures.

Excel's color-scale presets tend to be pretty bright, but you can edit a rule to get a more subtle effect. One option is to assign white to the Midpoint, then set the colors for Minimum and Maximum to lighter shades.

If you care mainly about either high or low values, you can also edit the rule and switch to 2-color scale, then use white for values that don't matter as much.

The result can be quite subtle and yet make the numbers much easier to understand at a glance.