Show Duplicate if the value in A2 appears more than once in column A

公式

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

说明

COUNTIF on a locked range counts every occurrence of this row’s value. Greater than 1 means the item is a duplicate. Use the same expression in conditional formatting.

步骤

  1. Lock the scan range $A$2:$A$500.
  2. COUNTIF > 1 → Duplicate.
  3. Copy down, or use the formula in a formatting rule.

变体

Flag only the 2nd+ occurrence

Expanding range. The first copy stays blank; later copies are flagged.

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

Conditional formatting formula

Apply to A:A. Excel highlights every duplicated cell.

=COUNTIF($A:$A, A1)>1

List duplicates only (365)

Spill the values that appear more than once.

=UNIQUE(FILTER(A2:A500, COUNTIF(A2:A500, A2:A500)>1))