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.