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.
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.
You will find in this post how to change the color of the highest or lowest value automatically.
Different types of conditional formatting
- 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 scale of 2 or 3 colors that varies according to values
- Add icon sets