How to Remove Duplicates from a List in Microsoft Excel

https://www.wiseowl.co.uk/blog/s177/how_to_remove_duplicates_from_a_list_in_excel_pt2.htm

 How to Remove Duplicates from a List in Microsoft Excel

 

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:

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

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:

1. Select the list or a single cell within the list.
2. 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.

And that's the end of this blog!  There are lots more resources on training in Excel here.