What is a conditional formatting

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.

You will find in this post how to change the color of the highest or lowest value automatically.

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 scale of 2 or 3 colors that varies according to values
  • Add icon sets

 

 

 

 

Related posts


Permanent link to this article: https://www.excel-exercise.com/what-is-a-conditional-formatting/


Leave a Reply

Your email address will not be published.