In this article, we are going to describe some ways to find duplicates in Excel using formula. Some of them may be familiar to you and some will be new. We will try to describe it in the simplest way so that you can catch it easily.
1. Using Logical Formula with COUNTIF to Find Duplicates in One Column in Excel
Let’s have a table of items like fruits. Here, the item name is in the column, and you want to find a duplicate.
Here is the first formula to find duplicates in Excel. It will include first occurrences too.
=COUNTIF(B:B,B5)>1
As you can see in the picture above, you get TRUE when there is a duplicate and FALSE when there are no duplicates. In this formula, we selected the whole B column.
Note:
To search for duplicates in a fixed range of cells, i.e., in cells B5:B11, use the following formula:
=COUNTIF($B$5:$B$11, $B5)
Read More: Excel Formula to Find Duplicates in One Column
2. Finding How Many Duplicates Are There Using Excel COUNTIF Function
If you want to know the total number of duplicate values, then you can use the COUNTIF function. For counting duplicate values, you need to use the given COUNTIF formula:
=COUNTIF($B$5:$B$11, $B5)
Read More: How to Find Duplicates in Excel Workbook
3. Using IF-COUNTIF Formula to Mark Duplicate Values with Specified Text
For a duplicate, you can use the IF Function with COUNTIF and get a duplicate or unique number.
=IF(COUNTIF($B$5:$B$11,$B5)>1,"Duplicate","Unique")
Read More: Find and Highlight Duplicates in Excel
4. Finding Duplicates Without First Occurrence- Change Range in IF-COUNTIF Formula Slightly
If you want to remove or filter duplicates, the above-mentioned formula will not work. Because it designates all duplicate records as such. And if you wish to maintain the unique values in your list, you can merely delete the second and any succeeding instances of the duplicate records rather than all of them.
So, we will modify the formula by using relative and absolute references:
=IF(COUNTIF($B$5:$B5,$B5)>1,"Duplicate","")
As you can see in the following picture, this formula doesn’t mark the 1st coccurrence of “Apples” as identical:
Read More: How to Find Duplicates in Two Different Excel Workbooks
5. Combining COUNTIFS and IF Functions to Find Duplicates in Two Columns
Above we have shown the way to find duplicate values in only one column, now we will show the way to search for duplicates in (two or more) columns in excel.
In this example, we have taken a table where the Student name is in column A and the Fruits are in column B. Now we want to find duplicate values having the same name and fruits.
The formula to find duplicate values in two columns is
=IF(COUNTIFS($B$5:$B$11,$B5,$C$5:$C$11,$C5)>1,"Duplicate","Unique ")
Read More: Find Duplicates in Two Columns in Excel
6. Finding Duplicate Rows Using Excel SUMPRODUCT Function
We can find duplicates in multiple rows. Here we will use the SUMPRODUCT function with the IF function.
Here is the formula:
=IF(SUMPRODUCT(($B$5:$B$11=B5)*1,($C$5:$C$11=C5)*1,($D$5:$D$11=D5)*1)>1,"Duplicates","Unique")
In the formula, $B$5:$B$11,$C$5:$C$11, and $D$5:$D$11 indicate the range columns that you want to find the duplicate from. You can change the range as per your data. Here we are using absolute references to get exact values from the data range. And B5, C5, D5 indicate the first cells in each column of the data which need to be applied to this formula, you can change them as per your data.
The above formula is based on data in 3 columns, you can increase columns in your data range, and accordingly, you will add the ranges. And then find the identical rows easily.
Download Practice Workbook
Download this practice spreadsheet to exercise while you are reading this article.
Conclusion
Here, we described 6 formulas to find duplicates in Excel. I hope, you find this content useful. However, if you have any confusion or queries, leave us a comment.
