## 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

`=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**