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)
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.
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:
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.
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.
Download Practice Workbook
Download this practice spreadsheet to exercise while you are reading this article.
Related Articles
- How to Find Duplicate Values Using VLOOKUP in Excel
- How to Find Duplicates without Deleting in Excel
- How to Find Similar Text in Two Columns in Excel
<< Go Back to Find Duplicates in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
