Formula to Find Duplicates in Excel (6 Suitable Examples)

Get FREE Advanced Excel Exercises with Solutions!

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

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)

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)

Find How Many Duplicates Are There Using COUNTIF Function

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

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

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:

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

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

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

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

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.


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.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Alok Paul
Alok Paul

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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo