Remove Duplicate Records in Excel

When you extract a large amount of data from a database, the first thing you may want to do is to clean the data and remove duplicates.

In this blog post, I will introduce you to two easy ways to remove duplicate values and get a list with unique records only in Excel.

You can download the practice file here > RemoveDup. It contains a list of all states in United States with each occurring more than once.

The classic way is to use [Filter]:
  • Go to [DATA] tab, and click [Advanced] near [Filter]


  • In the [Advanced Filter] dialogue box, check the box before [Unique records only] to hide duplicates.
  • You can either choose to [Filter the list, in-place] to filter the list in column A and then copy them to a new column, or choose to [Copy to another location] and make a list in another column.


The convenient way is to use [Remove Duplicates]:
  • Copy the list to another column – Important!! Otherwise you may just lose your original data
  • Select the whole column, then go to [DATA] tab, and click [Remove Duplicates]
  • Since we only have one column here, there is no need to select  columns, just click [OK]


  • This will also give you a summary and help you to find out how many duplicates are in your selected cells



As always, if  you have questions about this or other aspects of Excel, please stop by the Weigle Information Commons during my Excel office hours, and I will help to troubleshoot your problem!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s