The conditional formatting tool changes the color of a cell according to its value.

Never change the color manually ⛔

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

However, if you use a 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 to correct result.

Example without conditional formatting

In the following worksheet, we have the result of five tests and in column H, the average for each student. You want to color in green the highest average and in red the lowest average.

Now, we add the marks for the sixth course. The average has changed for all the students but the format of the cells is still the same and doesn't match the new result.

So, what to do 🤔

To change automatically the colors in function of cell values, it is better to use 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.

Insert bars in the cells

With the conditional formatting, you can insert bars in your cells in function of 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 scales 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