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 1****st**** 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`

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`

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

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.

**2. Formula to Find Duplicates in Excel Without 1****st**** 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:

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

**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.

**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.