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.
About David Ringstrom
David H. Ringstrom, CPA, is an author and nationally recognized instructor who teaches scores of webinars each year. His Excel courses are based on over 25 years of consulting and teaching experience. His mantra is “Either you work Excel, or it works you.” David offers spreadsheet and database consulting services nationwide.