**Method 1 – Using the COUNTIF Function to Find Duplicates in One Column Along with the First Occurrence**

We have a list of names in column B. The formula to find duplicates will return **TRUE **for duplicate names and **FALSE **for unique ones in column C.

- Insert the following formula in the first result cell (C5), then press
**Enter**and use**AutoFill**to get the results throughout the column.

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

The **COUNTIF **function returns the number of counts for each name (second argument). The logical operator checks for counts that are greater than **1**.

**Read More: **Find Duplicates in Two Columns in Excel

**Method 2 – Creating an Excel Formula with IF and COUNTIF Functions to Find Duplicates in One Column**

Under the **Output **header, the formula will return **Duplicate **for the duplicate names present in **Column B**.

- Insert the following formula in the first result cell (C5), then press
**Enter**and use**AutoFill**to get the results throughout the column.

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

The **IF **function wraps the formula from Method 1 to return the specified text **Duplicate **or a blank value.

**Read More: **How to Find Similar Text in Two Columns in Excel

**Method 3 – Finding Duplicates in One Column Without the First Occurrence in Excel**

The formula will display **Duplicate **only if a value has already been repeated previously (i.e., the first occurrence will get a blank result).

- Insert the following formula in the first result cell (C5), then press
**Enter**and use**AutoFill**to get the results throughout the column.

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

For the first output in Cell **C5**, we’ve defined the cell range with **$B$5:$B5 **so the second row reference moves with the formula. For each subsequent output, the number of cells in the** defined range** for the **COUNTIF **function increases by **1**. This ensures that the first value will only be counted once.

**Method 4 – Using Excel Formula to Find Case-Sensitive Duplicates in a Single Column**

- Insert the following formula in the first result cell (C5), then press
**Enter**and use**AutoFill**to get the results throughout the column.

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

** 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 TRUE and FALSE convert into numbers 1 and 0. 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**.- 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**.

**Method 5 – Finding How Many Times a Value Has Been Repeated in a Column**

- Insert the following formula in the first result cell (C5), then press
**Enter**and use**AutoFill**to get the results throughout the column.

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

This formula is similar to the one used to check duplicates without the first occurrence, with the second reference moving with the formula. The COUNTIF function naturally returns a number, so we don’t need any more checks.

**Method 6 – Filtering and Deleting Duplicates in One Column in Excel**

We’ve used Method 5 to get the serial number of each value’s occurrence.

**Steps:**

- Select the entire table, including its headers.
- Under the
**Home**tab, select the option**Filter**from the**Sort & Filter**drop-down in the**Editing**group of commands.

- This activates the
**Filter**buttons for the headers.

- Click on the
**Output**drop-down and unmark**1**. - Click OK.

- You’ll get a list of duplicated values.

- Select these cells and delete them.

- Open the
**Output**filter again. - Mark the option
**1**only. - Click on OK.

- You’ll get all the unique text data or names only. The cells with blank values have been hidden with the filter. You can remove those rows afterward.

**Read More:** How to Find Duplicates without Deleting in Excel

**Method 7 – Creating an Excel Formula to Find Duplicates in One Column Based on a Condition**

We have an additional column that represents the departments for all employees in an organization. We’ll check if we have duplicated combinations of name and department.

- Insert the following formula in the first result cell (D5), then press
**Enter**and use**AutoFill**to get the results throughout the column.

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

- Here’s the result.

The **COUNTIFS **function implicitly uses the AND argument between all conditions and their ranges.

**Method 8 – Finding and Highlighting Duplicates with Conditional Formatting**

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

- Select the
**Rule Type**as**Use a formula to determine which cells to format**. - In the
**Rule Description**box, insert the following formula:

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

- Press
**Format**.

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

- You’ll find a preview of the format of the cell as shown in the picture below. Click
**OK.**

- The formula highlights all duplicates including their first occurrences. You can use a different formula to highlight only the occurrences after the first (see Method 3).

**Read More: **How to Find Duplicate Values Using VLOOKUP in Excel

**Download the Practice Workbook**

## Related Articles

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