Excel Formula to Find Duplicates in One Column

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.


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.

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

Use COUNTIF Function to Find Duplicates Along with 1st Occurrence

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.

Use COUNTIF Function to Find Duplicates Along with 1st Occurrence

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.


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.

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

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.


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.

Find Duplicates in One Column without 1st Occurrence in Excel

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.


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.

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

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

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.

Find Sequence of Duplicates with Excel Formula

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.

Filter and Delete Duplicates in One Column in Excel

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

Filter and Delete Duplicates in One Column in Excel

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

Filter and Delete Duplicates in One Column in Excel

📌 Step 2:

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

➤ Now press OK.

Filter and Delete Duplicates in One Column in Excel

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

Filter and Delete Duplicates in One Column in Excel

📌 Step 3:

➤ Select the cells containing the names and corresponding outputs.

➤ And delete them all.

Filter and Delete Duplicates in One Column in Excel

📌 Step 4:

➤ Open the Output filter again.

➤ Mark the option showing ‘1’ only.

➤ Press Enter and you’re done.

Filter and Delete Duplicates in One Column in Excel

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

Filter and Delete Duplicates in One Column 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.

Excel Formula to Find Duplicates in One Column Based on Condition

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","")

Excel Formula to Find Duplicates in One Column Based on Condition

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

Excel Formula to Find Duplicates in One Column Based on Condition

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.


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.

Find and Highlight Duplicates with Conditional Formatting Rule

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

Find and Highlight Duplicates with Conditional Formatting Rule

📌 Step 3:

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

➤ Press OK.

Find and Highlight Duplicates with Conditional Formatting Rule

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

Find and Highlight Duplicates with Conditional Formatting Rule

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

Find and Highlight Duplicates with Conditional Formatting Rule


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.


You May Also Like to Explore

Find Duplicates in Two Columns in Excel (6 Suitable Approaches)

Find Matches or Duplicate Values in Excel (8 Ways)

How to Highlight Duplicate Rows in Excel (3 Ways)

How to Find Matching Values in Two Worksheets in Excel (4 Methods)

Excel Top 10 List with Duplicates

Excel Find Similar Text in Two Columns

How to Compare Rows in Excel for Duplicates

Finding out the number of duplicate rows using COUNTIF formula

How to Find & Remove Duplicate Rows in Excel

 

 

Nehad Ulfat

Hello, Welcome to my profile. I'm a Technical Content Creator as well as a Naval Architect & Marine Engineer. I have preferences to do analytical reasoning & writing articles on various statistical data. Here, you'll find my published articles on Microsoft Excel & other topics related to my interests!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo