How To Find And Remove Duplicate Excel Cell Data

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.

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.

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!).

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.

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 accomplish 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!!

How Do I Modify This To Fit My Specific Needs?

Chances are this post did not give you the exact answer you were looking for. We all have different situations and it's impossible to account for every particular need one might have. That's why I want to share with you: My Guide to Getting the Solution to your Problems FAST! In this article, I explain the best strategies I have come up with over the years to getting quick answers to complex problems in Excel, PowerPoint, VBA, you name it

I highly recommend that you check this guide out before asking me or anyone else in the comments section to solve your specific problem. I can guarantee 9 times out of 10, one of my strategies will get you the answer(s) you are needing faster than it will take me to get back to you with a possible solution. I try my best to help everyone out, but sometimes I don't have time to fit everyone's questions in (there never seem to be quite enough hours in the day!).

I wish you the best of luck and I hope this tutorial gets you heading in the right direction!

Chris :)