Categories
Excel Training Hints & Tips

Remove Duplicates in Excel

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.

RemoveDuplicates

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.

RemoveDuplicatesDialog
Remove Duplicates in Excel

 

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.

RemoveDuplicatesResult2

 

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.

RemoveDuplidcatesMultiCols2

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:

RemoveDuplicatesMultiColResult

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

 

 

By Richard Bailey

I love what I do; I get to work with an outstanding team to help hundreds of people with their challenges. I’ve learnt a lot from the teams I’ve worked with, no matter the size or industry we all have challenges to overcome, difficult customers, creating a budget or keeping a project on track.