How to Identify Duplicate Values in an Excel List

Identifying Duplicate Values in an Excel List

Periodically, Identifying duplicate values in an Excel list. Latest versions, along with Excel 2007 and later, contribute the ability within a few mouse clicks.
For great measure, I will also distinguish a worksheet function based approach that efforts in any version of Excel.

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

Pick the range of cells you wish to test. One way to do so is to tap on a single cell and then press Ctrl-A.
On Excel’s Home tab, select Conditional Formatting, Highlight Cells Rules, and then Duplicate Values.
Tap OK within the Duplicate Values dialog box to find out the duplicate values.
Duplicate values in the list will now be found.

Figure 1: Excel’s Conditional Formatting character makes it simple to find out duplicate values in a list.
You should then isolate the duplicates, as shown in Figure 2:

Right-click on one of the duplicate cells, select Filter, and then Filter by Selected Cell’s Color.
This collapses the list to view just the duplicate values, which you 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 colour.
To clear away the conditional formatting, one approach is to tap the Conditional Formatting button, prefer Clear Rules, and then Clear Rules from Entire Worksheet, as shown in Figure 3.

Figure 3: In a simple manner you can erase conditional formatting from a worksheet in Excel 2007 and later.

If you are using Excel 2003, you don’t have the ability to filter by colour, but you can confine duplicate values by way of the COUNTIF worksheet function as it has arguments:

Range: This is a range of two or more cells that you wish to test.
Criteria: The value that you are searching within the range.

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

Once if you typed the formula, double-tap on the Fill Handle in cell C2 to copy the formula down the column. The Fill Handle is having the little notch that many users can drag down a column at the time of copying formulas.
You should then filter the list of any values greater than 1
Excel 2007 and later

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

Excel 2003 and earlier

Tap on cell A1, then selects Data, Filter, and then AutoFilter.
Tap the Filter arrow in cell C1 and then select Custom.
Change Equals to Greater Than, Enter 1, and then tap OK.
Read Part 2 of this series to learn more ways to identify duplicate items and Excel spreadsheets.