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 the Excel built-in feature Remove Duplicates.

The Remove Duplicates feature removes duplicate rows from the selected columns.

Duplicate rows are deleted in this process.


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

Remove Duplicates single column

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

Next, we’ve selected the data we want to remove duplicates from and 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 then have we proceeded with duplicate removal. Had we not done so, our table would have been 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


The Remove Duplicates feature does not support the removal of duplicate columns from the selected rows. If our data is structured in such a 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 then 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


The UNIQUE function replicates and extends Excel’s Remove Duplicates feature in formula form, allowing us to return a list of unique values in a list or range.


Dig deeper:

Sort & Filter in Excel

3 thoughts on “Remove Duplicates feature”

Leave a Reply