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 to 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