November 25, 2020

How to remove duplicate values in Excel

If you want to remove all duplicate values from a table in excel please follow the below steps.

Step 1 : In the following example we have a table containing 2 columns. There are duplicate rows as follows.

Row 3 & 6 are duplicate : triangle – nachos

Row 4 & 9 are duplicate : square – field

Row 7 & 10 are duplicate : rectangle – book

Please note, when we say the row as duplicate it means all columns belong to those rows contain duplicate values.

Step 2 : Now go to Data -> Remove Duplicates

Step 3: Since here we are deleting duplicate rows, we need to select all columns. Our table contain headers that’s why ‘My data has headers’ has been selected. In this case the row containing column headers will not be considered for duplicate check.

If in your table there is no header you don’t need to select the same. In that case the very first row of the table will be also considered to check for duplicate.

Step 4: Here it will be displayed how many duplicate found has been found and removed. Click OK.

Step 5 : See below, 3 duplicate rows have been removed.

In case you need to remove the duplicate values with respect to only a subset of all columns in the table then there is a little change in the process as follows.

Like in our example the Column A has duplicate values.

A3 & A6 : triangle

A2 & A8 : round

A4 & A9 : square

A5, A7 & A10 : rectangle

Now go to Data -> Remove Duplicates and select only the column ‘Shapes’ (which is column A). Click OK.

See below, 5 duplicate values have been removed. Click OK.

Here is the table containing no duplicate values in column A.

One thought on “How to remove duplicate values in Excel

Leave a Reply

Your email address will not be published. Required fields are marked *