The tutorial explains how to remove duplicates in Excel 2019, Excel 2016, Excel 2013, and Excel 2010. You will learn a few different techniques to find and delete duplicate values with or without first occurrences, get rid of duplicate rows, detect absolute duplicates and partial matches.
Although Microsoft Excel is primarily a calculation tool, its sheets are often used as databases to keep track of inventories, make sales reports or maintain mailing lists.
A common problem that occurs as a database grows in size is that many duplicate rows appear in it. And even if your huge database contains just a handful of identical records, those few duplicates can cause a whole lot of problems, for example mailing multiple copies of the same document to the same person, or calculating the same numbers more than once in a summary report. So, before using a database, it makes sense to check it for duplicate entries, to make sure you are not wasting time on repeating your efforts.
In a couple of our recent articles, we discussed various ways to identify duplicates in Excel and highlight duplicate cells or rows. However, there may be situations when you may want to ultimately eliminate duplicates in your Excel sheets. And that's exactly the subject of this tutorial.
In all versions of Excel 365 - 2007, there is a built-in tool for removing duplicates called, not surprisingly, Remove Duplicates.
This tool allows you to find and remove absolute duplicates (cells or entire rows) as well as partially matching records (rows that have identical values in a specified column or columns). To perform this, follow the below steps.
Note. Because the Remove Duplicates tool permanently deletes identical records, it's a good idea to make a copy of the original data before removing duplicate rows.
Done! All duplicate rows in the selected range are deleted, and a message is displayed indicating how many duplicate entries have been removed and how many unique values remain.
Note. Excel's Remove Duplicates feature deletes 2nd and all subsequent duplicate instances, leaving all unique rows and first instances of identical records. If you want to eliminate duplicate rows including first occurrences, use one of the following solutions: filter out duplicates with 1st occurrences or utilize a more versatile Duplicate Remover for Excel.
Another way to get rid of duplicates in Excel is separating unique values, and copying them to another sheet or a different workbook. The detailed steps follow below.
Note. Excel's Advanced Filter allows copying the filtered values only to another location on the active sheet. If you want to copy or move unique values or duplicate rows to another sheet or a different workbook, you can easily do it using our Duplicate Remover for Excel.
One more way to delete duplicate values in Excel is to identify them using a formula, filter out, and then delete duplicate rows.
An advantage of this approach is versatility - it lets you find and delete duplicate values in one column or duplicates rows based on values in several columns, with or without first instances. A drawback is that you will need to remember a handful of duplicate formulas.
=IF(COUNTIF($A$2:$A2, $A2)>1, "Duplicate", "")
=IF(COUNTIF($A$2:$A$10, $A2)>1, "Duplicate", "Unique")
Where A2 is the first and A10 is the last cell of the range to be searched for duplicates.
Formulas to find duplicate rows
=IF(COUNTIFS($A$2:$A2, $A2, $B$2:$B2, $B2, $C$2:$C2, $C2)>1, "Duplicate row", "Unique")
=IF(COUNTIFS($A$2:$A$10, $A2, $B$2:$B$10, $B2, $C$2:$C$10, $C2)>1, "Duplicate row", "Unique")
Where A, B, and C are the columns to be checked for duplicate values.
For example, this is how you can identify duplicate rows except for 1st instances:
For more information about using duplicate formulas, check out How to identify duplicates in Excel.
In a similar manner, you can find and delete a specific duplicate occurrence(s), for example only 2nd or 3nd instances, or 2nd and all subsequent duplicate values. You will find an appropriate formula and step-by-step instructions in this tutorial: How to filter duplicates by their occurrences.
Well, as you have just seen there is a number of ways to find and remove duplicates in Excel, each having its strong points and limitations. But what would you say if instead of those numerous duplicate removing techniques, you had one universal solution that wouldn't require memorizing a bunch of formulas and would work in all scenarios? The good news is that such a solution does exist, and I will demonstrate it to you in the next and final part of this tutorial.
Unlike the inbuilt Excel Remove Duplicate feature, the Ablebits Duplicate Remover add-in is not limited to only removing duplicate entries. Like a Swiss knife, this multi-tool combines all essential use cases and lets you identify, select, highlight, delete, copy and move unique or duplicate values, absolute duplicate rows or partially matching rows, in 1 table or by comparing 2 tables, with or without first occurrences.
It works flawlessly on all operating systems and in all versions of Microsoft Excel 2019 - 2003.
Assuming you have our Ultimate Suite installed in your Excel, perform these simple steps to eliminate duplicate rows or cells:
As you can see in the following screenshot, all duplicates rows except 1st occurrences are deleted:
Tip. If you want to remove duplicate rows based on values in a key column, leave only that column(s) selected, and uncheck all other irrelevant columns.
And if you want to perform some other action, say, highlight duplicate rows without deleting them, or copy duplicate values to another location, select the corresponding option from the drop-down list:
If you want more options, such as deleting duplicate rows including first occurrences or finding unique values, then use the Duplicate Remover wizard that provides all these features. Below you will find full details and a step-by-step example.
Removing duplicates in Excel is a common operation. However, in each particular case, there can be a number of specificities. While the Dedupe Table tool focuses on speed, the Duplicate Remover offers a number of additional options to dedupe your Excel sheets exactly the way you want.
In this example, let's delete duplicate rows including 1st occurrences:
That's it! The Duplicate Remover add-in swiftly does its job and notifies you how many duplicate rows have been found and deleted:
That's how you can wipe duplicates off your Excel. I hope at least one of the solutions mentioned in this tutorial will work for you.
All of the powerful dedupe tools discussed above are included in our Ultimate Suite for Excel. If you are interested to give them a try, I encourage you to download a fully-functional trial version, and let us know your feedback in comments.
Table of contents