Excel color scale: Customizing the color scale format

Excel color scale: YOUR OBJECTIVE

You have formatted a table column in Excel so that the values are highlighted in a color scale. Now you want to replace this color scale with a red-green color scale where the highest values are highlighted in green and the lowest values in red, with their respective shades for the values in between

In this learning module “excel color scale”you will learn how to customize a color scale and what you have to pay attention to.

Excel color scale: HOW DOES IT WORK?

Use the Conditional Formatting Rules Manager if you want to modify a color scale.

Move the cursor into the data range for which you want to customize your excel color scale:

Then click the button in the Styles group on the Home tab

You can use the Conditional Formatting Rules Manage to edit an existing rule.

Click the command in the drop-down menu to open it:

The Conditional Formatting Rules Manager then opens:

Excel color scale: CAUTION

If you do not see any formatting rules, even though you have already created one, then your cursor has probably not been positioned within a data range for which a formatting rule has been defined. In this case, click the arrow button of the Show formatting rules for: drop-down menu and select the This Worksheet option.

The currently defined rules for your selected range are displayed in the dialog box. To modify a rule, select it by clicking it with your mouse and then click thebutton:

This opens the Edit Formatting Rule dialog box:

Excel color scale: HINT

You can also open the rule you want to modify by double-clicking it. This opens the Edit Formatting Rule dialog box, where you can then edit the rule.

You can select a different rule type in the upper section of the dialog box and then edit the rule in the lower section:

Select a different rule type for the currently selected rule here.

A 2-color scale is currently selected, so that the progression of the values is displayed in the shades of two different colors. However, you can also select one of the styles, such as the 3-color scale, data bars or icon sets by clicking the arrow button on the Format Style: drop-down menu.

Specify here the type of content that has to be in the cell so it is formatted in the color corresponding to the minimum value. In our example, the cell that contains the lowest value will be highlighted in the corresponding color. However, you can also select a content type such as Number, Percent, Formula and Quantile instead of Lowest Value.

Specify here the type of content that has to be in the cell so it is formatted in the color corresponding to the maximum value. In our example, the cell that contains the highest value will be highlighted in the corresponding color. However, you can also select a content type such as Number, Percent, Formula and Quantile aside from Highest Value.

So to define the two colors for the minimum and maximum values, now click the arrow button of the Color: drop-down menu:

A color palette then opens:

Click the color red to customize the color for the minimum value accordingly:

To then customize the color for the maximum values, click the arrow button in the Maximum section.

In the opening color palette, select the color light green to apply it to highest values.

Now close the dialog box by clicking the OK button to apply the changes.

This will take you back to the Conditional Formatting Rules Manager dialog box. The applied changes will be displayed in the preview:

Close the dialog box by clicking the OK button.

The changes have now been applied to the table:

 

Did you like it? Then share it or sign up!

You can share this article with your friends or sign up for our newsletter for further information.

Leave a comment

Connect with Facebook

*

Kein Banner zum Anzeigen