Find and Delete Duplicates in Excel – The Ultimate Guide

While working with huge data tables in Excel there are times when your data may contain duplicate records.
 
To eliminate the confusions that duplicate records may result into and to avoid unnecessary increase in physical size of the spreadsheet it is better to find and remove the duplicate records.

Today I will be sharing five easy and effective ways by which you can identify and remove duplicate records in Excel:
 
So, lets get started.

First Method: Finding duplicates using Conditional formatting:

This is one of the most easiest and fastest way for finding duplicate values in excel. Follow the below steps to perform this:

  • First of all select the range, in which you wish to identify the duplicate records.
  • With this range selected, on “Home” Tab navigate to “Conditional Formatting” > “Highlight Cells Rules” > “Duplicate Values”.

Conditional-Formating-Option-For-Finding-Duplicates

  • Next, a dialog box will popup which will ask you to select the colour for highlighting the duplicate entries. Select any colour scheme from the drop down, click “OK” and all the duplicate entries will be highlighted.

Second Method: Deleting duplicate records by using ‘Remove Duplicates’ option:

Here we will use an inbuilt feature of Excel to delete the duplicate entries in a range. Follow the below steps to use this option:

  • First of all select the range where the duplicate entries are present.

Remove-Duplicate-Records-Option

  • Next, with the range selected navigate to the “Data” tab and select the option “Remove Duplicates”.

Duplicate-Records-Removed

  • Now all the duplicate cells in the selected range will be removed.

Third Method: Separating unique records by using Advanced Filters:

In this method we will use Excel Advance filter and then copy only the unique records from a column to a different location. Follow the below steps:

  • First of all select the range that contains duplicate records.
  • With this range selected, navigate to “Data” tab and click the “Advanced Filter” option.

Copy-Only-Unique-Records-With-Advanced-Filter

  • Now an “Advance Filter” dialog pops-up in this window select the “Copy to another location” radio button, then enter the range where you wish to copy the unique values, and then check the “Unique records only” checkbox.  Click “Ok” and the unique records will be copied to the new location.

Fourth Method: For Identifying and deleting duplicate entries:

In this method we are going to use a formula for finding the duplicate records in excel. The formula that we are going to use is a CountIF Formula.

The formula is: =COUNTIF (<Range>, <current cell>)>1

Example:

Let’s consider we have a table containing Names of some people as shown below:

Fetch-Duplicate-Values-Using-Formula-1

In this case for identifying duplicates we will apply the formula (for first cell):

=COUNTIF(A$2:A14,A2)>1

And then drag this formula for the entire range.

This formula counts the cells that have same value equal to the current cell. Hence if this number is greater than 1 that means there are multiple rows that contain a value same as the current cell.

So, a “TRUE” value means that it is a duplicate record.

While “FALSE” means that the record is unique.

What’s the catch in this formula?

This formula works the way it is designed to do. It simply writes a “TRUE” in front of the records that are present in the data range more than once and for other records it results into a “FALSE” value.

But the point that should be noted is: This formula writes a “TRUE” for both the instances of a duplicate record. So, it isn’t safe to delete the records with “True” flag.

For example: See in the above image that this formula has resulted a “True” for the records at cells “A2” and “A6”. I agree that both these records are duplicates but if you want only unique records in your range then you cannot delete both the instances of these records, you can only delete any one of them.

A better Formula that can fix the above issue:

The above issue can be solved by using the formula: =COUNTIF($A$2:$A2, A2)>1

Fetch-Duplicate-Values-Using-Formula-2

This formula is created by cleverly using Relative and Absolute modifiers. Apply this formula in-front of the first record and then drag the formula to the last element in the range. After applying this formula simply filter the records with “True” value and then delete them.

Note that in this formula out of two duplicate instance of the record at “A2” and “A6” only one is “True”.

Fifth Method: Highlighting duplicate values using Macro:

For the people that are more oriented towards the coding side, I will share a macro that will highlight the duplicate entries inside a range.

This macro only works when two conditions are met:

  1. All the items inside the range must be sorted using a filter.
  2. The first cell in the range from which you want to remove the duplicate records should be selected before running the macro.

The macro is as follows:

Sub FindDups()
'
' NOTE: You must select the first cell in the column and
' make sure that the column is sorted before running this macro
'
ScreenUpdating = False
FirstItem = ActiveCell.Value
SecondItem = ActiveCell.Offset(1, 0).Value
Offsetcount = 1
Do While ActiveCell <> ""
If FirstItem = SecondItem Then
ActiveCell.Offset(Offsetcount, 0).Interior.Color = RGB(255, 0, 0)
Offsetcount = Offsetcount + 1
SecondItem = ActiveCell.Offset(Offsetcount, 0).Value
Else
ActiveCell.Offset(Offsetcount, 0).Select
FirstItem = ActiveCell.Value
SecondItem = ActiveCell.Offset(1, 0).Value
Offsetcount = 1
End If
Loop
ScreenUpdating = True
MsgBox "Done"
End Sub

Note: You can also find this macro at Microsoft Knowledgebase.

So, these were few ways by which you can find and delete duplicate values in excel.

About Ankit Kaul

Ankit is the founder of Excel Trick. He is tech Geek who loves to sit in front of his square headed girlfriend (his PC) all day long. :D. Ankit has a strong passion for learning Microsoft Excel. His only aim is to turn you guys into 'Excel Geeks'.

Privacy Policy | Disclaimer | About