Excel conditional formatting Color Scales

Color scales in Excel

A color scale is a sequence of smoothly changing colors that represent smaller and larger values. They come in especially handy to visualize relationships between numerical values in large datasets.

A typical example is heat maps that are widely used by analysts for discovering generic patterns and trends in various data types such as air temperatures, stock quotes, incomes, and so on.

There exist three main types of color scales:

• Sequential - gradients of the same hue going from light to dark or the other way round. They are best suited for visualizing numbers that go from low to high. For example, a medium green color says: "this value is a bit higher than the light green but lower than the dark green".
• Diverging, aka bipolar or double-ended - they can be thought of as two opposite facing sequential color schemes combined together. Diverging shades reveal more differences in values than sequential colors. They are perfect for visualizing frequencies, priorities, perceptions, or behavioral changes (e.g. never, rarely, sometimes, often, always).
• Qualitative or categorical - these are a few different colors such as red, blue, green, etc. They work nice for representing data categories that have no inherent order like industries, territories, species, etc.

Microsoft Excel has a number of preset 2-color or 3-color scales, which can be customized to your liking. Also, you can create a custom scale with a palette of your choosing.

How to add color scale in Excel

To add a color scale to your worksheet, follow these steps:

1. Select a range of cells that you want to format.
2. On the Home tab, in the Styles group, click Conditional Formatting.
3. Point to Color Scales and choose the type you want. Done!

For example, here's how you can use a 3-color scale (red-white-blue) to "map" air temperatures:

By default, for 3-color scales, Excel uses the 50th percentile, also known as median or midpoint. The median divides the dataset into two equal parts. Half of the values lie above the median and half are below the median. In our case, the cell that holds the median is colored white, the cell with the maximum value is highlighted in red, and the cell with the minimum value is highlighted in dark blue. All other cells are colored proportionally in different shades of those three main colors.

The default behavior can be changed by editing a preset color scale or creating your own one:

To modify an existing color scale, select any of the formatted cells, click Conditional Formatting > Manage Rule > Edit, and then choose different colors and other options. For more details, see How to edit conditional formatting rules.

To set up a custom color scale, please follow the below example.

How to make custom color scale in Excel

If none of the predefined scales suits your needs, you can create a custom scale in this way:

1. Select the cells to be formatted.
2. Click Conditional Formatting > Color Scales > More Rules.
3. In the New Formatting Rule dialog box, configure these options:
   • In the Format Style dropdown box, select either 2-Color Scale (default) or 3-Color Scale.
   • For Minimum, Midpoint and Maximum values, pick the data type (Number, Percent, Percentile, or Formula), and then choose the color.
4. When done, click OK.

Below is an example of a custom 3-color scale based on percentage:

Minimum is set to 10%. This will color the bottom 10% values in the darkest shade of the color you chose for the minimum value (lilac in this example).

Maximum is set to 90%. This will highlight the top 10% values in the darkest shade of the color chosen for the minimum value (amber in our case).

Midpoint is left default (50th percentile), so the cell containing the median is colored white.

Excel color scale formula

In Microsoft Excel, you would typically use the MIN function to get the lowest value in the dataset, MAX to find the highest value, and MEDIAN to get the midpoint. In conditional formatting color scales, it makes no sense to use these functions as the corresponding values are available in Type dropdown boxes, so you can simply pick them. In some situations, however, you may want to define the threshold values in a different way using other formulas.

In the below example, we have average temperatures for two years in columns B and C. In column D, the percent change formula returns the difference between the values in each row:

=C3/B3 - 1

The differences are conditionally formatted using a 2-color scale based on these formulas:

For Minimum, the SMALL function returns the 3^rd smallest value. As a result, the bottom 3 numbers are highlighted in the same shade of beige.

=SMALL($D$3:$D$16, 3)

For Maximum, the LARGE function brings the 3^rd highest value. As a result, the top 3 numbers are colored in the same shade of red.

=LARGE($D$3:$D$16, 3)

In a similar manner, you can do conditional formatting with 3-color scale formulas.

How to create 4-color scale and 5-color scale in Excel

Conditional formatting in Excel only provides 2-color and 3-color scales. No preset rules for multi-color scales are available.

To emulate 4-color or 5-color scale, you can create a few separate rules with formulas, one rule per color. Please note, the cells will be formatted with the distinct colors of your choosing and not gradient colors.

Here are the detailed instructions to set up a conditional formatting rule with formula. And here are formula examples to mimic a 5-color scale:

Rule 1 (dark blue): lower than -2

=B3<-2

Rule 2 (light blue): between -2 and 0 inclusive

=AND(B3>=-2, B3<=0)

Rule 3 (white): between 0 and 5 exclusive

=AND(B3>0, B3<5)

Rule 4 (light orange): between 5 and 20 inclusive

=AND(B3>=5, B3<=20)

Rule 5 (dark orange): higher than 20

=B3>20

The result looks pretty nice, isn't it?

How to show only color scale without values

For color scales, Excel does not provide the Show Scale Only option like it does for Icon Sets and Data Bars. But you can easily hide numbers by applying a special custom number format. The steps are:

1. In your conditionally formatted data set, select the values you want to hide.
2. Press Ctrl + 1 to open the Format Cells dialog box.
3. In the Format Cells dialog box, go to the Number tab > Custom, type 3 semicolons (;;;) in the Type box, and click OK.

That's all there is to it! Now, Excel shows only the color scale and hides the numbers:

Practice workbook for download

Using color scales in Excel - examples (.xlsx file)