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.
- Removing Duplicates From A List In Microsoft Excel
- Removing Duplicates in Excel 2003 lists
- Removing Duplicates in Excel 2007 and 2010 (this blog)
Posted by Andrew Gould on 12 October 2011 | no comments
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 in Excel 2007 and 2010
In Excel 2007 Microsoft introduced a dedicated tool for removing duplicates, cunningly called the Remove Duplicates tool. Using this tool is relatively simple but is still worth a look at how it works. In the diagram below is a short list of films:
The duplicate entries are shaded in green - we want to get rid of two of these rows.
Using the Remove Duplicates Tool
To remove duplicates from a list in Excel 2007 or 2010:
- Select the list containing the duplicates you want to remove, or just click on a single cell somewhere in the list.
- From the Ribbon select: Data -> Remove Duplicates
3. Choose whether the list has column headings.
When you click OK you'll be presented with a dialog box telling you how many rows have been deleted.
The dialog box tells you how many rows were deleted.
Take care when removing duplicates using this method because the duplicate rows are deleted from the list rather than simply hidden as in Excel 2003.
Removing Duplicates When Some Columns Have Different Data
Sometimes you will want to remove duplicate rows from a list where not every column contains the same information. Look at the example below:
In this example we've recorded the date and time that each film was added to the list. There are three records where the film data is duplicated but the date and time is different.
You can choose to omit certain columns when Excel compares rows to determine which records to delete. For this example we'd use the settings shown below:
Here we'd simply choose not to include the Added on column when comparing the rows.
The end result is that we'd delete the rows where only the Title, Release Date and Director values are the same.
Excel keeps the first instance of the rows that contain duplicates.
Avoiding Deleting Duplicates
If you weren't sure that you wanted to delete your duplicate values but would rather hide them instead, you can still use the same technique that works in Excel 2003. To do this:
- Select the list or a single cell within the list.
- From the Ribbon select: Data -> Advanced...
You'll see the same dialog box as in Excel 2003. Simply check the Unique records only box to hide the duplicates rather than delete them.