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.
- 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.
- 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.
- 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.
- Click on cell A1 and then choose Filter on the Data tab of Excel's ribbon.
- Click the Filter arrow in cell C1, choose Number Filters, and then Greater Than.
- Enter 1, and then click OK to filter the list to for duplicate values.
- Click on cell A1, then choose Data, Filter, and then AutoFilter.
- Click the Filter arrow in cell C1 and then choose Custom.
- Change Equals to Greater Than, Enter 1, and then click OK.
- Read Part 2 of this series to learn more ways to identify duplicate items an Excel spreadsheets.