Identifying Duplicate Values in an Excel List
by Terri Eyden on
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.
You may like these other stories...
It's not a reality—yet—but accounting software is poised to eliminate accountants. We are at a tipping point for many similar professions: online education replacing professors, legal software replacing...
Whenever I speak to accountants about creating a cloud practice, the most common question is, “How do I charge my clients?” Ten years ago, maybe even five years ago, if I would’ve posed this question...
While reputational risk is the No. 1 nonfinancial concern among corporate directors, cybersecurity/IT risk is gaining steam. In fact, both private companies and organizations with more than $1 billion in revenue felt they...
Upcoming CPE Webinars
In this session Excel expert David Ringstrom helps beginners get up to speed in Microsoft Excel. However, even experienced Excel users will learn some new tricks, particularly when David discusses under-utilized aspects of Excel.
FRF for SMEs Series--Measurement and Disclosure Principles for various Consolidations and Business Combinations, Part 4B
This webcast will focus on accounting and disclosure policies for various types of consolidations and business combinations.
In this session we'll review best practices for how to generate interest in your firm’s services.
Meet budgets and client expectations using project management skills geared toward the unique challenges faced by CPAs. Kristen Rampe will share how knowing the keys to structuring and executing a successful project can make the difference between success and repeated failures.