Excel icon: Customizing the icon set format


Excel icon:YOUR OBJECTIVE

You have highlighted columns with  excel  icon sets in a table. Now you want to modify the threshold values in the Revenue per stay column, so that all cells with a revenue of at least 40 Dollars per stay will be highlighted with a green traffic light:

 

In this learning module “excel icon”  you will learn how to modify the threshold values for a Conditional Formatting with the excel icon sets rule and what you have to pay attention to.

Excel icon: HOW DOES IT WORK?

If you want to modify the threshold values, use the Conditional Formatting Rules Manager to do so.

Move your cursor inside the data range you want to modify the conditional formatting for:

 

Then click the  button in the Styles group on the Home tab to open the following drop-down menu:

 

Click the command in the opening drop-down menu: 

 

This opens the Conditional Formatting Rules Manager, which will provide an overview of all rules defined for the current data range when opening it. 

 

Excel icon: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 list and select the This Worksheet option.

To edit the rule you want, select it by clicking it with your mouse and then click thebutton:

 

In the Edit Formatting Rule dialog box you can select a different rule type in the upper section and edit the rule in the lower section:

 

You can modify existing rules in the Edit the Rule Description: section:

 

The excel Icon Set formatting style has been defined for the current rule. Alternatively, you can also select the 2-color scale, 3-color scale or data bars as formatting style.

All values in the upper range that are higher than or equal to 67%, will be highlighted with a green traffic light icon.

All values in the medium range that are less than 67% and higher than or equal to 33% will be highlighted with a yellow traffic light icon.

All values in the lower range that are less than 33% will be highlighted with a red traffic light icon.

Select a different excel icon set here.

Select this option if you want to invert the order of the icons, so that the values that are in the upper range will be highlighted with a red traffic light, those that are in the medium range with a yellow traffic light and those in the lower range with a green one.

Select this option to hide the cell values, so that only the corresponding icons are displayed in the respective cells.

Now you want to redefine the threshold values. All cells with a value of 40 dollars or more will be highlighted with a green traffic light icon. Click the arrow button of the Type drop-down list first: 

 

Then select the Number option: 

 

You can now enter your own threshold value in the corresponding Value field. Replace the number “0″ with the number “40″: 

 

Proceed in the same way to modify the threshold value for the medium value range. Select the Number option from the corresponding Type drop-down list and enter the number “30″ in the respective Value field: 

 

You have modified the threshold values for the conditional formatting. Close the dialog box by clicking the OK button.

This will take you back to the Conditional Formatting Rules Manager dialog box:

 

Close this dialog box by clicking the OK button.

All cells with a value higher than or equal to 40 dollars are now highlighted with a green traffic light icon: 

 

 

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