Microsoft training courses | Wise Owl - home page

Phone (01457) 858877 or email

HOW TO REMOVE DUPLICATES FROM A LIST IN MICROSOFT EXCEL

Part three 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
  3. Removing Duplicates in Excel 2007 and 2010 (this article)
Posted by Andrew Gould on 12 October 2011 | no comments

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:

List with duplicates

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:

  1. Select the list containing the duplicates you want to remove, or just click on a single cell somewhere in the list.
  2. From the Ribbon select: Data -> Remove Duplicates
Removing duplicates in Excel 2007 or 2010

3. Choose whether the list has column headings.

4. Tick the boxes for the columns you want to use to establish whether a row is a duplicate.

5. Click OK

 

When you click OK you'll be presented with a dialog box telling you how many rows have been deleted.

The deleted duplicates

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:

Duplicates with different data

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:

Omitting a column

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.

Deleted duplicates

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:

  1. Select the list or a single cell within the list.
  2. From the Ribbon select: Data -> Advanced...
Filtering a list

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.

 

HOW TO REMOVE DUPLICATES FROM A LIST IN MICROSOFT EXCEL

Part three 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
  3. Removing Duplicates in Excel 2007 and 2010 (this article)

Comments on this blog

This blog currently has no comments.

All content copyright Wise Owl Business Solutions Ltd 2014. All rights reserved.