We often see some duplicate data in excel, and if we want to delete the duplicate data and keep only one copy, we need to find out all duplicate values from table and then remove them from table. This article will help you to know how can we find out these duplicate values and remove them by some different methods like Remove Duplicates Function or VBA Macro.

First, let’s see the example below, a Product List contains some duplicate data. Notice that ‘Pen’, ‘Ball’ and ‘Cap’ are displayed twice in this list.

Find Duplicate Data in Excel

---

Step 1: Select the range you want to filter and display the duplicate data. This step is required otherwise the following operations will not take effective.

Step 2: Click Home->Conditional Formatting->Duplicate Values.

Step 3: On pops up Duplicate Values dialog, keep default selection Duplicate in dropdown list, you can select different format to show duplicate values from ‘values with’ dropdown list.

 

Step 4: Click OK. Verify that duplicate data is marked with the format we set in Duplicate Values settings.

You can directly remove the duplicate data in the table and only keep one copy. Or you can follow below steps to remove them directly.

Remove Duplicate Data in Excel

---

Method 1: Remove Duplicate Data by Remove Duplicates

Step 1: Select the range you want to remove the duplicate data.

Step 2: Click Data->Remove Duplicates.

Step 3: In pops up ‘Remove Duplicates’ dialog, verify that ‘My data has headers’ option is checked by default, table header ‘Product’ is default checked under Columns. If they are not checked by default, or you have several columns, you can check on the headers you want to remove duplicate data per your demands (See notes for details).

Step 4: Click OK. Verify that duplicate data is removed properly.

Notes:

If there are server columns in a table, you check on different columns to remove duplicate data based on different conditions. See example below.

In ‘Remove Duplicates’ dialog, select ‘Unselect All’, then check on column header per you demands. First we only check on ‘Name’ and let’s see the result.

Verify that only the total line for duplicate name ‘David’ is removed.

Only check on Class A this time.

Verify that only one copy of A, B, C for Class A is remained in the table.

Method 2: Remove Duplicate Data by VBA Code

If you are familiar with coding, you can remove duplicate data by edit VBA code in excel.

Step 1: Click Developer tab->Visual Basic or Alt+F11 to load Microsoft Visual Basic for Applications window.

Step 2: Click Insert->Module, then paste below code into the module window.

Sub RemoveDuplicateData()

    Dim rng As Long: rng = Range("A" & Rows.Count).End(xlUp).Row

    ActiveSheet.Range("A1:A" & rng).RemoveDuplicates Columns:=1, Header:=Product

End Sub

See screenshot below:

Notes:

1. A Indicates column A;
2. A1:A indicates the list range;
3. Columns: Column number of the selected range, in this case columns:=1;
4. Header: Column header, in this case header is Product.

You can change above parameters per your request.

Step 3: Click F5 directly to Run Macro. Or you can click Developer->Macros, select RemoveDeplicateData we just created, then click Run.

Step 4: Check Run result. Verify that duplicate data is removed.