Excel how to remove both items if a duplicate


Excel has an in-built feature to remove duplicates from a list but what if you want to delete both items if they are duplicated or more than two?

Remove Duplicates

Remove Duplicates is tucked away on the Data tab, Data Tools | Remove Duplicates.  It will ensure that an Excel list/table has only unique values for the column selected.

That’s OK for most situations.  In this case, it’ll remove the extra rows for Happy and Jumpy – leaving just one row for each.

Remove both duplicates

Sometimes you need to remove BOTH entries if there’s any duplication.  For the above example that means removing both rows for Happy and Jumpy.

The solution is to count the number of entries using Countif()

1
=COUNTIF(<column range),<cell to test>)
1
=COUNTIF($A$2:$A$26,A2)

Where the entries are in column A down to row 26. A2 is the current cell.

In an Excel table the formula can be easier to read:

1
=COUNTIF([Dwarves],[@Dwarves])

The list does not have to be in alphabetical order or have the duplicates in neighboring rows.

A little surprisingly the CountIf test is NOT case-sensitive. All three ‘Gabby’ rows are counted together despite the capitalization.

Now you can filter by the count column to show only unique (count = 1) values.

Having a Count column can be really useful for other reasons. It helps detect unexpected duplications in what should be a unique list.