Conditional formatting

Conditional formatting in Excel highlights cells visually as soon as a condition is met. This makes data more descriptive and allows facts to be recognized more quickly.

Condition greater than

In our first example, from a list of prices for cars, we will highlight all amounts that cost over €10,000. To do this, we will select all cells with prices as in the screen capture.

Then we click on the “Conditional Formatting” button in the “Start” tab in the “Style” group.

A menu opens and we click on “Rules for highlighting cells” on the selection “Greater than”.

The “Greater than” dialog box opens. We enter the value 10.000 € in the left cell. This will highlight all values greater than 10,000.

Tip: Instead of a fixed value, we can select a cell in our datasheet. For example C1

In the second cell we set the formatting. We select “green fill” for our example. This will mark all values above €10,000 with a green background.

We click OK and see the result:

Tip: We can also add additional rules like Equal or Double or insert other conditions like Upper/Lower rules.

Manage formatting rules with symbols

We would like to compare the expenses for shoes of three women- and this with colored symbols! We mark our data as in the example.

Then we click on the “Conditional Formatting” button in the “Start” tab in the “Style” group.

In the following menu we select symbol sets.

We click on 3 traffic lights (without border) in the Shapes category.

The result looks like in the screen shot. However, we want to mark the high expenses with red traffic lights and not with green ones.

For this reason we change the rules for the distribution of the traffic lights. To do this, we click on the “Conditional formatting” button while the outputs are still selected.

We click on the “Manage rules” option at the very bottom of the menu.

Now we see the current rule and several buttons. We click on “Edit rule”.

the “Edit formatting rule” dialog box opens in the “Edit rule description” area on the “Reverse symbol order” button. To confirm, we click OK and then OK again.

ready. Our table now clearly shows the distribution of spending on shoes.

Tip: When using conditional formatting, the rule is: as much as necessary and as little as possible.

Conditional formatting with color scales

For the next example, we would like to add a color scale to the “Price” column in our table of used cars. Therefore, we select the Price column as in the first example.

Then we click on the “Conditional Formatting” button in the “Start” tab in the “Style” group.

In the following menu we click on Color scales.

Excel shows us a selection of formatting rules with different gradations of colors. We choose the green-white color scale, where the higher the number, the more intense the green.

This is the result:

The following screenshot shows the series “Price” sorted. If you want to know how to sort data series, click here: Sorting Data in Excel.

Remove conditional formatting

To remove the Conditional Formatting we click on the “Conditional Formatting” button and select “Delete rules” from the menu and click on “Delete rules in selected cells”.