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 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.
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 of how many times they occur.
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.
Note: You could also build more advanced formula with wrapped by IF function, eg. =IF(COUNTIF($A$1:A1,A1)>1,"",A1), where "A1" is the cell reference for the first cell of the column you want to remove duplicates from. The COUNTIF function returns the number of times a specific value appears in a range of cells. The IF function is used to conditionally display the values in the new column based on the COUNTIF result. If the count of a specific value is greater than 1, meaning it's a duplicate, the formula will return an empty string, causing it to be omitted from the unique values.
Further reading: How to find duplicates in two columns Countif non empty cells Countif partial match