Conditional formatting can highlight values or make some cells easy to understand. We can change the appearance of a cell based on some criteria. We can use conditional formatting to mark cells which meet a few criteria. Or we can highlight a whole cell range and get the same format for each cell.
Conditional formatting can mark important information in a spreadsheet. But sometimes we need to do something and the built-in formatting rules don’t go quite far enough. We can add own formula to a conditional formatting rule. It helps you do things the built-in rules can’t do.
In the examples above, we used formulas for conditional formatting. We can try to show you some useful examples for real life.
Identifying Duplicate Values in an Excel List
We can highlight duplicate value to easy identify some problems in data.
For example, we combine several databases into one. And we must know is it some duplicate rows there.
So we can just enter this formula
=COUNTIF($D$2:$D$5;D2)>1 and add format for duplicate values. We get the table where all duplicates value highlight and we can identify and remove it.
Also we can highlight duplicates using built-in tools:
Below, you can find links to an animation which shows the formatting process
Highlight Trends and Patterns
The following example shows orders by month with 3-color scale conditional formatting applied:
So, we can clearly see maximum and minimum months.