5+ Formulas to Find Duplicates in One Column in Excel

ByRhidi Barma Updated on Advanced 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.

Find Duplicates in One Column in Excel.xlsx

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

Find Duplicates in One Column in Excel with COUNTIF Formula

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)

Formula 

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

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.

Use COUNTIF function to find duplicates in one column, go through these steps below:

  1. Select a blank cell.
  2. Insert the formula: =COUNTIF($B$2:$B$15,B2)>1                       
  3. Press ENTER.
    This will return the formula result in cell C2.
  4. Select cells C2 to C15.
  5. With all the cells being selected, press CTRL + D.

This will copy the above formula in all the selected cells with changing cell references. 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.

Usage of COUNTIF function to Find Duplicates in One Column in Excel

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.

Find Duplicates in One Column in Excel Combining IF & COUNTIF Functions

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. However, 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)

Formula

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

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.

Combine IF and COUNTIF functions to find duplicates in one column. To do that, follow these steps:

  1. Click an empty cell.
  2. Type the formula: =IF(COUNTIF($B$2:$B$15,B2)>1,”Duplicate”,”Unique”)
  3. Press ENTER to insert the formula.
  4. Select the range C2 to C15.
  5. 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. 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.

Usage of IF and COUNTIF function to Find Duplicates in One Column in Excel

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.

Find Duplicates in Excel with IF Function Returning Duplicate/Unique

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. Nevertheless, 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)

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

Formula

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

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.

Formula to Find Duplicates in One Column in Excel

Find Duplicates in Excel with COUNTIF Returning Counts of Duplicates

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.

Displaying result to show the counts of duplicates with COUNTIF function

Find Duplicates in Excel Using IF, SUM & EXACT Functions

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

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

Read the usage guide of the formula in Excel.

Highlighted the Unique value with IF, SUM and EXACT function in Excel

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.

Find Duplicates in One Column Based on Condition

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.

Frequently Asked Questions

Can Excel count duplicates in a column?

Yes, Excel can count duplicates in a column using the COUNTIF function:

  1. Select the cell where you want the count result.
  2. Enter the formula: =COUNTIF(A:A, A1) where A:A is the column range and A1 is the first cell.
  3. Press ENTER.

This formula counts the occurrences of each value in the specified column, helping you identify and quantify duplicates.

What is the shortcut for duplicate formula in Excel?

The shortcut for duplicating a formula in Excel is to use the Fill Handle:

  1. Select the cell with the formula.
  2. Hover over the bottom-right corner of the selected cell until the cursor becomes a small square.
  3. Double-click or drag the fill handle in the direction you want to copy the formula.

This quickly duplicates the formula in the adjacent cells, saving time and effort.

How do I apply a formula to an entire column?

To apply a formula to an entire column in Excel:

  1. Select the cell in the column where you want the formula.
  2. Enter your formula and press ENTER.
  3. Hover over the bottom-right corner of the selected cell until the cursor becomes a small square.
  4. Double-click or drag the Fill Handle down the column.

Excel will automatically apply the formula to the entire column, adjusting cell references as needed.

Rate this post

Data Analyst

Howdy! I'm Rhidi Barma. I'm a professional Excel user. I've completed my graduation from Jahangirnagar University, Bangladesh. I've been working with Microsoft Excel since 2015. I love writing blogs on MS Excel tips & tricks, data analysis, business intelligence, capital market, etc. I love to spend my leisure time reading books and watching movies. To know more about me, you can follow me on social media platforms. Thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *