To remove duplicate values in Excel, follow the steps below.
This is the first post in a series of basic data analytic procedures using Excel. If you work with data regularly, these procedures will help you understand your data better and analyze it faster.
I started this series because I am asked how to do these tasks, sometimes by experienced contractors and auditors.
These procedures are written for Excel 2013, but the same steps should also work in Excel 2010, but the screenshots might be a little different.
Why Duplicate Transactions Matter
Knowing whether a file contains duplicates is important for the following reasons:
- If you total a column of numbers for all records, duplicate records will cause the total to be incorrect. Likewise, if you’re trying to determine the percentages of IT auditors vs financial auditors in a list, duplicate records can affect the percentages.
- Having duplicates may indicate the system is processing or exporting records incorrectly.
- Duplicate transactions may be an indication that users are making errors or that fraud is occurring.
Removing Duplicates Across All Columns
To remove duplicates across all values (columns) in an Excel table:
- Click inside one of the cells in the table from which you want to remove duplicates.
In Eample 1 below, I clicked in cell A2 (to enlarge an example, click it).
- On the Data tab, click Remove Duplicates.
See Example 1 below.
- The Remove Duplicates dialog box appears. Since you want to remove rows/lines that are duplicates across all columns, leave all the columns checked, and press OK.
See Example 2 below.
- The duplicates are removed. Notice that Excel tells you how many duplicates were removed and how many unique values remain.
See Example 3 below.
Removing Duplicates Across Specific Columns
If you are interested in removing duplicates only in specific columns, leave only those columns checked.
For example, if you wanted to remove all duplicate positions from the list, you would leave only the Position column checked (see Example 4 below).
When you press OK, the list would appear as follows (see Example 5).
The first IT auditor, Finance Auditor, and Operations Auditor would remain, and all others would be removed (if only getting rid of bad auditors was that easy).
See all the posts for these series at Excel: Basic Data Analytic series.
9 responses to “Excel: Remove Duplicates”
I wish it was a bit more robust, first finding the duplicates and then prompting you for what you wanted to do with them. Sort of a blend of data filter & remove duplicates … filter duplicates.
Not sure what you mean, John…
Good series idea. I am amazed at the amount of people who do not know how to use excel, but even MS word seems to be a problem. When I review reports doing things like repeating table headers, using header tags to create a TOC, making tables repeat across pages, etc. is just something people never learn to do. Maybe I’ll do a few write-ups on “style”.
Also, I’d love to see a post or two about creating excel add-on ribbons. I’ve never made my own, but I’ve installed and used many of them.
I’m amazed too, but I did audit for a year before I learned vlookup. In my case, I just didn’t know any better. I get frustrated when you show an auditor something as basic as vlookup and he refuses to use it. I’ve found that’s usually auditors that are 35+ years old.
Yes, most people don’t know Word, but I did a lot of publishing documents in my early years and I learned Word pretty well. The best trick that most auditors don’t know is that Word can delete the first couple characters of multiple lines without deleting the rest. Very handy for cleaning up data in a pinch.
I’ll do a post on that some day with examples.
I like your style idea. Go for it. You guys sure are putting a lot of info out there. Wow. Where do you find the time….
One of my favorite audit stories is a guy that would spend a day a week summarizing payroll data (I didn’t know that). He sent me the data and I replied 5 minutes later with his errors. When I showed him pivot table, he exploded… true story. He felt so dumb, I actually felt guilty. It made me really wonder if I would do more for the company simply looking for those opportunities. A fifth of his year was spent on manually building a pivot table.
Christian, I didn’t see a plug, are you in the blogroll?
Hey John – I’m the author of http://www.risk3sixty.com. Mack and I link back and forth on occasion, including in our blogrolls. I hope to see you around both our sites.
And if you haven’t checked out Christian’s blog (see above), you’re missing something.
You have permission to leave my blog without feeling guilty…..
I’ll never forget the time I was copying data from a webpage that was full of graphics; all I wanted was the text.
As I’m complaining, my buddy says, hey Mack, don’t you know about Paste as text?
I felt pretty stupid by he was gracious.
Pingback: Excel: Identify Duplicates | ITauditSecurity