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 in Figure 1, 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.

Figure 1: Excel's Conditional Formatting feature makes it easy to identify duplicate values in a list.

 

You can then isolate just the duplicates, as shown in Figure 2:

• 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 in the inset in Figure 2.

Figure 2: Excel 2007 and later offer the ability to filter cells based on color.

 

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 in Figure 3.

 

Figure 3: You can easily clear conditional formatting from a worksheet in Excel 2007 and later.

 

If you're using Excel 2003,  you don't have the ability to filter by color, but you can isolate duplicate values by way of the COUNTIF worksheet function. COUNTIF has two arguments:

• Range: This is a range of two or more cells that you wish to test.
• Criteria: The value that you're seeking within the range.

Building on the example shown in Figure 1, you can add the following formula to cell C2:

 

=COUNTIF(A:A,A2)

 

Once you've entered the formula, double-click on the Fill Handle in cell C2 to copy the formula down the column. The Fill Handle is the little notch that most users drag down a column when copying formulas.

 

You can then filter the list for any values greater than 1:

 

Excel 2007 and later

1. Click on cell A1 and then choose Filter on the Data tab of Excel's ribbon.
2. Click the Filter arrow in cell C1, choose Number Filters, and then Greater Than.
3. Enter 1, and then click OK to filter the list to for duplicate values.

Excel 2003 and earlier  

1. Click on cell A1, then choose Data, Filter, and then AutoFilter.
2. Click the Filter arrow in cell C1 and then choose Custom.
3. Change Equals to Greater Than, Enter 1, and then click OK.
4.  
5. Read Part 2 of this series to learn more ways to identify duplicate items an Excel spreadsheets.