In Microsoft Excel, there are numerous methods available to find duplicates in a single column. We can insert a function or use a combined formula to identify the duplicates or matches in our Excel spreadsheet. In this article, you’ll get to learn all possible simple methods to find duplicate values in one column with examples and proper illustrations.

**Table of Contents**hide

**Download Practice Workbook**

You can download the Excel workbook that we’ve used to prepare this article.

**8 Suitable Ways to Find Duplicates in One Column with Excel Formula**

**1. Use COUNTIF Function to Find Duplicates Along with 1st Occurrence**

In the following table, there are several names lying under the **Name **header in **Column B**. And under the **Duplicate **header in **Column C**, we’ll **use the COUNTIF function** to find out if any name in the left column has duplicates or not. The formula will return **TRUE **for duplicate names and **FALSE **for unique ones.

In the first output** Cell C5**, the formula with the **COUNTIF **function will be:

`=COUNTIF($B$5:$B$14,B5)>1`

After pressing **Enter **and auto-filling the rest of the cells in **Column C**, we’ll get the following results.

In this formula, the **COUNTIF **function returns the number of counts for each name and by using the logical operator, we have looked for the counts that are greater than **1**. Thus we can identify the duplicates by looking for the boolean value **‘TRUE’ **only.

**Read More:** **Formula to Find Duplicates in Excel (6 Easy Ways)**

**2. Create a Formula with IF and COUNTIF Functions to Find Duplicates in One Column**

We can also combine **IF** and **COUNTIF **functions to return the outputs with customized texts. Under the **Output **header, the formula will return **‘Duplicate’ **for the duplicate names present in **Column B**. And if a text is unique in the **Name **column then the formula will return a blank for the corresponding text value.

So, the required formula combining the **IF **and **COUNTIF **functions in **Cell C5** should be:

`=IF(COUNTIF($B$5:$B$14,B5)>1,"Duplicate","")`

Now press **Enter**, use **Fill Handle** to autofill the other cells under the **Output **header and you’ll find the following outputs at once.

In this formula, the **IF **function looks for the counts of more than 1 and if found, it returns the specified text **‘Duplicate’**, otherwise a blank cell.

**Read More: How to Find Duplicate Values in Excel Using Formula (9 Methods)**

**3. Find Duplicates in One Column without 1st Occurrence in Excel**

In this section, we’ll insert a formula that will display **‘Duplicate’ **from the 2nd occurrence of a similar text. For example, if a name in **Column B** is present thrice, then the formula will return the defined text **‘Duplicate’ **for the 2nd and 3rd occurrences only.

The required formula for the first output **Cell C5** will be:

` =IF(COUNTIF($B$5:$B5,B5)>1,"Duplicate","")`

After pressing **Enter **and dragging down to the last cell in the **Output **column, we’ll get the following return values.

For the first output in Cell **C5**, we’ve defined the cell range with **$B$5:$B5** only, and therefore, the formula will look for the first cell only to find a duplicate value. While dragging down the **Fill Handle** to find the next outputs, the number of cells in the** defined range** for the **COUNTIF **function increases by **1 **for each successive cell. So, the 1st occurrence of any text in the **Name **column is not counted for more than **1** here.

**Read More: How to Find Duplicates in a Column Using Excel VBA (5 Ways)**

**4. Excel Formula to Find Case-Sensitive Duplicates in a Single Column**

Now we’ll apply another combined formula to find the case-sensitive duplicates. We’re going to combine the **IF, SUM, and EXACT functions** here. The **EXACT **function checks whether two strings are exactly the same. **The SUM function** simply sums the numerical values.

In the first output **Cell C5**, the combined formula with the referred functions will be:

`=IF(SUM((--EXACT($B$5:$B$14,B5)))<=1,"","Duplicate")`

Now press **Enter **and autofill the entire column to find all return values.

If you notice, you’ll find that the name **‘Fred’ **is present thrice in the **Name **column. But the formula has returned **‘Duplicate’ **for the first two occurrences only and the third one is ignored since its first letter case does not match with the other ones.

**🔎**** How Does the Formula Work?**

- The
**EXACT**function here looks for the case-sensitive and exact matches for the first text in the Name column and thereby returns the following output:

**{TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}**

- With the use of
**double-unary (–)**, the return values convert into numbers,**‘1’**for**TRUE**and**‘0’**for**FALSE**. So, the return values here will be:

**{1;0;0;0;0;0;0;0;0;0}**

- The
**SUM**function then sums up all the numeric values found in the preceding step. **=SUM((–EXACT($B$5:$B$14, B5)))<=1:**This part of the formula checks if the sum or the return value found in the last step is equal to or less than**1**.- Finally, the
**IF**function looks for the sum less than or equal to**1**and returns a blank cell, and if not found then it returns the defined text**‘Duplicate’**. - The formula becomes applicable for the rest of the cells in the
**Output**column after we fill down the first cell.

