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.
The following YouTube video takes you though the process in this page. The file is below.
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
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.
On the Home Menu - Choose Conditional Formatting - Highlight Cell Rules - Duplicate Values
The default result looks like the below.
If you wanted to show those values which were not duplicated in colour change the dialog to the following:
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.
Go to Data – Remove Duplicates.
The following dialog box will appear.
Be sure to tick My data has headers, if you have selected the header.
Then click OK.
The above summary appears and you click OK.
The list will be cleansed of the conditional formatting plus the duplicate values will be removed.
The final result is only unique values in the list. The following file contains the raw file for this tutorial.