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.
- 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.
- 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.
- 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.
- 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 an Excel spreadsheets.