Many Excel users are not familiar with the conditional formatting tool. But, it is the essential tool for automatically changing 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 color 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?
In a spreadsheet where data will change, it is better to use conditional formatting so that your colors automatically adapt to cell values.
In the worksheet below, change any value for courses 1 to 6.
Based on the new calculations, you can see that the highest or lowest cells will be always colored.
Different types of conditional formatting
Since the version of Excel 2007, the conditional formatting tool has been greatly enriched. In the menu Home>Conditional Formatting, you can choose from the following options:
- Apply a logical rule (as superior, inferior to a value)
- Highlight the highest or lowest value
- Insert data bars in cells proportional to values
- Create a color scales of 2 or 3 colors that varies according to values
- Add icon sets