How to Remove Duplicates from a List in Microsoft Excel
Part two of a three-part series of blogs

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.

  1. Removing Duplicates From A List In Microsoft Excel
  2. Removing Duplicates in Excel 2003 lists (this blog)
  3. Removing Duplicates in Excel 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 in Excel 2003

In Excel 2003 you can only remove duplicates from a list if an entire row of data is duplicated.  In the diagram below we've highlighted the rows that Excel 2003 sees as duplicates:

Duplicates in Excel 2003

The duplicate rows are shown here shaded in green.


Using the Advanced Filter

To start removing duplicates:

  1. Either select the whole list of data, or just select a single cell within the list.
  2. From the menu select: Data -> Filter -> Advanced Filter...
  3. Complete the dialog box as shown in the diagram below.
Filtering the list to show duplicates

4. Choose whether to delete the duplicates from the original list (Filter the list in place) or copy the unique records to a new position (Copy to another location).


When you click OK you should find that you are left with one copy of any duplicate rows in the list.

The unique values

The list now shows only one entry for the duplicated data.


What Happens to the Duplicates?

In Excel 2003 the duplicated data isn't truly removed from the list, rather it is filtered out by hiding the rows containing duplicate values.  If you look at the row numbers on the left hand side of the diagram above you'll notice that number 7 and number 11 are missing.

Because the duplicates aren't actually removed from the list, you can easily bring them back again.  To do this:

  1. Select the list or a single cell within the list.
  2. From the menu select: Data -> Filter -> Show All

You should find that you see all of the records again.

If you want to truly delete the duplicate values in Excel 2003, the easiest way to do it is to choose the option Copy to another location within the Advanced Filter dialog box.  This will copy the unique values to a different set of cells.  You can then delete the original list completely and then optionally copy the unique values back into the cells that were previously occupied by the original list.

What's Next?

Deleting duplicates from the list is rather awkward in Excel 2003. Read the next part of this blog to see how easy the process is in Excel 2007 and 2010.

This blog has 0 threads Add post