Identifying Duplicate Values in an Excel List

From time to time, you may need to identify duplicate values with a list in Excel. Modern versions, including Excel 2007 and later, provide this capability with just a few mouse clicks. For good measure, I'll also describe a worksheet function-based approach that works in any version of Excel.

As shown below, you can easily identify duplicate invoice numbers in a list. To do so:

Select the range of cells you wish to test. One way to do so is to click on a single cell and then press Ctrl-A.
On Excel's Home tab, choose Conditional Formatting, Highlight Cells Rules, and then Duplicate Values.
Click OK within the Duplicate Values dialog box to identify the duplicate values.
Duplicate values in the list will now be identified.

You can then isolate just the duplicates, as shown below:

Right-click on one of the duplicate cells, choose Filter, and then Filter by Selected Cell's Color.
This collapses the list to show just the duplicate values, which you can copy and paste to another worksheet, or otherwise manage, as shown below.

To remove the conditional formatting, one approach is to click the Conditional Formatting button, choose Clear Rules, and then Clear Rules from Entire Worksheet, as shown below:

Source: David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide.