Remove Duplicates feature

You will sometimes want to retrieve unique values from a data set – i.e., to find and remove duplicate values. While there are a few ways to do this, the most effective option is Excel built-in feature Remove Duplicates.

Remove Duplicates feature removes duplicate rows from the selected columns.

Duplicate rows are deleted in this process.

The simplest example of this process is removal of duplicate values from a single column:

Remove Duplicates single column

In this example, we’ve started with the data in the column B. As we don’t want to destroy our original data, first we’ve copied our data to the column G and then removed the duplicates.

Next, we’ve selected the data we want to remove duplicates from, and than followed the steps shown above.

Data that was deleted in the process is marked orange:

Remove Duplicates single column - result

 

Removing data from multiple columns is very similar:

Remove Duplicates from multiple columns

However, results are very different – notice that the third occurrence of the text “green” was not removed as the combination of “green” and “inactive” is unique:

Remove Duplicates from multiple columns - result

Also notice that we once again first copied our data and only than we’ve proceeded with duplicate removal. Had we not done so, our table would be broken.

 

If your columns have headers, you can select the My data has headers option – however, this only does something if the value in your header repeats below the header and you also want to keep it twice for that reason:

Remove Duplicates My data has headers

 

Selecting the whole columns and then using Remove Duplicates also makes little difference:

Remove Duplicates whole columns

 

Remove Duplicates feature does not support removal of duplicate columns from the selected rows. If our data is structured in such way, we will first have to transpose it.

We start by copying our data:

Remove Duplicates - duplicate columns from selected rows - step 1

We next paste our data as Paste Special, Transpose:

Remove Duplicates - duplicate columns from selected rows - step 2

We can than remove duplicates as usual:

Remove Duplicates - duplicate columns from selected rows - step 3

If needed, we can Paste Special, Transpose it back:

Remove Duplicates - duplicate columns from selected rows - step 4

One thought on “Remove Duplicates feature”

Leave a Reply