Duplicate values can clutter your Excel spreadsheets and make it difficult to find the information you need. Fortunately, there are several ways to remove duplicates in Excel.
There are several reasons why you might want to remove duplicates in Excel. For example, you might want to:
Clean up your spreadsheets and make them easier to read
Remove duplicate data to improve the accuracy of your calculations
Prepare your data for import into another program
How to Remove Duplicates in Excel?
Here, we will show you how to remove duplicates in Excel using the following methods:
Using the Remove Duplicates feature
Using the Advanced Filter
Using a VBA macro
Method 1: Remove Duplicates in Excel using Remove Duplicate Feature
This is the simplest way to remove duplicates in Excel. It allows you to select the range of cells that you want to remove duplicates from, and then Excel will remove all duplicate values from the selected range of cells.
Here's how to remove duplicates in Excel using Remove Duplicate Feature:
STEP 1: Select the range of cells that you want to remove duplicates from.
STEP 2: Click on the Data tab.
STEP 3: In the Data Tools group, click on the Remove Duplicates button.
STEP 4: In the Remove Duplicates dialog box, select the My data has headers checkbox if your range of cells includes headers. Also, select the name column.
STEP 5: Click on the OK button.
Method 2: Remove Duplicates in Excel using Advanced Filter
The Advanced Filter is a more powerful tool that allows you to remove duplicates based on specific criteria. You can select the range of cells that you want to remove duplicates from, and then specify which columns you want to remove duplicates from.
Here's how to remove duplicates in Excel using Advanced Filter:
STEP 1: Select the range of cells that you want to remove duplicates from.
STEP 2: Click on the Data tab.
STEP 3: In the Sort & Filter group, click on the Advanced button.
STEP 4: In the Advanced Filter dialog box, select the Filter the list, in-place option.
STEP 5: Select the "Unique records only" option.
STEP 6: Click on the OK button.
Method 3: Remove Duplicates in Excel using VBA Marcos
If you want to remove duplicates automatically, you can use a VBA macro. This is a more complex method, but it allows you to remove duplicates from a range of cells without having to manually select the cells or specify the criteria.
Sub RemoveDuplicates()
'Declare variables
Dim rng As Range
Dim i As Integer
'Set the range of cells to remove duplicates from
Set rng = Range("A1:A100")
'Loop through the range and remove duplicates
For i = 1 To rng.Rows.Count - 1
If rng(i).Value = rng(i + 1).Value Then
rng(i + 1).Delete
End If
Next i
End Sub To run this macro, press Alt+F11 to open the Visual Basic Editor. Then, paste the code into a new module and press F5 to run the macro.
Highlight Duplicate Values
To identify duplicate values in your spreadsheet, you can use conditional formatting. This feature allows you to visually spot duplicate entries quickly. Follow these simple steps to highlight duplicate values:
STEP 1: Select the range of cells in which you want to find duplicates.
STEP 2: Go to the "Home" tab on the Excel ribbon.
STEP 3: Click "Conditional Formatting" in the "Styles" group.
STEP 4: Choose "Highlight Cells Rules" from the dropdown menu.
STEP 5: Select "Duplicate Values" from the sub-menu.
STEP 6: In the dialog box, you can customize the formatting options for duplicate values.
STEP 7: Click "OK," and Excel will automatically highlight all the duplicate values in the selected range.
Conclusion
Removing duplicates in Excel is a straightforward process that can help you clean and organize your data effectively. By following the appropriate steps and carefully reviewing the results, you can confidently eliminate duplicate values, improve data quality, and enhance the overall efficiency of your Excel worksheets.