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 in and to avoid an unnecessary increase in the 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, let's get started.

Method 1: Finding Duplicates using Conditional Formatting

This is one of the easiest and fastest ways 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 the “Home” Tab navigate to “Conditional Formatting” > “Highlight Cells Rules” > “Duplicate Values”.

Conditional-Formating-Option-For-Finding-Duplicates

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

Method 2: Deleting Duplicate Records by using the ‘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.

Method 3: Separating Unique Records by Using Advanced Filters

In this method, we will use the 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 the “Data” tab and click the “Advanced Filter” option.

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

  • Now an “Advanced 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.

Method 4: Using the COUNTIF Formula for Identifying and Deleting Duplicate Records

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 based on the CountIF Function.

The formula is:

=COUNTIF(<Range>, <current cell>)>1

Example – Find Duplicates in Excel Using the COUNTIF Function

Let’s consider we have a table containing the 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 To Find and Delete Duplicates In Excel

The issue that we discussed above 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 the “True” value and then delete them.

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

Method 5: Highlighting Duplicate Values In Excel using VBA Script

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

Well, I am Ankit Kaul, the founder of Excel Trick. I am a die-hard fan of Microsoft Excel and have been working with spreadsheets for the past 10+ years. My only aim is to turn you guys into 'Excel Geeks'. Check out more about me here.