Excel COUNTIF Remove Duplicates: Data Deduplication

How To Use COUNTIF To Remove Duplicates

Sometimes you may need to find duplicates without removing them.

Countiif Excel function will help you with that.

We have such data in column A.

In column B I will create Countif formula:

=COUNTIF($A:$A,A2)

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

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

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.

Here's how to use filters in Excel.

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

That’s how you spotted duplicates with countif formula. Duplicated data is not removed. Next you can work on this duplicates eg. cut/paste them to the other sheet or clean data.

Template

Further reading: 
How to find duplicates in two columns 
Countif non empty cells 
Countif partial match
Basic concepts 
Getting started with Excel
Cell References