Excel Remove Duplicates

Excel Remove Duplicates (Table of Contents)

• Remove Duplicates in Excel
• How to Remove Duplicates in Excel?
• #1 – Using Conditional Formatting followed by Filter feature
• #2 – Using Remove Duplicates feature in Excel
• #3 – Using COUNTIf Function along with Filter feature in Excel

Remove Duplicates in Excel

It is very common to have a duplicate occurrence of data in a spreadsheet. There are many reasons and ways to remove duplicate dataset before analysis of data.

There are three methods of finding and removing duplicate data:

1. Using Conditional Formatting followed by Filter feature of Excel.
2. Using Remove Duplicate feature in Excel.
3. Using the COUNTIF function.

How to Remove Duplicates in Excel?

Let’s understand the working of removing excel duplicate values from a given dataset with the below examples.

You can remove duplicate records in a very simple and easy way to excel.

#1 – Using Conditional Formatting followed by Filter feature

For Conditional Formatting

• There are duplicate occurrences of data in the below table.

• Select a range of cells or table. Go to the Home Tab in the Excel Ribbon. Click on Conditional Formatting drop-down list. Select Highlight Cells Rules and then click on Duplicate Values.

• A duplicate Values dialog box will appear. Select either Duplicate or Unique option that you want to highlight. In this example, we have selected the Duplicate option.

• Choose color and font of the highlighted cells in values with a drop-down list and then click on OK.

• After using the Conditional Formatting feature, the table will look like below:

• To clear Conditional formatting:

Select a range of cells or table. Go to the Home Tab in the Excel Ribbon. Click on Conditional Formatting drop-down list. Select Clear Rules and then click on Clear Rules from selected cells.

 

How to use Filter for finding Duplicate values in Excel?

The Filter feature allows a user to filter selected data in the spreadsheet. The Advance filter feature allows copying of resulting filtered list to a new location.

• Select the columns heading of the table.
• Go to Data tab in the Excel Ribbon. Click on Filter.

The table will look like below:

• To remove duplicates from any column, click on the Filter drop-down icon in the column header. Click on Filter by Color and select Filter by Cell Color or Filter by Font Color.

This will show duplicated values which you may delete.

The output will be as shown below:

• How to clear Filter?

Select the column header of the table. Click on Filter.

Then the filter will be removed.

Points to Remember:

• The deletion in the above process is done manually.
• Conditional formatting is used to highlight duplicate cells whereas Filter->Filter by Cell Color or Filter by Font Color feature is used to filter highlighted duplicate cells in excel.

#2 – Using Remove Duplicates feature in Excel

• Select the range of cells or table. Go to the Data Tab in the Excel Ribbon. In the Data tool group Clicks on Remove Duplicates.

• Remove Duplicates dialog box will appear. To delete duplicate values, select one or more columns that contain possible duplicates. Here in the above example, we have selected Name and Code no columns, as both the columns, contain duplicate values and then click OK.

• A message box will be displayed, indicating a number of duplicate values were found and removed and how many unique values remain. In case of no duplicate value, it will display the message No duplicate values found and then click OK.

• The output will be as shown below:

Points to be Remember:

• It is an inbuilt feature in Data tool of MS-Excel.
• It removes Excel duplicate data permanently.

#3 – Using COUNTIf Function along with Filter feature in Excel

A COUNTIF function is an easy method of removal of duplicate values in excel. Depending on whether you want to find duplicate values with or without first occurrences, there will be a slight difference in writing the formula.

1. A formula to find duplicates in Excel including the first occurrence:

• Duplicate in words

=COUNTIF(range, criteria)>1

Here, a range is column A and topmost cell for criteria is A3. Input the formula shown below in C3 cell. Select the C3 cell and drag to copy the formula till C11. The result will appear as True or False.

• Duplicate in numbers

=COUNTIF(range,criteria)

Here, Range is column A and topmost cell for criteria is A3. Input the formula shown below in D3 cell. Select the D3 cell and drag to copy the formula till D11. The result will appear in count i.e. number.

2. A formula to find duplicates in Excel without first occurrence:

If you want to filter or remove excel duplicates after finding them, the above formula will create some confusion as it marks True to all the identical records. So, we have to use the following formula to keep unique values in the list and to delete all subsequent repetition of values. Let’s modify our Excel duplicate formula by using absolute (by freezing the cell by using $ sign) and relative cell references wherever appropriate.

• Duplicate in words

Here the unique value will be displayed as FALSE whereas the subsequent duplicate values will be displayed as TRUE.

• Duplicate in numbers

Here the unique value will be numbered as 1 whereas duplicate values will be numbered as 2.

• Use Filter along with the above cases for finding Duplicate values in excel

Select the columns header of the table. Go to Data tab in the Excel Ribbon. Click on Filter feature.

Drop down arrow will appear beside the column header.

To remove excel duplicates, click on the filter drop-down icon in the column header. Deselect Select All. Select True and then click on Ok.

This will show duplicated values which you may delete.

The same procedure to be followed while selecting the drop-down filter arrow of column Duplicates in Numbers. Here the difference is instead of True/False the option will be given in numbers i.e. 1,2.

Points to be Remember:

• Here, data are being removed manually using the correct formula.
• A filter is used to make the task easy.

Conclusion

It is always suggested to copy the original range of data or table to another worksheet or location before deleting or removing duplicates permanently from the worksheet.

Recommended Articles

This has been a guide to Remove Duplicates in Excel. Here we discuss how to Remove duplicates in excel using three different methods along with practical examples and downloadable excel template. You may also look at these useful excel tools –

1. Data Filter | Useful Basic Tool of Excel
2. Guide To Excel COUNTIF Function
3. Examples Of COUNTIF with Multiple Criteria
4. What is Relative Reference in Excel?