How to remove duplicates from a column of data in Excel
It’s not uncommon when adding or importing records that you may need to remove duplicates in Excel. Whilst filtering and deleting records to make a list of unique items is an effective solution, there is an even easier way.
Simply follow these steps to remove duplicates in Excel.
Step 1 Copy the column of data containing the repeated or duplicate items to a blank column.
Step 2 Highlight the copied column of data and select Data, Remove Duplicates.
Step 3 From dialog box remove the tick next to ‘My data has headers’ if you did not include a header at the top of your data.
Step 4 Press OK and all the duplicates will be removed.
A message displays at the end telling you how many duplicates have been removed.
Remove duplicates in Excel for more than one column
The Remove Duplicates in Excel feature also allows duplicates to be found in a combination of columns.
For example suppose from the data below you want to remove items where the Date and Country are the same value.
This time the data contains headers for clarity.
With both columns ticked duplicates are only removed if rows have the same Date and the same Country. The result is:
Note that ticking only the Country column and not ticking the Date column will display as before the first 8 unique countries.
Additional Resources
Using the Advanced Filter in Excel
How to highlight duplicates in Excel 2010?
How to remove duplicate records