The Excel Addict - Remove Duplicate Values From Your Data in Seconds

Home Recent Posts Newsletter Training Add-Ins Testimonials About

Remove Duplicate Values From Your Data in Seconds

by Francis Hayes (The Excel Addict)

When you need to remove duplicates from a list, that could mean deleting records based on duplicate values in a single column or based on duplicates found in two or more columns.

Click here to download practice file for this tip

Remove Duplicates in Microsoft Excel 2007 2010 2013 2016 365

Beginning with Excel 2007, a Remove Duplicates option was added that has saved Excel users countless hours of manually deleting duplicate records.

Just Highlighting the Duplicates
If instead of removing the duplicates you'd prefer just to highlight them, click here to see how to Easily Highlight Duplicate Values.

Since the Remove Duplicates feature deletes the duplicate values from your data, it's always a good idea to make a backup copy of your original data, just in case you later discover that you need to retrieve it. A simple way to backup your list is to copy the sheet tab by holding down the CTRL key and dragging the tab to the right.

Quickly copy a sheet tab in Microsoft Excel

If your data is in an Excel Table, when you choose the Remove Duplicates option, the entire Table will be automatically selected and, based on the columns you select, the entire records (i.e. entire rows) related to those duplicates will be removed from the table .

When removing duplicates from a 'normal' range of cells you need to be careful. If you select only a single column within the range, you will be given a warning that there is data next to your selection that you may want to include. You can then choose whether to expand the selection or not.

Expand Selection Remove Duplicates in Microsoft Excel 2007 2010 2013 2016 365

However, if you select two or more columns within the range, you won't get that warning and only those cells within the selected columns will be removed.

Remove Duplicates No Warning in Microsoft Excel 2007 2010 2013 2016 365

In the example above, if you highlight only the data in columns B and C and choose Remove Duplicates, the adjacent cells in columns A and D won't be removed. As you can see, if the data in columns A and D are related to the cells in columns B and C, as is usually the case in Excel, this can really mess up your data.

Some people think they first need to select the column(s) of data which they want to remove duplicates from. However, it is a better practice to select just a single cell in the range and when you choose the Remove Duplicates option, the entire range will be selected and you then will be given the option to choose which column(s) you want to base your duplicates on.

1) Select a single cell in your data;

2) From the Data tab, in the Data Tools group, click Remove Duplicates;

3) In the Remove Duplicates dialog, all columns are checked by default. This means that if there are rows with duplicate values in every column, duplicate rows subsequent to the first instance are removed from the Table or selection.

Remove Duplicates All Columns in Microsoft Excel 2007 2010 2013 2016 365

To remove the entire rows for which only specific columns contain duplicate values, deselect the columns you don't want checked for duplicates. If your working with a lot of columns and you want to select only a few columns, you may find it easier to click the Unselect All button first and then select only those columns you want checked for duplicates;

Remove Duplicates Select Columns in Microsoft Excel 2007 2010 2013 2016 365

4) Click OK. A message will be displayed indicating how many duplicate values were removed and how many unique values remain.

Duplicates Removed Confirmation Dialog in Microsoft Excel 2007 2010 2013 2016 365

Note that at this point you do have the option to Undo (CTRL+Z) the Remove Duplicates action.

If you found this tip helpful, please share it with your friends and colleagues.

To get more tips every week like this one...

Sign up for my FREE twice-weekly Newsletter
'Spreadsheet Tips From An Excel Addict'
'Excel in Seconds' & 'Excel in Minutes'

Plus you also get my 'Excel in Seconds' E-book as a BONUS!

(Download it immediately after you sign up)

Home Recent Posts Newsletter Training Add-Ins Testimonials About