Excel Color Scales (Add, Customize & Remove)

Get FREE Advanced Excel Exercises with Solutions!

In this article, we will talk about and show how to work with Excel color scales. We will do the task using Conditional Formatting. We will go through some easy methods to thoroughly describe the topic for your better understanding.

The color scale feature of Excel is widely used to visually represent and analyze data patterns within a range of cells. It helps users quickly identify trends, variances, and outliers in their data sets by assigning colors to data points based on their relative values.

Download Practice Workbook

Color Scale.xlsx

Table of Contents Expand

How to Add Color Scale to Cells

The primary way to add color scales to Excel cells is by using Conditional Formatting.

First of all, we will select cells C5:C14 >> go to the Home tab.
Then, from the Conditional Formatting group >> select Color Scales.
Then, select a color scale according to your choices.

Therefore, you can see the color scale in the selected cells.

How to Create Custom Color Scales in Excel

If you want to customize the color and format of the color scale, you can create custom color scales.

First of all, we will select cells C5:C14 >> go to the Home.
Then, from the Conditional Formatting group >> select Color Scales >> select More Rules.

At this point, an Edit Formatting Rule dialog box will appear.

Select Format all cells based on their values.
In the Format Style box, select 3-Color Scale. You can also select a 2-Color scale.
Select Number in the Midpoint and Maximum.
We set the Midpoint as 7 and the Maximum as 21.5.
Select a Color from the color boxes according to your choices.
Click OK.

Therefore, you can see a 3-color scale.

Using Formula to Create Color Scale

Here, we will use the SMALL and LARGE functions to create color scales in Excel. Here, we will create a 2-Color scale.

In the beginning, following the steps mentioned in the previous method, we will bring the Edit Formatting Rule dialog box.

Then, in the Format Style box, select the 2-Color Scale.
Select Formula in the Minimum and the Maximum.
Type the following formula in the Minimum Formula Value box.

=SMALL($C$5:$C$14, 3)

Type the following formula in the Maximum Formula Value box.

=LARGE($C$5:$C$14, 3)

Select a color according to your choices.
Click OK.

As a result, you can see the 2-color scale using the formula.

Creating Different Types of Color Scales

Here, we will create a 4 and 5-color scale using Conditional Formatting.

1. Applying 4-Color Scale

In the first place, we will select cells C5:C14 >> go to the Home.
Then, from the Conditional Formatting group >> select New Rule.

Then, we will use the formulas and select colors for each rule.
Since we will apply 4 color-scale, we have to apply 4 rules including 4 formulas.
Also, we have to select 4 different colors.

Therefore, you can see the 4-color scale in Excel cells.

2. Applying 5-Color Scale

To create a 5-color scale, we have to apply 5 different rules in the cells. Here, we applied the following 5 formulas and selected 5 colors.

Therefore, the 5-color scale will look like the following.

How to Remove Color Scale from Excel Cells

Removing the color scale from Excel cells is easy.

First of all, select the cells that contain the color scales. Here, we select cells C5:C14 >> go to the Home tab.
Then, from the Conditional Formatting group >> select Clear Rules.
Select Clear Rules from Selected Cells.

As a result, you can see that the cells have no color scales.

Conclusion

In this article, we extensively discussed how to add, use, and format the Excel color scale. This article provides sufficient knowledge for you so that you can add 2 to 5-color scales and customize them. Also, we show some easy techniques to remove color scales.

Thank you for going through the article. We hope this article was helpful to you. If you have any queries or suggestions, please let us know in the comment section.

Excel Color Scales: Knowledge Hub

<< Go Back to Conditional Formatting | Learn Excel