How To Remove Duplicates In Excel

Remove Duplicates

The Remove Duplicates button is a quick and easy way to remove duplicates in a data series. It is a simple matter of highlighting all relevant cells and pushing the ‘Remove Duplicates’ button within the Data ribbon.

Remove duplicates on ribbon

Another useful capability of this button is to identify unique cells within a range of cells so the duplicate removal can be tailored to your requirements.

This is demonstrated below in two examples:

1.     Removing duplicates in 1 column

2.     Removing duplicates over 2 columns

Remove Duplicates Based On 1 Column

Select the data range and click on the Remove Duplicates button.  The menu below will pop up that populates the column headings. Where your data has headings, click on ‘My data has headers’ and this will exclude the header from your data range.

For example, by ticking only the ‘Asset’ column, the duplicates within column A will be removed. In the example below, rows 3, 4, 5, 7 and 9 will all be deleted because they are duplicates.

Remove duplicates menu

Remove Duplicates Based On 2 (Or More) Columns

In some instances, a duplicate may be defined over a number of columns.  In the example above, although rows 2 to 5 are all BTC, there are 3 different unique numbers next to each BTC. Under this condition, the only duplicate above in both ‘Asset’ and ‘Number’ is BTC and 1.  In order to filter over more than one column, simply select more columns in the Remove Duplicate menu in the image above.

There you have it, a quick tip on removing duplicate data.

Try this for yourself next time you are dealing with large data sets and want to quickly remove duplicates.

You may also be interested in Why Is Trim Not Removing Spaces In Excel?

Video Guide