**Read More:** **Find Duplicates in Two Columns in Excel (6 Suitable Approaches)**

**Similar Readings**

**Excel Find Duplicates in Column and Delete Row (4 Quick Ways)****Excel Find Duplicate Rows Based on Multiple Columns****How to Find Duplicate Rows in Excel (5 Quick Ways)****Excel Top 10 List with Duplicates (2 Ways)****How to Compare Rows in Excel for Duplicates**

**5. Find Sequence of Duplicates with Excel Formula**

In this section, we’ll look for the duplicates with the **COUNTIF **function, and then it’ll return the sequential number of each occurrence of the similar text in the Output column.

The required formula in the first output **Cell C5** is:

`=COUNTIF($B$5:$B5,B5)`

Press **Enter**, fill down the entire column and you’ll get the following return values. In the picture below, the name **Fred **is present thrice and in the highlighted output cells, you’re seeing the sequential numbers for all duplicates including the 1st occurrence as well.

In this formula, we have used the **relative cell reference** for the selected cell range. So, when the formula starts to go down in the **Output **column, it intakes the increased number of cells up to the corresponding output cell only. Thus the duplicate text in all next cells gets ignored until the formula reaches down that corresponding duplicate value.

**6. Filter and Delete Duplicates in One Column in Excel**

After applying the formula to find the duplicate values, we can filter and delete them right away. In the picture below, the output data have been found by following the previous method. Now we’ll go through the next steps to meet our objectives in this section.

**📌**** Step 1:**

➤ Select the entire table first including its headers.

➤ Under the **Home **tab, select the option **Filter **from the **Sort & Filter** drop-down in the **Editing **group of commands.

So, we’ve just activated the **Filter **buttons for our headers in the following table.

**📌**** Step 2:**

➤ Click on the **Output **drop-down and unmark the first option showing the numeric value **‘1’**.

➤ Now press **OK**.

We’re now seeing the duplicate texts without their 1st occurrences in the filtered table below.

**📌**** Step 3:**

➤ Select the cells containing the names and corresponding outputs.

➤ And delete them all.

**📌**** Step 4:**

➤ Open the **Output **filter again.

➤ Mark the option showing **‘1’ **only.

➤ Press **Enter **and you’re done.

Like in the following screenshot, now you’ll get all the unique text data or names only.

**Read More:** **How to Find & Remove Duplicate Rows in Excel**

**7. Excel Formula to Find Duplicates in One Column Based on Condition**

We can also insert a condition and find out the duplicates accordingly for the values lying in a column. In the picture below, we have an additional column now that represents the departments for all employees in an organization.

Now we may have two employees with a similar name but in different departments. And one of their names with the corresponding department might be present with duplications in the following dataset. By combining the **IF **and **COUNTIFS** functions, we’ll now look for those duplicate rows.

The required formula in the first output **Cell D5** will be:

`=IF(COUNTIFS($B$5:$B$14,B5,$C$5:$C$14,C5)>1,"Duplicate","")`

After pressing **Enter **and filling down the entire **Output **column, we’ll get the return values as shown below.

In **Column B**, we have the name **‘Fred’ **thrice but only of them is with the **Sales **department **(Row 7)**. The other two occurrences of the similar name are with the **Marketing **department **(Row 9 and Row 13)**. So, one of them is a duplication. Thus, we can input multiple conditions in the **COUNTIFS **function to find the required output.

**Read More:** **Find Matches or Duplicate Values in Excel**

**8. Find and Highlight Duplicates with Conditional Formatting Rule**

In the last section, we’ll find out the duplicates and highlight them by using the **Conditional Formatting** command. We’ll assign a formula with the **COUNTIF **function in the **Rule Box** and then define the format of the cells where our formula will return the boolean value **‘TRUE’ **only.

Let’s follow the steps below now to meet the criteria:

**📌**** Step 1:**

➤ Select all the names under the **Name **header in **Column B**.

➤ Under the **Home **ribbon, choose the option **New Rule** from the **Conditional Formatting** drop-down.

A dialog box named **New Formatting Rule** will appear.

**📌**** Step 2:**

➤ Select the **Rule Type** as **‘Use a formula to determine which cells to format’**.

➤ In the **Rule Description** box, embed the following formula:

`=COUNTIF($B$5:$B$14,B5)>1`

➤ Press **Format**.

**📌**** Step 3:**

➤ In the **Format Cells** window, switch to the **Fill **tab and select a background color for the duplicate cells.

➤ Press **OK**.

**📌**** Step 4:**

➤ You’ll find a preview of the format of the cell as shown in the picture below.

➤ Press **OK **for the last time and we’re done.

Finally, you’ll notice the highlighted duplicates including the 1st occurrences as displayed in the following screenshot.

**Read More: ****How to Find, Highlight & Remove Duplicates in Excel**

**Concluding Words**

I hope all of these simple methods mentioned above will now help you to apply them in your Excel spreadsheets when you have to identify duplicates with formulas. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.