top of page

How to Remove Duplicates in Excel?

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.

How to Remove Duplicates in Excel using remove duplicates feature


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.



How to Remove Duplicates in Excel using remove duplicates feature 1

STEP 5: Click on the OK button.

How to Remove Duplicates in Excel result


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.

How to Remove Duplicates in Excel using advanced filter

STEP 4: In the Advanced Filter dialog box, select the Filter the list, in-place option.


STEP 5: Select the "Unique records only" option.


How to Remove Duplicates in Excel using advanced filter 2

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.


How to Remove Duplicates in Excel highlight duplicate values

STEP 5: Select "Duplicate Values" from the sub-menu.


STEP 6: In the dialog box, you can customize the formatting options for duplicate values.

How to Remove Duplicates in Excel highlight duplicate values

STEP 7: Click "OK," and Excel will automatically highlight all the duplicate values in the selected range.


How to Remove Duplicates in Excel highlight duplicate values result

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.

コメント


bottom of page