×

How To Find And Remove Duplicate Excel Cell Data

By Chris Newman •  Updated: 06/29/16 •  9 min read
How to find and delete duplicates in Excel

Double Trouble

I think we have all had our fair share of experiences getting weird outcomes because of duplicate data within our cell ranges. They can easily cause your VLOOKUPs to pull unexpected values or give your subtotals problematic outcomes. Don't you fret though because there are a bunch of ways you can check and even remove duplicate rows of data within your Excel spreadsheets. Enjoy.

How To Find Duplicate Data In Your Spreadsheet

Method 1: Using A Formula

With this method, I am using a COUNTIF function to determine if there are 2 or more instances of a cell value. If multiple instances are found, then those rows are flagged with the phrase "Dup" (short for "duplicate"), but you can make your flag be any word that you wish.

How to find duplicate data in Excel using a formula

For a VBA-base solution to quickly flagging duplicates on your spreadsheets, Dan Wagner put together a great article and video on his blog entitled How to Count and Label Duplicate Values Without COUNTIF or Range.Find.

Method 2: Using Conditional Formatting

Did you know you can use a conditional formatting preset to flag duplicates? I didn't for the longest time and still remember the disbelief that this simple option had been available to me the whole time! All you need to do is highlight your cell data and navigate to

Home Ribbon Tab > Conditional Formatting > Highlight Cells Rules > Duplicate Values....

From there, you can simply hit the OK button when the dialog box pops up and any cells that are duplicates will be highlighted with a light red cell fill color. Have thousands of rows that you don't want manually scroll through to check for the red fill? Well, you can easily apply a filter to your data and see if you can filter on the red cell fill color.

This is a great method that is very fast to implement on data to see if there are any duplicate instances. I make a habit of using this process to check my ID columns that VLOOKUP formulas are pointed to, so I am certain I have unique values throughout the column.

How to use conditional to highlight duplicate values in Excel
Ho to format cells with duplicate values in Excel

Method 3: Using VBA Macro Coding

This wouldn't be a true Spreadsheet Guru article without a solution for all you VBA automators! The following is a neat little macro I whipped up that will determine if you have any duplicate values within your selected cell range.

After running the VBA macro code, you will get a message box that tells you how many duplicate cells were found and ask if you want to highlight them yellow. This would be a great addition to your Personal Macro Workbook (it's definitely in mine!).

Using VBA code to automate removing duplicate values within a selected cell range
Sub SearchForDuplicates()
'PURPOSE: Determine if there are duplicate values within the selected cell range
'SOURCE: www.TheSpreadsheetGuru.com
 
Dim rng As Range
Dim rngFind As Range
Dim cell As Range
Dim DupAddresses As String
Dim SearchList As String
Dim Delimiter As String
 
'Setup Variables
  Set rng = Selection
  Delimiter = "-;;-"
 
'Loop through each cell in selection
For Each cell In rng.Cells
  'Does cell have value?
    If cell.Value <> "" Then
      'Has value been searched for yet?
        If InStr(1, SearchList, cell.Value & Delimiter) = 0 Then
          SearchList = SearchList & cell.Value & Delimiter
          
          Set rngFind = rng.Find(what:=cell.Value, LookIn:=xlValues, _
            lookat:=xlWhole, searchdirection:=xlNext)
        
          If Not rngFind Is Nothing Then
            'Record first instance found
              FirstAddress = rngFind.Address
              
            'Find any next instances of value
              Do
                Set rngFind = rng.FindNext(rngFind)
                If rngFind.Address = FirstAddress Then Exit Do
                DupAddresses = DupAddresses & rngFind.Address & ","
              Loop
              
          End If
        End If
    End If
Next cell
 
'Report Results
  If DupAddresses <> "" Then
    Set rng = Range(Left(DupAddresses, Len(DupAddresses) - 1))
    UserAnswer = MsgBox(rng.Count & " duplicate values were found," _
      & " would you like them to be highlighted in yellow?", vbYesNo)
    If UserAnswer = vbYes Then rng.Interior.Color = vbYellow
  Else
    MsgBox "No duplicate cell values were found"
  End If
 
End Sub

**Note this code does not highlight the initial instance of a duplicate value. Only the 2nd instance and beyond are highlighted.

How To Delete Duplicate Data From Your Spreadsheet

Method 1: Use the Remove Duplicates Button

Did you know there is a dedicated button to remove duplicates in the Excel Ribbon? This is a really awesome functionality that can save you a ton of time. All you need to do is navigate to the Data tab in your Excel Ribbon. You can then click the Remove Duplicates button and fill out the dialog box to remove duplicates according to your specific needs. 

Remove Duplicates Ribbon Button

You can also access the Remove Duplicates button within the Table Tools - Design Ribbon tab if you are working with an Excel Table object.

Remove Duplicates Button with Excel Tables

Method 2: Using VBA Coding

The following VBA code shows how you can programmatically delete duplicate rows within your selected cell range without having to manually covert the range into a table. Don't worry, it will ask you if you are certain you want to delete the duplicate rows before permanently cleansing your data.

Using VBA coding to delete duplicate rows withing a selected cell range without converting the range into a table.
Sub DeleteDuplicates()
'PURPOSE: Delete any duplicate rows based on analyzing first column in selected range
'SOURCE: www.TheSpreadsheetGuru.com
 
Dim rng As Range
Dim rngFind As Range
Dim cell As Range
Dim DupAddresses As String
Dim SearchList As String
Dim Delimiter As String
 
'Setup Variables
  Set rng = Selection
  Delimiter = "-;;-"
 
'Loop through each cell in selection
For Each cell In rng.Columns(1).Cells
  'Does cell have value?
    If cell.Value <> "" Then
      'Has value been searched for yet?
        If InStr(1, SearchList, cell.Value & Delimiter) = 0 Then
          SearchList = SearchList & cell.Value & Delimiter
          
          Set rngFind = rng.Find(what:=cell.Value, LookIn:=xlValues, _
            lookat:=xlWhole, searchdirection:=xlNext)
        
          If Not rngFind Is Nothing Then
            'Record first instance found
              FirstAddress = rngFind.Address
              
            'Find any next instances of value
              Do
                Set rngFind = rng.FindNext(rngFind)
                If rngFind.Address = FirstAddress Then Exit Do
                Set rngFind = rngFind.Resize(1, rng.Columns.Count)
                DupAddresses = DupAddresses & rngFind.Address & ","
              Loop
              
          End If
        End If
    End If
Next cell
 
'Report Results
  If DupAddresses <> "" Then
    Set rng = Range(Left(DupAddresses, Len(DupAddresses) - 1))
    rng.Select
 
    UserAnswer = MsgBox(rng.Count & " duplicate values were found," _
      & " would you like to delete any duplicate rows found?", vbYesNo)
    If UserAnswer = vbYes Then Selection.Delete Shift:=xlUp
  Else
    MsgBox "No duplicate cell values were found"
  End If
 
End Sub

Any Others I Overlooked?

Are there any other methods that Excel offers that are even easier than the methods in this article? Do you know a more simplistic approach to accomplishing the tasks in my VBA macros? I want to hear your thoughts and learn from your expertise. Leave a note in the comments section below if you have any way to make the content in this article better! I look forward to reading your thoughts!!

Keep Learning

Chris Newman

Chris Newman

Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.