Applying Color Scales using Conditional Formatting

What is the Color Scales Option?

Color Scale is an option in conditional formatting which you can apply to a range of cells to highlight them with color shades according to the value in a particular cell. For example, cells with higher values will get green, and lower will get red.

In Excel, there are two ways to use color scales.

• Pre-Defined Color Scales
• Custom Color Scales

And in this tutorial, we will discuss both ways in detail.

Steps to Apply Color Scale in Excel

1. Select the range on which you want to apply it.
2. Go to Home, and then click on Conditional Formatting.
3. Go to the Color Scales and hover your cursor on the color scale you want to apply.
4. In the end, click on the color scale you want to apply.

There are 12 pre-defined color scales that you can use here. Each of these color scales is unique. And the color scales we have applied have three colors; green, yellow, and red, which means green for higher values, yellow for mid-range values, and red for lower values.

Note: If you want to hide the numbers from the color scales, open the format cells options (Ctrl + 1) and, click on the custom and enter ;;;; in the input bar, and then click OK.

Create a Custom Color Scale

Apart from the pre-defined scales, you can create a custom rule to apply the color scale to a range of cells. For this, you need to click on the “More Rules” in the color scales options.

When you click on it, it will show you a new dialog box where you can create a custom color scale.

You can choose “Format Style” from this dialog box to apply a 2-Color Scale or 3-Color Scale.

Once you select the scale you want to apply, further you have five ways to create a rule:

1. Lowest/Highest Value

It is the default way to create a rule, and pre-defined rules are based on this. It takes the lower and the higher value from the range you have selected and creates a rule based on that.

2. Percentage

With percentage, you can specify the percentage for lower, mid, and higher values (3-Scale), or lower and higher (2-Scale). For example, if you specify 10% in the lowest percentage, it will apply the darkest shade of the lower color to the lowest 10% values. And if you use 80% to the highest percentage, it will apply the darkest shade of the higher color to the top 20%.

3. Percentile

Percentile works differently to apply color scales. In the example below, we have used 80 for the highest value, which means the top 20th percentile. It will create a group of top values, and the exact same color shade will be applied to all those cells.

Same with the lowest percentile and 10th percentile lowest values. All the values in this group of values will get the exact color you specified in the minimum color.

Quick Links to Learn More – Link

4. Number

With 0the number, you can define a custom number by entering it. This option is great to use when using numbers as per a standard. Let’s say you want to apply color based on 0 and 100 as minimum and maximum values.

5. Formula

Sometimes, you might need to get the value by using a formula. Let’s say you want to apply the color scale to a column but want to get the minimum, midpoint, and maximum values from a different column.

Creating a Color Scale with More than 3 Color

We don’t have the option to create a 4-Color scale or more; in this case, you can create five conditional rules. Below we have a 5-color scale where we have used five different rules.

 Each of these five rules uses five different conditions.

These five formulas calculate five different slabs of numbers from the range.

1. Below 2000
2. From 2000 to 3999
3. From 4000 to 5999
4. From 6000 to 7999
5. Above 8000

And for each formula, there is a defined color to apply.