How to Find and Remove duplicates in Excel

Duplicate values are the same value or set of the same values that most frequently appear in the Excel document. Duplicate values generally occur when we import data from other resources.

Microsoft Excel provides a powerful feature for finding and removing duplicates in the document:

Find duplicates in Excel

There are the following methods to find duplicates in Excel -

Method 1: Find Duplicates in a Single Column using Conditional Formatting

In a Microsoft Excel document, the Conditional Formatting option is used to find and highlight the duplicate data.

Steps to find duplicates in Excel

There are the following easiest steps to find duplicates in Excel document -

Note: We use the below steps to find duplicates in Microsoft Excel 2007, Excel 2010, Excel 2013, Excel 2016, and Excel 2019.

Step 1: Open a new Microsoft Excel document (File -> New -> Blank document -> Crreate) or an existing Microsoft Excel Document (File -> Open -> Browse location -> Open button).

Step 2: Select or Highlight the number of cells in which you want to find duplicate cells.

Step 3: Go to the Home tab and click on the Conditional Formatting option in the Styles section.

Step 4: Click on the Highlight Cells Rules button. A list of options will appear in which click on the Duplicate Values.

Step 5: A Duplicate Values dialog box will appear on the screen. On the left, select Duplicate from the drop-down menu. Click on the values with drop-down button and then select the colors to view duplicate values in the Excel document.

Note: In our case, we use Green Fill with Dark Green Text option to view duplicate values.

The below-given screenshot shows the duplicate text with our select color.

Method 2: Separate Unique values in separate cell or sheet using the Advanced Filter dialog box

Microsoft Excel also provides an excellent Advanced Filter dialog box method to find/separate unique values in the separate cell.

Follow the below given easiest steps to find duplicates-

Step 1: Open a new or an existing Microsoft Excel document in which you want to separate the unique values.

Step 2: Highlight the range that contains records (values).

Step 3: With the selected range, Go to the Data tab on the Ribbon and click on the Advanced Filter option under the Sort & Filters section.

Step 4: An Advanced Filter dialog box will appear on the screen in which do the following -

1. Click on the Copy to another location radio button to separate the unique values in the cells.
2. Select or type your list range in the List range: section.
3. Select or Type the location in the Copy to: text box where you want to view the unique values.
4. Tick the Unique records only check box.
5. Click on the OK button at the bottom of the Advanced Filter dialog box. See the screenshot given below.

Now, you can see that all unique values will appear in the newly selected location.

Remove duplicates in Excel

Remove duplicates feature is used to remove duplicate permanently.

Method 1: Remove duplicates using the Remove Duplicates command

A list of Steps to Remove duplicates from Excel document is discussed below -

Step 1: Open a new or an existing Microsoft Excel document.

Step 2: Click the Data tab on the Ribbon and then click on the Remove Duplicates button in the Data Tools section.

Step 3: A Remove Duplicates dialog box will appear on the Excel document. Click on the Select All button, tick on the column name in which you want to remove duplicates. At the last click on the OK button.

Step 4: A small Microsoft Excel dialog box pops up saying 3 duplicate values found and removed; 11 unique values remain. Click on the OK button in the Microsoft Excel dialog box.

All duplicate values are removed from the Excel document. Consider the screenshot given below.

Method 2: Remove duplicates using Advanced filters

This is another simple as well as the easiest method to remove duplicates from the Excel document.

Steps to Remove duplicates

Step 1: Open an Excel document in which you want to remove duplicates.

Step 2: Click on the Data tab, and under the Sort & Filter section click on the Advanced tab.

Step 3: An Advanced Filter dialog box will appear on the screen in which do the following -

1. Click on either Filter the list, in-place or Copy to another location radio button.

Note: In our case, we use the Filter the list, in-place radio button.

2. Enter the List Range to remove duplicates.

3. Click on the check box associated with the Unique records only.

4. Press the OK button in the Advanced Filter dialog box.

The snapshot mentioned below shows that all duplicate values are removed from the Excel document.