Conditional Logic

Excel formula for Flag duplicate values

Mark the second and later occurrences of any repeated value.

Copy this formula

=IF(COUNTIF($A$2:A2, A2)>1, "Duplicate", "")

How the formula works

The range $A$2:A2 grows as you fill down, so COUNTIF counts how many times the value has appeared up to this row. A count above 1 means it's a repeat — the first occurrence stays unmarked.

Examples

=IF(COUNTIF($A$2:A2, A2)>1, "Dup", "Unique")→Dup

Labels repeats vs first-seen values.

FAQ

How do I flag ALL copies, including the first?

Use the whole range: =IF(COUNTIF($A$2:$A$100, A2)>1, "Dup", "").

Related formulas

Count how many times a value repeats Get a list of values without duplicates

Functions used

COUNTIF IF

Need a formula for your own data?

Describe what you want in plain language and let ExcelGPT write, explain, and verify the formula — free to start, no plugin.

Try ExcelGPT free