Skip to content

Flag duplicate values

Mark every value that appears more than once in a column.

The formula

Argument separator
Excel and Google Sheets
=IF(COUNTIF($A$2:$A$100, A2)>1, "Duplicate", "")

How it works

  1. COUNTIF($A$2:$A$100, A2)

    How many times this row's value appears in the whole column. The $ signs keep the range fixed.

  2. >1

    More than once means it is a duplicate.

Example

A: EmailB: Result
a@x.comDuplicate
b@x.com
a@x.comDuplicate

Tips

  • To flag only the second and later copies, use COUNTIF($A$2:A2, A2)>1.