by Alexander Frolov, updated on
This tutorial will teach you how to conditionally format cells in Excel using gradient color scales to visually compare values in a range.
Excel conditional formatting is all about visualizing data with colors. You can use contrasting colors to represent data categories or gradients to "map" data with some intrinsic order. When a certain pallet is used to visually represent data, it becomes a color scale.
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:
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.
To add a color scale to your worksheet, follow these steps:
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.
If none of the predefined scales suits your needs, you can create a custom scale in this way:
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.
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 3rd 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 3rd 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.
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?
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:
That's all there is to it! Now, Excel shows only the color scale and hides the numbers:
This is how to add color scales in Excel to visualize data. I thank you for reading and hope to see you on our blog next week!
Using color scales in Excel - examples (.xlsx file)
Table of contents