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 almost Selection Sunday, when it seems that everyone in the country, including President Obama, makes their picks for the Final Four. There’s no need to search the Internet for a bracket template – you...
With high school teachers having to vie against all things digital to capture their students’ attention, it’s more important than ever for them to use fun and interactive lessons to bring accounting principles to...
Building complex spreadsheets without lookup formulas, such as VLOOKUP, is akin to putting a screw in the wall with a hammer. It’s possible, but the results aren’t pretty, and most probably won’t maintain...
Upcoming CPE Webinars
BAR is an acronym for: Boundaries, Authority and Role. This simple tool will provide participants with a solid understanding of leadership essentials to improve their performance.
This material is designed to provide a start-to-finish overview of how to plan and complete high-quality small audits efficiently.
In this session Excel expert David H. Ringstrom, CPA shares numerous techniques that you can use to work with charts more efficiently.
Key Accounting and Reporting Issues for Nonprofits No. 1: Overview and Statement of Financial Position
This material focuses on non-profit organizations organization, accounting and reporting.