How to use countif to remove duplicates?

In this Excel tutorial lesson, you will write the countif to remove duplicates.

How to remove duplicates with Countif?

Sometimes you may need to find duplicates without removing them.

The Countiif Excel function will help you with that.

You have such data in column A.

countif to remove duplicates source data

Countif formula to remove duplicates

In column B, I will create a countif formula: =COUNTIF($A:$A,A2)

The formula will count how many times the value occurs in the whole column.

countif to remove duplicates countif formula

Drag the formula down to check every value in the data set.

countif to remove duplicates found

Now you can see duplicated data. Every value above 1 means that there are duplicates of this value in the data set.

You can filter duplicates out.

countif to remove duplicates filter

And here are duplicated values with the number of how many times they occur.

countif to remove duplicates filtered

That’s how you spotted duplicates with the countif formula. Duplicated data is not removed. Next, you can work on these duplicates, e.g., cut and paste them to the other sheet or clean data.

See also  Counting unique values in pivot table