Formula to Find Duplicates in Excel (6 Suitable Examples)

Method 1 – Using Logical Formula with COUNTIF to Find Duplicates in One Column in Excel

• Consider a column of item names like fruits in column B, where we want to find duplicate values and put the result in column C.
• Insert this formula into C5:
`=COUNTIF(B:B,B5)>1`
• Drag the Fill Handle down to apply it to the entire range.

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.

• 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)`

Method 2 – Finding How Many Duplicates Are There Using Excel COUNTIF Function

• Put the following formula in C5:
`=COUNTIF(\$B\$5:\$B\$11, \$B5)`
• Drag the Fill Handle down to autocomplete the rest of the column.

Read More: How to Find Duplicates in Excel Workbook

Method 3 – Using IF-COUNTIF Formula to Mark Duplicate Values with Specified Text

• Paste the formula into C5:
`=IF(COUNTIF(\$B\$5:\$B\$11,\$B5)>1,"Duplicate","Unique")`

• Drag down the Fill Handle.

Read More: Find and Highlight Duplicates in Excel

Method 4 – Finding Duplicates Without First Occurrence- Change Range in IF-COUNTIF Formula

• Put the following into C5:
`=IF(COUNTIF(\$B\$5:\$B5,\$B5)>1,"Duplicate","")`
• Drag the Fill Handle down.

As you can see in the following picture, this formula doesn’t mark the first occurrence of “Apples” as identical:

Method 5 – Combining COUNTIFS and IF Functions to Find Duplicates in Two Columns

• Consider 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 ")`
• Paste the formula into D5 and drag the Fill Handle down to the rest of the dataset.

Read More: Find Duplicates in Two Columns in Excel

Method 6 – Finding Duplicate Rows Using Excel SUMPRODUCT Function

Consider that you need to find whether information across columns B, C, and D is duplicated.

Put the formula in column E:

`=IF(SUMPRODUCT((\$B\$5:\$B\$11=B5)*1,(\$C\$5:\$C\$11=C5)*1,(\$D\$5:\$D\$11=D5)*1)>1,"Duplicates","Unique")`

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, but you need to use absolute references to get exact values from the data range. Since B5, C5, D5 indicate the first cells in each column of the data which need to be applied to this formula, the formula is pasted into E5 and copied via Fill Handle.

The above formula is based on data in three columns. You can increase the number of columns in your data rangeby adding the respective range after.

Related Articles

<< Go Back to Find Duplicates in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Alok Paul

Alok Paul has completed his B.Sc. in Electronics and Telecommunication Engineering from East West University. He has been working on the ExcelDemy project for more than 2 years. He has written 220+ articles and replied to numerous comments. He is experienced in Microsoft Office, especially in Excel. He also led some teams on Excel and VBA content development. He has a keen interest in Advanced Excel, Data analysis, Excel Pivot Table, Charts, and Dashboard. He loves to research... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF