BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
If you've ever had the tedious job of manually scrolling through a long list of data to remove the duplicate entries in Excel then this blog is for you! Discover how to hide or delete duplicated data in Excel 2003, 2007 and 2010.
Posted by Andrew Gould on 12 October 2011
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Removing Duplicates From A List In Microsoft Excel
When you're working with long lists of data in Microsoft Excel it's common to want to check for and remove duplicate entries. Of course it's possible to do this by hand, scrolling through your list and manually deleting the rows containing duplicate data, but it's much easier to get Excel to do this for you!
What Does Excel Consider as a Duplicate?
Before you start trying to remove duplicate values from a list, it's worth considering what Excel actually sees as a duplicate entry. Have a look at the short list of films in the diagram below:
In this list there are three versions of King Kong, but none of them are duplicates.
In the list above there are three versions of the film King Kong. If we asked Excel to remove duplicates from this list you might be surprised to find that by default none of the King Kong films would actually be removed. This is because although the titles of the films are the same, each row has different information in the Release Date and Director columns.
If you're working in Excel 2007 or 2010 you can specify which columns are used to establish whether a row is a duplicate or not.
Differences Between Excel Versions
Removing duplicates from a list works very differently in different versions of Excel. In Excel 2007 and 2010 there is a dedicated Remove Duplicates tool for deleting duplicates in a list. In Excel 2003 you must use the Advanced Filter tool in order to remove duplicates.
In Excel 2003 removing duplicates from a list merely hides the rows that contain duplicates. In Excel 2007 and 2010 removing duplicates actually deletes the data from the list and shifts the remaining rows to fill in the blank spaces that are left behind.
The next part of this blog explains how to remove duplicates from a list in Excel 2003, before we move on and look at how to do the same thing in Excel 2007 and 2010.