Home » Blog » Find duplicate rows

Find duplicate rows

Hello everyone! Did you know……Microsoft Excel can easily find duplicate rows by using the COUNTIF function.
book a session

Join up row’s cells’ content as first step to find duplicate rows

First of all, on a blank column to the right of our data, for each cell in this new column we join up all the cells on the same row to the left using the “&” symbol, e.g.

=A6&B6&C6

What a “full” duplicate row is

So any cell in this new column, in say column D, which has exactly the same value as another cell in this column is definitely a full duplicate row.

Let’s now deploy the COUNTIF function

Beside this first new column we now set up another new column, say in column E, with the COUNTIF function, i.e.

= COUNTIF(first new column range,new cell on the current row in first new column)

or as in an example:

=COUNTIF($D$6:$D$100,D6)

The non-duplicates

Those cells which result in a count of “1” indicate a non-duplicate, as shows appearing only one time in this new column.

Duplicate rows found

Any cell which counts to greater than “1” is obviously a duplicate.

Filter it

Apply a filter and you can filter out the duplicate rows by filtering in the “1”s only.

Additional Info

Bring back in a duplicate

You can filter in the duplicates (Greater Than 1) and delete any unwanted rows.

The Conditional Formatting alternative

Instead of using the COUNTIF column we could instead apply conditional formatting to the JoinedUp column.  So highlight this column and click through the following command path:

Home / Styles / Conditional Formatting / Highlight Cells Rules / Duplicate Values

Format cells that contain – select “Duplicate”

values with – select a suitable colour format

Ignoring the first duplicate

We could actually identify in our COUNTIF column the first duplicate as a non-duplicate.  This would be the adjusted formula to enter in cell E6 (and then copy down in column E):

=COUNTIF($D$6:$D6,D6)

This is probably the best approach?

Get spreadsheet

book a session

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.