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.

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.

  • 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


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.

Find How Many Duplicates Are There Using COUNTIF Function

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.

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

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:

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


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.

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

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

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


Download Practice Workbook

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


Related Articles


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

Get FREE Advanced Excel Exercises with Solutions!
Alok Paul
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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo