Do you have data full of duplicate values that you wish to clean? It can be annoying to have repeated values in your worksheet. Luckily, Excel has inbuilt features to handle just that. You can take advantage of these features to remove duplicates in Excel easily.
In this guide, I will show you different methods that you can use.
Using Conditional formatting
As the name suggests, this feature allows you to format specific cells, columns, or rows if certain conditions are met. Since you want to get rid of the duplicates, you can set Excel to highlight in a different color the repeated values. Follow these simple steps.
Assuming you want to remove duplicates from the dataset below
1. Highlight the range containing your data
2. Navigate to the styles group under the home tab and click on conditional formatting
3. On the drop-down menu that appears select Highlight Cell Rules>Duplicate Values
4. Set the color that will be used to highlight the duplicate values on the pop
5. Press Okay to apply changes
As you can see from the picture above, Excel has highlighted the duplicate values, which are John and Mary
6. To remove the duplicates, Click on Data Tab>Remove Duplicates
7. Excel will ask you if you want to expand the selection or continue with the current selection
Since you probably don’t want to keep the related data of the duplicates, select expand the selection.
After selecting expand the selection, you can now click Remove duplicates
8. The next pop-up will ask to confirm if your data has headers and for you to select one or more columns with duplicate values
9. Press Okay, and all the duplicate values will be removed successfully
10. Press okay on the dialog box, and viola, you are left with clean data
Download Free Practice Sheet
VBA code to find and remove duplicates in Excel
You can easily remove duplicates in Excel using VBA.
Follow these simple steps.
1. Launch the spreadsheet containing data with duplicates
2. Open the VBA editor by clicking on Developer Tab > Visual Basic. You can also launch the developer tab by using shortcuts such as Alt+F11 on Windows or fn+f11 on Mac. (Follow this guide to learn how to add a developer tab in Excel)
3. While at the editor, click on Insert on the navigation menu, then select Module from the list of options
4. Copy the following code and paste it into the VBA code editor
Sub DeleteDuplicates()
Dim ws As Worksheet
Dim rng As Range
Dim lastRow As Long
' Set the worksheet to work with
Set ws = ThisWorkbook.ActiveSheet
' Prompt the user to select the range
On Error Resume Next
Set rng = Application.InputBox("Select the range to delete duplicates", Type:=8)
On Error GoTo 0
' Check if a range was selected
If rng Is Nothing Then
MsgBox "No range selected. Exiting the macro.", vbExclamation
Exit Sub
End If
' Delete duplicates in the range
With rng
.RemoveDuplicates Columns:=1, Header:=xlNo
End With
MsgBox "Duplicates have been deleted.", vbInformation
End Sub
5. Run the code. (Excel Will prompt you to choose a range that you wish to delete duplicates. You can use the mouse to select the range interactively.)
6. Press okay, and Excel will remove the duplicates automatically.
VBA code to highlight duplicates
Suppose you are only looking to find the duplicates. You can use VBA only to highlight the duplicates.
Follow the steps above but use the following VBA code instead.
Sub HighlightDuplicates()
Dim ws As Worksheet
Dim rng As Range
Dim lastRow As Long
Dim cell As Range
' Set the worksheet to work with
Set ws = ThisWorkbook.ActiveSheet
' Prompt the user to select the range
On Error Resume Next
Set rng = Application.InputBox("Select the range to highlight duplicates", Type:=8)
On Error GoTo 0
' Check if a range was selected
If rng Is Nothing Then
MsgBox "No range selected. Exiting the macro.", vbExclamation
Exit Sub
End If
' Clear any existing conditional formatting
rng.FormatConditions.Delete
' Apply conditional formatting to highlight duplicates
With rng
.FormatConditions.AddUniqueValues
.FormatConditions(.FormatConditions.Count).SetFirstPriority
.FormatConditions(1).DupeUnique = xlDuplicate
.FormatConditions(1).Interior.Color = RGB(255, 0, 0) ' Adjust the color as needed
End With
MsgBox "Duplicates have been highlighted.", vbInformation
End Sub
Final Thoughts
In this article, we have covered how to remove duplicates in Excel. You can either use conditional formatting or the VBA method. Depending on your skill level, these two methods work perfectly. You can use the practice sheet to follow along with the tutorial or exercise, removing duplicates.
