How to Find and Remove Duplicate Data in Excel

,

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 and Remove Duplicate Data 1

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.

Find and Remove Duplicate Data 2

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.

Find and Remove Duplicate Data 3

Find and Remove Duplicate Data 4

 

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

Find and Remove Duplicate Data 5

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.

Find and Remove Duplicate Data 6

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).

Find and Remove Duplicate Data 7

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

Find and Remove Duplicate Data 8

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.

Find and Remove Duplicate Data 9

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.

Find and Remove Duplicate Data 10

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

Find and Remove Duplicate Data 11

Only check on Class A this time.

Find and Remove Duplicate Data 12

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

Find and Remove Duplicate Data 13

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:

Find and Remove Duplicate Data 14

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.

Find and Remove Duplicate Data 15

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

Find and Remove Duplicate Data 16