by 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.
Remove Duplicates tool - eliminate repeated rows
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.
- To begin with, select the range in which you want to ddelete dupes. To select the entire table, press Ctrl + A.
- Go to the Data tab > Data Tools group, and click the Remove Duplicates button.
- The Remove Duplicates dialog box will open, you select the columns to check for duplicates, and click OK.
- To delete duplicate rows that have completely equal values in all columns, leave the check marks next to all columns, like in the screenshot below.
- To remove partial duplicates based on one or more key columns, select only those columns. If your table has many columns, the fastest way is to click the Unselect All button, and then select the columns you want to check for dupes.
- If your table does not have headers, clear the My data has headers box in the upper-right corner of the dialog window, which is usually selected by default.
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.
Get rid of duplicates by copying unique records to another location
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.
- Select the range or the entire table that you want to dedupe.
- Navigate to the Data tab > Sort & Filter group, and click the Advanced button.
- In the Advanced Filter dialog window, do the following:
- Select the Copy to another location radio button.
- Verify whether the correct range appears in the List Range This should be the range you've selected on step 1.
- In the Copy to box, enter the range where you wish to copy the unique values (it's actually sufficient to select the upper-left cell of the destination range).
- Select the Unique records only box.
- Finally, click OK, and the unique values will be copied to a new location:
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.
How to remove duplicate rows in Excel by filtering
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.
- Depending on your task, use one of the following formulas to detect duplicates.Formulas to find duplicate values in 1 column
- Duplicates except 1st occurrences:
=IF(COUNTIF($A$2:$A2, $A2)>1, "Duplicate", "") - Duplicates with 1st occurrences:
=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
- Duplicate rows except 1st occurrences:
=IF(COUNTIFS($A$2:$A2, $A2, $B$2:$B2, $B2, $C$2:$C2, $C2)>1, "Duplicate row", "Unique") - Duplicate rows with 1st occurrences:
=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.
- Duplicates except 1st occurrences:
- Select any cell within your table, and apply Excel's auto filter either by clicking the Filter button on the Data tab, or Sort & Filter > Filter on the Home tab.
- Filter out duplicate rows by clicking the arrow in the header of the "Duplicate" column, and then check the "Duplicate row" box.If someone needs more detailed guidelines, please see How to filter duplicates in Excel.
- And finally, delete duplicate rows. To do this, select the filtered rows by dragging the mouse across the row numbers, right click them, and select Delete Row from the context menu. The reason you need to do this instead of simply pressing the Delete button on the keyboard is that it will delete entire rows rather than just the cell content:
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.
Duplicate Remover - universal tool to find & delete duplicates in Excel
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.
How to get rid of duplicates in Excel with 2 mouse clicks
Assuming you have our Ultimate Suite installed in your Excel, perform these simple steps to eliminate duplicate rows or cells:
- Select any cell in the table that you want to dedupe, and click the Dedupe Table button on the Ablebits Data tab. Your entire table will get selected automatically.
- The Dedupe Table dialog window will open, and all the columns will be selected by default. You pick Delete duplicates from the Select the action drop-down list and click OK. Done!
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.
How to find and delete duplicate values with or without 1st occurrences
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.
- Select any cell within the table where you want to delete duplicates, switch to the Ablebits Data tab, and click the Duplicate Remover button.
- The Duplicate Remover wizard will run and the entire table will get selected.The add-in will also suggest creating a backup copy, and because you are going to permanently delete duplicates, we strongly advise that you check this box. Verify that the table has been selected correctly and click Next.
- Select what records you want to find and remove. The following options are available to you:
- Duplicates except 1st occurrences
- Duplicates including 1st occurrences
- Unique values
- Unique values and 1st duplicate occurrences
In this example, let's delete duplicate rows including 1st occurrences:
- And now, select the columns to search for duplicates. Because our aim is to eliminate duplicate rows, be sure to select all the columns (which is usually done by default).
- Finally, select the action you want to perform on dupes and click the Finish button. In this example, we expectedly choose the Delete duplicate values option.
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.
35 comments
Please help me to accomplish my task, I have column 1 and column 2 in column 1 I have duplicate values like; 1001, 1002, 1003, 1001, 1003; and in column 2 40, 60, 10, 20, now I want to remove duplicate from column 1 but add the value in column 2; 1001, 1002, 1003; 60, 60, 10. guide me how can I do this in excel?
Hi! You can't solve your problem with a single Excel formula. I recommend to pay attention to the Merge Duplicates tool. It may help you solve it in a few clicks. You can merge duplicates in a key column and use the SUM() function in the Delimiter field for the second column. You will get exactly the result you wanted. It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.
Hi,
I have a table containing to colums.
In colum A there are always values. In column B there are values or blanks.
Now I want to delete the rows containing duplicate values in column A but keep the instance with the non-blank cell in column B.
How can I do this natively in excel?
Thanks and kind regards.
Wim
Hi! Please re-check the article above since it covers your task. If I understand your task correctly, the following formula finds rows that have a duplicate in column A and an empty value in column B.
=IF(AND(COUNTIF($A$1:$A$20,A1)>1,ISBLANK(B1)),"Delete","")
I am looking to delete any row that has a duplicate value in a specific column. I only want to see lines that have only 1 occurrence. For instance, I have cat, cat, dog, bird, bird, sheep. I only want to keep the lines that contain dog and sheep because they are lines that only occur once.
I hope you have studied the recommendations in the tutorial above. It contains answers to your question
Hello,
I have a table in which I want to remove the duplicate values of a unique identifier. However, that column also contains blanks, all of which need to be retained in the table.
How do I remove duplicate values except where the value in a column is blank?
Hello!
The Duplicate Remover tool allows you to skip empty cells when searching for duplicates. You can see his work in this video.
I hope I answered your question. If you have any other questions, please don’t hesitate to ask.
Hello Alex, thanks for the reply.
Is there a way to do this within excel natively. This is a shared file and sometimes others will be executing this process on the file when I am unavailable.
Hello!
To use the Ultimate Suite in collaboration, you can use business edition installation or Terminal Server Users edition installation.
You can ask a question to our technical support.
Kindly help me with a method or formula to DELETE first and REPEATED ROWs OF text occurring in a single excel COLUMN of Excel, for example,
Cat
Dog
Amber
Rat
Cat
Yellow
Dog
Rat
Red
Mouse
Dog
Cow
SUCH THAT MY NEW COLUMN WILL HAVE
Amber
Yellow
Red
Mouse
Cow
Thanks.
Hello!
If your data is written in a single column, you can use the UNIQUE function.
=UNIQUE(A1:A10,FALSE,TRUE)
Also, take a look at this guide: How to get a list of unique and distinct values in Excel.
I hope my advice will help you solve your task.
How do I remove duplicate records made up of 2 columns across several tabs in the same spreadsheet. Example would be Tab 1 contains on line 7 of column1 and column 2 the values 12345 AAAA. Those same values are on Tab 3, line 40 and on Tab 7, lines 41 and 56. I only want these values to be in the spreadsheet one time regardless of the number of tabs. And the data is so large that I cannot copy and paste all of the data into one Tab.
I need help! Thanks!
Hello!
Use Duplicate Remover to remove duplicate rows in a worksheet.
If your data is on multiple sheets, you can combine them on one sheet using the Copy Sheets tool.
It is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.
I hope I answered your question. If something is still unclear, please feel free to ask.
Hi,
I'm looking for a way to remove the first occurence and keep the latter one. Since the number will be updated by the same person, so the older one wasn't needed anymore. Is there any way to do this?
Hi,
To find duplicates without the last occurrence, use the formula
=IF(AND(COUNTIF($A$2:$A$9, A3)>1, COUNTIF($A$2:$A3, A3)<>COUNTIF($A$2:$A$9, A3)), "Duplicate", "")
Use the example from this article.
I hope my advice will help you solve your task.
Hey Alex, how to remove duplicate NUMBERS from single column, eg: I have the data range from A2:A10 (including duplicates) and want to type in the formula in column ‘C’, don’t want to use unique function
Plz help
Hello!
To extract unique values from a column try these guidelines.
Hi,
Want to delete duplicate cells in each row
I have rows of data with duplicate cells in each row. I do not want to delete any rows. I want to delete only duplicate cells in each row.
For example, I now have in one row...
fox, sheep, goat, fox, dog, cat, fox
I want to delete the duplicate cells in each row. I want to end up with the following...
fox, sheep, goat, dog, cat
Thank you in advance for any replies.
Hello Eren!
If I understand your task correctly, in order to remove duplicates in rows, you can transpose your table. Rows and columns will be swapped. How to do this, read this guide.
Then remove duplicates in the columns using any of the methods recommended above. Then transpose again.
Alexander,
Can you do this technique where the original data has over 1100 rows?
Hello Carol!
Manually working with so much data is very difficult. Therefore, I recommend using the ready-made solution Ablebits Tools - Transpose and Ablebits Data - Duplicate Remover.
This tool is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and use for free: https://www.ablebits.com/files/get.php?addin=xl-suite&f=free-trial
Wow This is soooo Close to the answer I need to find out! This is exactly what I need minus one caveat. I need the formula whether that be in standard excel or through the Ablebits ad ins to remove all duplicates except for the last occurrence. Is that possible??
Hi Jacob,
The first thing that comes to mind is to flip your dataset vertically, then remove all duplicates but the first occurrence, and then flip the data back. Hope it helps :)
great ! 1
I learnt to find the duplicate in a excel sheet, now i want to rearrange the 2 duplicate values in a same row. Can somebody help on it?
Eg:
Apple Kiwi
Banana Orange
orange Apple
Kiwi Banana
Done to find duplicate, now want the result as:
Apple Apple
Banana Banana
Orange Orange
Kiwi Kiwi
Thanks
I have quite a large spreadsheet and I need to remove items with more than 3 duplicates. How can I do that?
In my dataset. each row OF A COLUMN contains this type of data, as:
Car-01 (80),Car-02 (35), Bus-01 (60), Bus-03 (90)
Bus-03 (120),Bus-01(69)
Now I want to remove the "Car-", "Bus-" etc from the 2nd to last but keeping the first. As:
Car-01 (80),02 (35), Bus-01 (60), 03(90)
Bus-03 (120),01(69)
is there any vba for this??
Hello,
I'm afraid there's no easy way to solve your task with a formula. Using a VBA macro would be the best option here.
However, since we do not cover the programming area (VBA-related questions), I can advice you to try and look for the solution in VBA sections on mrexcel.com or excelforum.com.
Sorry I can't assist you better.
Hi Svetlana,
I am looking to find duplicates and sum the duplicates in the same column .
I use the following formula to find duplicated = countif( A1:A7,A1) , so how
i can sum the duplicated in same column when i find the duplicates
8195022618495887 50 2
8195022618495887 500 1
A B C
Cards No Balance Duplicated
8193041466505317 25 1
8193220122990838 25 1
8193323426658387 25 1
8194697882227576 50 1
8195022618495887 50 2
8195022618495887 500 1
8197847512417947 25 1
Hello,
For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
Please also don't forget to include the link to this comment into your email.
I'll look into your task and try to help.
Hi Svetlana,
I am looking to delete duplicates in one column in separate rows but keeping the additional values in different columns of each row and create a total new list.
Col A . ColB . ColC
Name 3 1
Name 2
Name 5 4
Name 6
Result
Name 1 2 3 4 5 6
By the way, thank you for all these!
Dear fnd,
How i can remove one of repeat (text or numbers) from a cell.
Thanks & Regards
Hanuman Singh
Mobile No. 9034509168
I'm looking for a solution to mass delete a cell that appears in a row with the same value twice the spread sheets we have are 10,000+ rows and it is very tedious to remove them all by using conditional formatting and going through it by hand. The sheet contains contact information and sometimes the numbers are listed twice for the accounts we have and to stop them from being loaded into our software twice we have to go through and delete them out of the sheet first. The problem with just deleting them using the remove duplicate tool is that the person may have two separate accounts and it would remove the phone number from a row further down the page.
I WANT THE SAME SOLUTION