Identifying Duplicate Values in an Excel List | AccountingWEB

Identifying Duplicate Values in an Excel List

By David H. Ringstrom, CPA

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.

Figure 1:
Excel's Conditional Formatting feature makes it easy to identify duplicate values in a list.
You can then isolate just the duplicates, as shown in Figure 2:
  • 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.
Figure 2: Excel 2007 and later offer the ability to filter cells based on color.
Wait, there's more!
There's always more at AccountingWEB. We're an active community of financial professionals and journalists who strive to bring you valuable content every day. If you'd like, let us know your interests and we'll send you a few articles every week either in taxation, practice excellence, or just our most popular stories from that week. It's free to sign up and to be a part of our community.