Here, 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.
Here we include a dataset that indicated Student Name and their favorite Fruits.
Download Practice Workbook
Download this practice sheet to exercise while you are reading this article.
1. Formula to Find Duplicates in Excel Including 1st Occurrences
1.1 Using 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’s a formula to find duplicates in Excel including first occurrences,
As you can see in the picture above, the formula returns TRUE for duplicate values and FALSE for unique values. In this formula, we selected the whole B column.
You can find duplicates in a fixed range of cells rather than in an entire column. For this, you need to lock that range with the $ sign. For example, to search for duplicates in cells B4:B10, use this formula:
1.3 Using IF Function with COUNTIF in Excel
For a duplicate, you can use the IF Function with COUNTIF and get a duplicate or unique number.
In case, you want an Excel formula to find duplicates only, replace “Unique” with blank (” “) like this:
The formula will show “Duplicates” for duplicate records, and a blank cell for unique records.
2. Formula to Find Duplicates in Excel Without 1st Occurrences
Here we will detect duplicates without first occurrence. Here we use two formulas one IF with COUNTIF and the other is IF with COUNTIFS.
2.1 Using the If Function in One Column Excel
If you want to filter or remove duplicates, the above-mentioned formula will not work. Because it marks all identical records as duplicates. And if you want to keep the unique values in your list, then you cannot delete all duplicate records, you need to only delete the 2nd and all subsequent instances.
So, we will modify our Excel duplicate formula by using absolute and relative cell references:
As you can see in the following picture, this formula does not identify the first occurrence of “Apples” as duplicate:
2.2 Using If Function with COUNTIFS to find duplicates in Two Columns
Above we showed how to find duplicate values in one column, now we will see here how to find duplicates in two columns in excel.
In this example, we have taken a table where the Student name is in column A and Fruits is 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
3. Using If Function with SUMPRODUCT to Find Duplicates in Multiple Rows
We can find duplicates in multiple rows. Here we will use the SUMPRODUCT function with the IF function.
Here is the formula:
<span style="font-size: 14pt;">=IF(SUMPRODUCT(($B$4:$B$10=B4)*1,($C$4:$C$10=C4)*1,($D$4:$D$10=D4)*1)>1,"Duplicates","Unique")</span>
If you breakdown the formula to
<span style="font-size: 14pt;">=SUMPRODUCT(($B$4:$B$10=B4)*1,($C$4:$C$10=C4)*1,($D$4:$D$10=D4)*1)</span>
You will get how many times that row is repeated.
In the formula, $B$4:$B$10,$C$4:$C$10,$D$4:$D$ 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 value from the data range. And B4, C4, D4 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.
Here, we described five formulas to find duplicates in Excel. You will get the formula to find duplicates from rows and columns separately. A formula is also given to find how many times it is repeated.