Debug Your Data with Conditional Formatting

(By Guest blogger Yoav Ezer)

Many times when a workbook is crammed full of numbers, your data can be difficult to read. This is bad enough when you are sure the data is correct, but If you are supplied a spreadsheet that contains potential errors you really want to be able to detect them so they can be fixed.

A common error in inputting is where your work contains duplicate records. There are a couple of ways we can delete duplicates, but what if you only want to see them rather than delete them? Here is when conditional formatting can help. With this little technique you can make the duplicates jump out at you!

Check out this screen grab below.

See how the duplicate rows are highlighted? In this sheet, the highlighting helps us indentify duplicate invoices.

The Solution

Here is how it was done.

First you need to go to the conditional formatting dialog as you normally would.

Then in the “Edit the Rule Description” box:

Enter this formula:

=SUMPRODUCT(($B2=$B$2:$B$16)*($A2=$A$2:$A$16)*($C2=$C$2:$C$16))>1

The formula might look complicated, and it kind of is. It relies on a function you might not have seen much called SUMPRODUCT. If you are curious about the function, this article is a great introduction to the topic.

In this formula, SUMPRODUCT will return the sum of rows from rows 2 through 16 where columns A,B and C are equal to the current row. If the result is greater than 1 then the format is implemented on that row.

Summary

As you can see, when you are given a spreadsheet containing problems, you don’t always want to nuke error rows, some times you need to know about them so you can deal with the issues at source. Conditional formatting can raise your awareness without changing the content of your spreadsheet. Give it a try!

About the author

Yoav Ezer co-authors the technology and productivity blog Codswallop. He is also the CEO of a company that produces PDF to Excel conversion software.

For more Excel tips from Yoav, join him on Facebook or Twitter