Excel: Highlight cells with user-defined rules

Excel highlight: YOUR OBJECTIVE

You have created an Excel sheet with revenue listings. Now you want to highlight all cells with a value between 150,000 and 190,000 in a different color to analyze them:

In this learning module “ Excel highlight” you will learn how to define your own Conditional Formatting rules and what you have to pay attention to when doing this.

Excel highlight:HOW DOES IT WORK?

If the Conditional Formatting rules provided by Excel do not suit you, you can define your own rules.

Select the data range that you want to define your own rule for:

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

Click the command to define your own rule:

This opens the New Formatting Rule dialog box:

Select one of six different rule types in the Select a Rule Type: dialog box.

After selecting the rule type in the upper section of the dialog box, you can specify the details in the Edit the Rule Description: section.

The individual rule types allow you to specify the following formatting options:

Select this rule type to format all cells within the selected cell range by comparing them to each other, i.e. highlighting a value progression graphically. It uses color scales, color bars or icons to format the cells.

Select this rule type to format those cells that contain a particular text or where the cell value is within a particular range.

Select this rule type to format the top or bottom values of a data range by comparing them to each other.

Excel highlight:Select this rule to highlight all cells within a selected cell range where the value is within a particular range of accuracy above or below the average value.

Select this rule type to format all cells that contain duplicate values or unique values within a particular data range.

Select this rule type to format all cells where the value returns a True for a formula you have specified. If the result of the formula equals “0″ or any text, then the logical value False is returned, any other value represents the value True.

In this example, all cells that contain values between 150,000 and 190,000 will be highlighted in a different color. Select the Format only cells that contain rule type:

The bottom half of the New Formatting Rule dialog box changes:

Now you can edit the selected rule type in the bottom half of the dialog box. In this example, all values between 150,000 and 190,000 Dollars will be highlighted.

Now enter the corresponding numbers:

Now you have to specify how to format the cells of the value range you have specified. Click the  button:

This opens the Format Cells dialog box, where you can specify the following settings:

Select a number format in which to display the corresponding cell content here.

Select a font format for your text on this tab.

Change the appearance of the cell borders here.

Select this tab to design the background of the corresponding cells.

The background of all cells with a value between 150,000 and 190,000 Dollars will be highlighted in yellow. Select the Fill tab to specify a background color.

When you have selected a color by clicking it with your mouse, it will be displayed in the sample:

After you have set up the format you want, close the dialog box by clicking the OK button.

This will take you back to the New Formatting Rule dialog box, where you will also see a preview of the formats you have specified:

To apply your settings, close this dialog box by clicking the OK button.

The selected cell range will then be formatted according to the rule you have created:

The next learning module will show you how to modify or delete a rule.

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