Find and Remove Duplicates in Excel

Duplicates in Excel can be a problem especially when you are needing a unique list of values. When you are working with large data sets finding duplicates becomes increasingly a problem. Fortunately Excel has some really neat tools to find these duplicates and remove them, this article will go through what to do to find and remove these duplicates in Excel.

How to Find and Remove Duplicates

Duplicates can be remarkably troublesome - quite often we need to find and remove them to cleanse our data and present unique values. Fortunately the Conditional Formatting feature has a find and highlight feature in built. The following will outline this process.

Finding Duplicates in a Single Excel Column

Excel Highlight duplicates


The list above has many duplicates and few unique values. To highlight all of the duplicate:

First Highlight the cells you want evaluated for duplicates.

duplicates in excel

On the Home Menu - Choose Conditional Formatting - Highlight Cell Rules - Duplicate Values

Highlight duplicates in excel

The default result looks like the below.

duplicates in excel

If you wanted to show those values which were not duplicated in colour change the dialog to the following:

excel highlighting duplicate values


Remove the Duplicates in Excel


In the above we looked at highlighting both duplicates and unique values in a single list. The following section will concentrate on removing those duplicates.

In our example our data is in a single column and we want to remove all the duplicates, 

Select the data.

Remove duplicates

Go to Data – Remove Duplicates.

Remove duplicates excel

The following dialog box will appear.

Remove Duplicates in Excel

Be sure to tick My data has headers, if you have selected the header.

Then click OK.

Remove Dups

The above summary appears and you click OK.

The list will be cleansed of the conditional formatting plus the duplicate values will be removed.

Remove all duplicates

The final result is only unique values in the list.