Remove Duplicates

The tool Remove Duplicates

Since 2007, you can remove duplicates values with this tool in the ribbon Data (Data > Remove Duplicates).

Menu to remove duplicates

To extraction without duplicates, you follow these 2 steps:

  • Select the column you want to remove duplicates in
  • Click on the tool Remove Duplicates

In the next dialog box, indicate if the first row of your selection is the header of your column. If so, this row will not be included in the process to remove duplicates.

Dialog box to remove duplicates

Click on OK. The process to remove the duplicates starts immediately.

At the end of processing, a dialog box shows you the number of rows deleted and the number of unique values remains.

Dialog box with the number of rows removed

Remove on multi-columns

This tool is really impressive because it also works if you select more than 1 column.

For instance, in this exercise, you want to extract the unique Last Name + First name.

Example with 2 columns to remove duplicates
  1. Select your 2 columns
  2. Activate the tool Remove duplicates
  3. Check the option My data has headers
  4. Click OK

The result is:

Example to remove duplicates with many columns

Excel 2003 and before

Before Excel 2007, it was also possible to remove duplicates but the tool was not easy to find. The tool is in the menu Data>Filters>Advanced filter

In addition, you can specify if you want to display the results in the same place or display them in a different location.

  1. Start by activating the Data menu>Filters>Advanced Filter
  2. Specify the cell range that will be filtered.
  3. Choose the Copy to another location menu and select a destination area where your list will be displayed after duplicates have been removed.
  4. Finally, check the box Unique records only.
Remove duplicates with Excel 2003 and before

Complex situation

But in some situation, you can not used this tool. In this example, all the rows look the s

It's not because there is a bug but because the data are too complex, like in this example.

Situation where the tool remove duplicates can not be used

Related posts


Permanent link to this article: https://www.excel-exercise.com/remove-duplicates/

Leave a Reply

Your email address will not be published.