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.

x
How to Limit Data Entry in a Cell in Excel

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

 

Related Posts

VBA Macro For VLOOKUP From Another Sheet
vba macro for vlookup from another sheet1

In the previous post, you should know that how to fix or remove the #N/A error when using VLOOKUP formula to lookup value from another sheet. And this post will show you how to use VBA code to vlookup data ...

How To Insert Comments in Protected Worksheet in Excel
insert commetns in protected workshee3

This post will show you how to allow comments in a protected worksheet in Excel. You can easily to insert comments into cells in a normal worksheet in Excel, but if want to insert a comment in a worksheet that ...

How To Convert Text to Upper Cases(Using VBA) in Excel
convert text to upper cases1

This post will show you how to switch from lower case to upper case in Excel. and I am going to show you two different ways of converting text to upper cases using formula or VBA macro in Excel 2013,Excel ...

How To Hide Every Other Row in Excel (Using VBA)
hide every other row1

This post will show you how to hide alternate rows or columns in Excel or how to hide every third, fourth, fifth row or column in Excel. If you want to hide every other row in your current worksheet, how ...

How to Disable the Save As Prompt in Excel
disable save as prompt1

This post will show you how to use a VBA Macro to save an Excel file and overwrite any existing file without a prompt so that you are going to get the little window that says file already exists do ...

How to Count Cells that Contain even or odd numbers in Excel
count cells that contain even numbers5

This post will guide you how to count the number of cells that contain odd or even numbers within a range of cells using a formula in Excel 2013/2016.How do I count cells that contain odd numbers through the use ...

How to Count Cells that Contain negative Numbers in Excel

This post will guide you how to count the number of cells that contain negative numbers within a range of cells using a formula in Excel 2013/2016.You can count the number of negative numbers in your data using easy functions ...

How to Count Cells Are Not Blank or Empty in Excel
count non blank nonempty cells5

This post will guide you how to count cells that are not blank or empty in a given range cells using a formula in Excel 2013/2016.How do I count the number of cells that are not blank in a particular ...

How to Count Cells Less Than a Specific Value in Excel
count cells lessr than5

This post will guide you how to count the number of cells less than a particular numeric value in a given range cells using a formula in Excel 2013/2016. How do I count cells that are less than a specific ...

How to Count Cells Greater Than a Specific Value in Excel
count cells greater than 5

This post will guide you how to count the number of cells greater than a particular numeric value in a given range cells using a formula in Excel 2013/2016. How do I count cells that are greater than a specific ...

Sidebar