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.
You can also access the Remove Duplicates button within the Table Tools – Design Ribbon tab if you are working with an Excel Table object.
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 SubAny 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!!
Learn my best Excel chart design hacks. These will turn your ugly charts into professionally crafted graphics, leaving your boss speechless!!!
Keep Learning
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.