How to Use Color Scales in Excel? A Step-by-Step Guide

• Facebook
• Twitter
• YouTube

(Note: This guide on how to use Color Scales in Excel is suitable for all Excel versions including Office 365)

When dealing with a large amount of data in Excel, it might sometimes be hard to ascertain or grasp the data without giving it a deeper look. However, when you’re running low on time, you might want cues to quickly comprehend the data.

In such situations, you can customize the data using conditional formatting and adding visual effects to the data set. This will make it easy to compare, contrast, and understand the data. 

One such way to use conditional formatting is by making use of Excel Color Scales.

In this article, I will show you how to use color scales in Excel. You’ll also learn how to customize them and remove them if necessary.

You’ll Learn:

• What Are Color Scales in Excel?
• Example
• How to Use Color Scales in Excel?
• How to Customize the Excel Color Scales?
• How to Remove Color Scales in Excel?

Related Reads:

How to Insert a Page Break in Excel? (3 Simple Steps)

How to Remove Dropdown in Excel? 3 Easy Methods

How to Change the Margins in Excel? 2 Useful Ways

What Are Color Scales in Excel?

Before we see how to use color scales in Excel, let us see what color scales are.

Color scales are gradient differentiation within a cell that helps the user identify the range the data falls into. The color scales represent the minimum point, midpoint, and maximum point respectively. For example, a darker color can be applied for a greater value and lighter color can be applied for a lesser value, making it easier to compare and ascertain the values. 

Color scales are commonly used to represent profit, investments made, temperature, time, or any other measurable quantity. 

You can choose either two or three-color scales depending on the data. Using color scales and conditional formatting, you can input the data, and based on the set threshold, Excel highlights the particular cell with the value. 

Example

Consider a simple example where you have a list of boilers in a Power Plant (say 10) and their corresponding temperatures in ℃. Since the data is small, it is easy to ascertain the data. But, when the schematic layout of the whole plant is given, it might be hard to keenly observe the data.

In such cases, let us see how the usage of color scales affects the readability of the data.

How to Use Color Scales in Excel?

Let us now see how to use Excel color scales with an example. 

• First, select the cells to which you want to apply color scaling. You can select a range of cells, particular tables, or even the whole sheet. In this case, we have selected the cell range C6 through C15.

• Navigate to Home. Under the Styles section, click on the dropdown from Conditional Formatting and select Color Scales.

• Excel offers a total of 12 default color scales. Among them, 6 are two-color scales and the other 6 are three-color scales. When you hover over them, you can see the preview of the color scales and their description.

• Once you click on the color scale, they get applied to the selected data. In this case, we have selected the Green-Yellow-Red color scale. 

You can see that the color scales are assigned to the values based on the default settings. That is, Excel compares the values and automatically assigns the color scale to the selected cells. Here, you can see the color of lower values ( >0) in red, middle values (25-75) in yellow and greater values (>75) in green. 

This is a quick and efficient way to assign the color scales to the selected data that will help you understand the data easily. 

Note: When the selected data contains any blank cells or any error values, the conditional formatting skips those cells and highlights the remaining cells.

Suggested Reads:

How to Recover Unsaved Excel File? – 3 Ways

How to Spell Check in Excel? A Step-by-Step Guide

How to Change Row Height in Excel? 5 Easy Ways

How to Customize the Excel Color Scales?

From the above method, we saw how to use the default color scales in Excel. When you select the default color scales, the values are automatically highlighted with the default color without specifying any conditions. What if you want a specific color mapped to a particular range of values or change the color scheme? 

When you need to change the color based on specific values, you can assign the values from the More Rules section. Let us now see how to customize the values and color scales based on user conditions. 

• To customize the conditions and color scales, first, select the cells. 

• Then, navigate to the Home main menu. Under the Styles section, click on the dropdown from Conditional Formatting. Extend the Color Scales section and click on More Rules. 

• This opens the New Formatting Rule dialog box. 

• In the dialog box, under the Select a Rule Type, click on Format all cells based on their values. Under Edit the Rule Description, click on the dropdown from Format Style. Here, you can choose between a two-color scale or a three-color scale.

• By default, the minimum, midpoint, and maximum threshold values will have their types as lowest value, percentile, and highest value respectively. From the Type dropdown, you can change the nature of the values. 

• From the Value text box, you can either select the cells or enter the values manually. In this case, let us select the Type as Number, and enter the values manually. 

• Finally, from the Color dropdown, choose the color schemes of your choice.

• You can see the transition of color based on the highest to lowest value from the Preview option. 

Once you click OK, the customized color scale will apply to the selected cells. 

How to Remove Color Scales in Excel?

After applying the color scales to the data, you might sometimes not want the color scales in the selected cells. In such cases, you can choose to remove them using the below-given method.

• First, select the cells from which you want to remove the color scales. 

• Navigate to the Home tab. Click on the dropdown from Conditional Formatting, extend the Clear Rules options and click on Clear Rules from Selected Cells. If you want to remove the color scales from the whole worksheet, click on Clear Rules from Entire Sheet.

This instantly removes the color scaling from the selected cells.

Also Read:

How to Use Cell Styles in Excel: A Step-by-Step Guide

How to Reduce Excel File Size? 7 Proven Ways

How to Insert a New Worksheet in Excel? 5 Easy Ways

Frequently Asked Questions

Closing Thoughts

Color Scales in Excel are a great way to visually represent the data for the user to understand quickly. Instead of looking at each data individually, using the color scales will help the user save time and increase efficiency.

Please visit our free resources center for more high-quality guides on Excel and other Microsoft Suite applications.

Ready to dive deep into Excel? Click here for advanced Excel courses with in-depth training modules.

Simon Sez IT has been teaching Excel and other business software for over ten years. For a low, monthly fee you can get access to 140+ IT training courses.

Simon Calder

Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA.He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!