Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

Formula to Find Duplicates in Excel (6 Suitable Examples)

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.


Download Practice Workbook

Download this practice spreadsheet to exercise while you are reading this article.


6 Examples to Find Duplicates in Excel Using Formula

1. Using a Logical Formula with COUNTIF to Identify Duplicates in One Column

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

Using a Logical Formula with COUNTIF to Identify Duplicates in One Column

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)

2. Find 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)

Find How Many Duplicates Are There Using COUNTIF Function


3. Using an IF-COUNTIF Formula to Mark the 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")

Using an IF-COUNTIF Formula to  Mark the Duplicate Values with Specified Text


4. Find Duplicates Without the First Occurrence- Change the 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:

Searching for Duplicates Without the First Occurrence- Change the Range in IF-COUNTIF Formula Slightly


5. Find Duplicates in Two Columns Using a Combination of COUNTIFS and IF Functions

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 ")

Find Duplicates in Two Columns Using a Combination of COUNTIFS and IF Functions


6. Find 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")

Mark Duplicate Rows Using SUMPRODUCT Function

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.


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. And don’t forget to visit our blog for more articles.

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