Formula to Find Duplicates in Excel (6 Easy Ways)

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,

=COUNTIF(B:B,B4)>1

Using COUNTIF to Find Duplicates in Excel

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.

Note:

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:

=COUNTIF($B$4:$B$10,B4)>1

Using COUNTIF to Find Duplicates in Excel from a range


1.2 Count the Number of Duplicates Using COUNTIF

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$4:$B$10, $B4)

Find duplicates in Excel Using COUNTIF counting repeated times.


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.

=IF(COUNTIF($B$4:$B$10,$B4)>1,"Duplicate","Unique")

Find duplicates in Excel Using If function with COUNTIF.

In case, you want an Excel formula to find duplicates only, replace “Unique” with blank (” “) like this:

=IF(COUNTIF($B$4:$B$10,$B4)>1,"Duplicate","")

The formula will show “Duplicates” for duplicate records, and a blank cell for unique records.Using If with COUNTIF find only duplicate items.


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:

=IF(COUNTIF($B$4:$B4,$B4)>1,"Duplicate","")

As you can see in the following picture, this formula does not identify the first occurrence of “Apples” as duplicate:Using If with COUNTIF find only duplicate items in one column.


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

=IF(COUNTIFS($B$4:$B$10,$B4,$C$4:$C$10,$C4)>1,"Duplicate","Unique ")

Using If with COUNTIF find only duplicate items in two columns.


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>

Find duplicates from multiple columns

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.


Conclusion

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.

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo