The first cell of this range is locked up. Just look at the range ($B$2:$B2) inside the COUNTIF function. Just use the following formula instead of the formula used in the previous method. The above two formulas identify the first appearance of a duplicate value as Duplicate.īut 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.Įverything is the same as the previous_method. It returns Duplicate when it finds duplicate contents. This formula skips the first appearance of a duplicate value. 3rd Formula to Find Duplicates in One Column in Excel But when the COUNTIF function returns FALSE, the IF function returns Unique. When the COUNTIF function returns TRUE, the IF function returns Duplicate. If any content appears more than once, the COUNTIF function returns TRUE. The COUNTIF function compares the content of cell B2 with that of the range B2:B15. As the range is fixed here, we must lock the range. $B$2:$B$15 is the cell range where we want to find the duplicates.This will copy down the above formula to the selected range with changing cell references. Step_4: Select the Home tab > Editing group > Fill drop-down menu > Down. Step_2: Press ENTER to insert the formula.
The previous formula returns TRUE for the duplicate values and FALSE for the unique values.īut if you want to display other messages in exchange for TRUE or FALSE, you need to merge one extra function with the previous formula.įormula Syntax IF(COUNTIF(range, criteria),value_if_true,value_if_false) The formula returns Duplicate when it finds duplicate content.
2nd Formula to Find Duplicates in One Column in Excel We can use absolute cell references by imposing dollar signs ($) before the cell references to lock the range. If any content appears more than once, it treats that content as a duplicate value.Īs 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.This will copy the above formula in all the selected cells with changing cell references. Step_4: With all the cells being selected, press CTRL + D. This will return the formula result in cell C2. We can use the COUNTIF function to easily find the duplicates in one column in Excel. The COUNTIF function is a combined version of the COUNT function and the IF function.
The formula returns TRUE if the content is a duplicate. Find Duplicates in One Column Based on Condition (Another Column)ġst Formula to Find Duplicates in One Column in Excel.5th Formula to Find Duplicates in Excel.4th Formula to Find Duplicates in Excel.3rd Formula to Find Duplicates in One Column in Excel.2nd Formula to Find Duplicates in One Column in Excel.1st Formula to Find Duplicates in One Column in Excel.