# 5+ Formulas to Find Duplicates in One Column in Excel

Finding duplicates is a very common task in an Excel spreadsheet. This article presents **more than 5 formulas** to find duplicates in one column in Excel.

## Watch How to Find Duplicates in One Column in Excel Using Formula

## 1st Formula to Find Duplicates in One Column in Excel

**What this formula actually does?**

*The formula returns TRUE if the content is a duplicate. Otherwise, it returns FALSE.*

The **COUNTIF **function is a combined version of the **COUNT **function and the **IF **function. We can use the **COUNTIF **function to easily find the duplicates in one column in Excel.

**Syntax**

*COUNTIF(range, criteria)*

**Step_1: **Insert the formula in cell **C2**.

=COUNTIF($B$2:$B$15,B2)>1

**Step_2:** Press **ENTER**.

This will return the formula result in cell **C2**.

**Step_3:** Select cells **C2 **to **C15**.

**Step_4:** With all the cells being selected, press **CTRL + D.**

This will copy the above formula in all the selected cells with changing cell references.

**Final Result >**

**Formula Explanation**

**$B$2:$B$15**is the cell range where we want to find the duplicates.**B2**is the criteria here which is the first cell in the range**B2:B15.**

The **COUNTIF **function compares the content of cell **B2 **with that of the range **B2:B15**. If any content appears more than once, it treats that content as a duplicate value.

As the range is fixed here, we must lock the range. We can use absolute cell references by imposing **dollar signs ($)** before the cell references to lock the range.

**🔗 How to Find Duplicates in Two Columns in Excel (7 Methods)**

## 2nd Formula to Find Duplicates in One Column in Excel

**What this formula actually does?**

*The formula returns Duplicate when it finds duplicate content. Otherwise, it returns Unique.*

The previous formula returns **TRUE **for the duplicate values and **FALSE **for the unique values.

But if you want to display other messages in exchange for **TRUE **or **FALSE**, you need to merge one extra function with the previous formula.

**Syntax**

*IF(COUNTIF(range, criteria),value_if_true,value_if_false)*

**Step_1: **Type the formula in cell **C2**.

=IF(COUNTIF($B$2:$B$15,B2)>1,"Duplicate","Unique")

**Step_2:** Press **ENTER **to insert the formula.

**Step_3:** Select the range **C2 **to **C15**.

**Step_4: **Select the **Home **tab > **Editing **group > **Fill **drop-down menu > **Down**.

This will copy down the above formula to the selected range with changing cell references.

**Final Result >**

**Formula Explanation**

**$B$2:$B$15**is the cell range where we want to find the duplicates. As the range is fixed here, we must lock the range.**B2**is the criteria here which is the first cell in the range**B2:B15.**

The **COUNTIF **function compares the content of cell **B2 **with that of the range **B2:B15**. If any content appears more than once, the **COUNTIF **function returns **TRUE**. Otherwise, it returns **FALSE**.

When the **COUNTIF **function returns **TRUE**, the **IF **function returns Duplicate. But when the **COUNTIF function** returns **FALSE**, the **IF **function returns **Unique**. This the how the formula works.

**🔗 How to Compare Rows in Excel for Duplicates (7 Ways)**

**🔗 How to Find Similar Text in Two Columns in Excel (5 Ways)**

## 3rd Formula to Find Duplicates in One Column in Excel

**What this formula actually does?**

*This formula skips the first appearance of a duplicate value. It returns Duplicate when it finds duplicate contents. Otherwise, it returns Unique.*

The above two formulas identify the first appearance of a duplicate value as a **Duplicate**.

But if you want to skip the first appearance of the duplicate value and only identity the second and onward appearances, then you can use this formula.

**Syntax**

*IF(COUNTIF(range, criteria),value_if_true,value_if_false)*

**Usage Guide**

Everything is the same as the previous_method. Just use the following formula instead of the formula used in the previous method.

=IF(COUNTIF($B$2:$B2,B2)>1,"Duplicate","Unique")

**Final Result >**

**Formula Explanation**

Just look at the **range ($B$2:$B2) **inside the **COUNTIF **function. The first cell of this range is locked up. Only the second cell of the range can be changed to** $B2, $B3, $B4,** etc.

For the first appearance of a value, **COUNTIF($B$2:$B2,B2)>1** becomes false. Thus, it returns **FALSE**. As a result, the **IF **function returns **Unique**.

For the second and onward appearance of a value, **COUNTIF($B$2:$B2,B2)>1** becomes **TRUE**. Thus, it returns **TRUE**. As a result, the **IF **function returns **Duplicate**.

**🔗 5 Ways to Find Matching Values in Two Worksheets in Excel**

**🔗 Find, Highlight, and Remove Duplicates in Excel [Step-by-Step]**

## 4th Formula to Find Duplicates in Excel

**What this formula actually does?**

*This formula returns the sequence of a duplicate value’s appearances in terms of 1,2,3, etc.*

**Syntax**

*COUNTIF(range, criteria)*

**Formula**

=COUNTIF($B$2:$B$15,B2)

To read the usage guide and explanation of this formula, **click_here.**

**🔗 4+ Methods to Filter Duplicate Values in Excel**

## 5th Formula to Find Duplicates in Excel

**What this formula actually does?**

*This is a case-sensitive formula. It returns “Duplicate” when a certain value has more than one exact appearance. Otherwise, it returns “Unique”.*

**Syntax**

*IF(SUM(--EXACT(range, criteria))),value_if_true,value_if_false)*

**Formula**

=IF(SUM((--EXACT($B$2:$B$15,B2)))<=1,"Unique","Duplicate")

**Read **the usage guide of the formula in Excel.

**Formula Explanation**

The **EXACT **function looks for exact matches between the content of cell **B2 **with that of the range **$B$2:$B$15**. The **SUM **function calculates the summation of the number of times the **EXACT function** finds a match. If the summation becomes less than or equal to **1**, the **IF **function returns **“Unique”**. Otherwise, it returns **“Duplicate”.**

**🔗 How to Remove Duplicates in Excel [13 + Different Methods]**

## Find Duplicates in One Column Based on Condition (Another Column)

**What this formula actually does?**

*This formula finds the duplicates in one column based on criteria set in another column.*

In this particular example, the formula discussed below can find the duplicates in the column, **Ice Cream Flavors.** In the column, **Company**, two ice cream manufacturer company name is enlisted. The formula will find duplicates of ice cream flavor names under the same company.

**Syntax**

*IF(COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…),value_if_true,value_if_false)*

**Formula**

**=IF(COUNTIFS($A$2:$A$15,A2,$B$2:$B$15,B2)>1,"","Unique")**

**Formula Explanation**

**$A$2:$A$15:**This is*The cell range of the column,***Company**.**A2:**This is the*first cell of***criteria_range1.****$B$2:$B$15:**This is*The cell range of the column,***Ice Cream Flavors.****B2:**This is the*first cell of***criteria_range2.**

**🔗 4+ Ways to Find Duplicates in a Column and Delete Rows in Excel**

**🔗 Vlookup for Duplicate Values and Return the Matches in Excel [8 Cases]**

## Conclusion

I’ve discussed more than 5 formulas to find the duplicates in one column in Excel. I expect you’ve found this article useful. You can read more articles relating to Excel from the **Blog** page of our website.