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]

1

  • 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.

2

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]

3

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

4

 

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:

WordPress.com Logo

You are commenting using your WordPress.com 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