Compare two lists; or how to find duplicates in Excel

Comparing two lists, or comparing items within a single list is one of the most common business tasks done with a spreadsheet.  You might have a list of names or products and would like to see if one entry occurs twice. Excel contains a handy built-in function to do this task quickly.

How to highlight duplicates, quick and dirty.

First, select the worksheet region that contains the values or labels that you want to compare. If there are two lists, select both with CTRL-select. Then in the Home menu, select Conditional Formatting, then Highlight Cells Rules, then Duplicate Values

Its as easy as that!  This highlights any duplicate values in the selected color.

You can also toggle the duplicate/unique setting to highlight the opposite : only the unique values.

Now what about removing those duplicates?

If you now want to remove the duplicates from you list you could of course start down the list and begin deleting duplicates cell by cell as you see them (the red ones). However the list could be hundreds or even thousands of rows long. This would take hours to do by hand. This is where you will need to get your Worksheet Ninja skills going and remove duplicates using a formula process.

There’s also a much better and thorough way to compare two lists, exhaustively.

Close Menu