Conditional formatting in Excel is a feature that automatically applies formatting rules to cells based on specified conditions.

Never change the color manually ⛔

Many of you change the color of the cells manually using the formatting bar. Of course, to change the color of the headers of a painting or a whole column, it's perfect.

However, if you use color to highlight an important result, this technique is not good at all.

Because if you update your data, your highest or lowest value will not be the same, and then the color will not reflect the correct result.

Example without conditional formatting

In the following worksheet,

• We have colored in red the minimum value in H2
• We have colored in green the maximum value in H8

Now, we add the marks for the sixth course. The average has changed for all the students but the format red and green doesn't reflect the minimum and the maximum anymore.

So, what to do 🤔

To change automatically the colors according to the cell values, it's better to use the tool conditional formatting.

In this example, you can see that now, automatically, the colors reflect the highest or lowest value.

Different types of conditional formatting

Since the version of Excel 2007, the conditional formatting tool has been greatly enriched in Excel with many different options

Insert bars in the cells

With conditional formatting, you can insert bars in your cells according to the values in the cells.

Add icons in your cells

You can also add icons to highlight the KPI (key performance indicator)

Create a color scales

If the values in your cells follow a trend, you can create a color scale to highlight the distribution.

Highlight Top / Bottom values

You can also highlight the highest and lowest values automatically

Create your custom rule

You can also create your custom rule with a logical test