Conditional Formatting - Formula to highlight duplicates, errors and omissions

Today we are looking at another example of using formulas in conditional formatting.

It is recommended to first read and understand the entrance Conditional Formatting - formulas for selecting cells

1. Highlight duplicates in a list

For this example we will use a standard list of data like this. This list is in the range B7:B23.

So the formula to highlight duplicates is:

=COUNTIF($B$8:$B8,$B8)>1

2. Highlight errors in a list

Let's see how to highlight errors in a list like this, located in the range D7:F16.

To highlight any errors in column F, we just need to use the following formula in conditional formatting.

=ISERROR(F8)

3. Highlight omissions in a list

Imagine we have two lists and a super list. We highlight those values in the super-list that does not appear in the list A or B.

To highlight the omissions, the following formula is used:

=COUNTIF($H$8:$I$19,$J8)<1

With the following result:

We have seen how to use conditional formatting formulas to highlight omissions, duplicates and errors.

Download an example file

To download an example file with all the examples that we are covering here click on the following link:

Conditional Formatting - duplicates, errors and omissions

--------------------------------------------------------------------------------

To go to the menu tutorial on conditional formatting, click here.

Subscribe and get all entries via email

You can do it by providing your email into "Join the newsletter" box in the right sidebar.

Or follow me via Social Media

Facebook, Twitter and Google+