Excel rules: How to highlight cells with simple rules

Excel rules: YOUR OBJECTIVE

You have calculated a development in percentages in a data table and want to see at a glance which cells contain negative values. The conditional formatting function automatically allows you to highlight all cells with negative values in a different color.

In this learning module “ Excel rules ” you will learn how to create excel rules with the Conditional Formatting function to highlight cells with a particular content in a different color.

Excel rules:HOW DOES IT WORK?

Excel offers the option of designing cells using Conditional Formatting, so that they will be formatted automatically, i.e. highlighted in a different color if their content meets a particular criterion.

Excel rules:

 

First select the cell range to which you want to apply a conditional formatting:

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

Excel rules: Excel provides a selection of simple rules. Click the  command to display the available rules:

A submenu opens:

Select this option to highlight all cells where the content is greater than a value specified by you.

Select this option to highlight all cells where the content is less than a value specified by you.

Select this option to highlight all cells where the content is between two values specified by you.

Select this option to highlight all cells where the content equals exactly a value specified by you.

Select this option to highlight all cells that contain a particular text.

Select this option to highlight all cells that contain a particular date.

Select this option to highlight all cells that contain the same values.

In the example below, all cells containing negative values, i.e. values less than zero, will be formatted in a different color. Click the  command. The following dialog box opens:

Eter the value with which you want to compare the cell content here.

This drop-down menu contains six different formatting suggestions for all cells that contain the corresponding value. If you do not like either suggestion, you can you select the Custom Format… option to highlight the cells and format numbers, fonts, borders and fills individually.

Excel rules: HINT (1)

You can either enter the value manually or select the cell containing the value you want with your mouse. To do so, click the  button to leave the dialog box and click in the cell containing the comparative value you want. Then click the button to return to the dialog box.

In our example, all cells where the value is below zero are to be highlighted. So enter the number “0″ here:

Then select the format you want.

Review in the live preview how your format will affect the selected cell or cell range.

Then close the Less Than dialog box by clicking the OK button.

The selected format will be applied to all cells where the content corresponds to the specified value.

Excel rules: HINT (2)

You can identify what the respective format will look like by the description of the formatting. Light Red Fill will only fill the cell background in red, Red Text will only color the text and Red Border will apply only a red cell border to the cell. The number “2″ behind Light Red Fill 2 and Yellow Fill 2 indicates that two formats will be applied in total: namely one to the text and one to the background. This will result in a light background behind a dark red text, or a yellow background behind a dark yellow text. However, Excel will also apply two corresponding formats when you select the Green Fill with Dark Green Text option, so that it results in a light green background behind a green text.

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