Conditional Formatting using Color Scales

Today, we are going to learn a very interesting topic. Conditional Formatting is used to format the cells based on some condition. Color Scales are one of the methods using which we can apply conditional formatting to our data set.

So let us begin learning.

What is Color Scale?

Color Scale colors the cells based on the magnitude of the value it contains. We can apply conditional formatting using these color scales.

Grab the practice workbook from here.

Microsoft Excel defines two types of color presets – 2 color scale and 3 color scale.

color scales preset - 2 color scale and 3 color scale - Excel example 1

This is a 3-color scale ( Min, Max, and Mid ) where the maximum value is 10 (A3). It is highlighted with dark red while the minimum value is -10 in cell A2 which is highlighted in Blue. The different shades of both colors indicate the value a cell contains. The values lying in the middle of the maximum and the minimum value are in white. There are values close to zero.

We can customize a color scale very easily.

color scale infographics

How to Add a 2 Color Scale? – Conditional Formatting

let us suppose we have got the grades of different students as follows.

highlighting the cells based on values

To highlight these cells based on the marks, we can use the two color scale. Implement the following steps.

  • Select the range of cells A2:E8.
  • Go to the Home tab on the ribbon. In the Styles Group, click on the Conditional Formatting Button.
adding two color scale to marks in excel step 1
  • Choose the white red color scale.
white red color scale in excel

This would format the different subject marks such that the highest marks would be in white color and the lowest marks in red.

white red color scale in excel result

So you see, now we can easily figure out the marks that are result spoilers and are lowest or need improvement. Darker shades of red are highlighting the lower values.

How to Add 3 Color Scale?

3 color scale would contain three colors to highlight the cell contents. Let us suppose we have the percentage of monthly revenue for 5 years.

adding 3 color scale raw dat

To add a 3 color scale, follow these steps:-

  • Select the range of cells A2:F13
  • Click on the Conditional Formatting button under the styles group in the home tab.
  • Click on More Rules in the Color Scale Section. Note that you can either make your own rule for color scale regarding formats and min, mid and max values or you can just pick a 3-color preset.
make your own color scale in excel
  • This opens the New Formatting Rule Dialog box.
adding three color scale to marks in excel step 3
  • Choose the 3 color scale in the Format Style Field. Change the Midpoint value to the 15 percentile as the percentage of revenue lies between 1 and 30 percent. Click OK.
adding three color scale to marks in excel result

As a result, the lower values are in red, the middle values are in yellow, and the higher values are in green color.

This brings us to end. Thank you for reading.

Leave a Comment