Spreadsheet Practice

How to flag duplicates in a column in Google Sheets

Google Sheets formula

=ARRAYFORMULA(IF(A2:A11="","",IF(COUNTIF(A2:A11,A2:A11)>1,"Duplicate","")))

Count each value in the column with COUNTIF inside ARRAYFORMULA: =ARRAYFORMULA(IF(A2:A11="","",IF(COUNTIF(A2:A11,A2:A11)>1,"Duplicate",""))).

Result on the example
a 10-row list that spills from B2 in B2
Excel
See below

See it work

The formula is already in B2. Change any input and the result updates, or edit the formula to experiment.

How it works

With the whole column as its criteria, COUNTIF counts every email at once: 2 for each copy of ana@ and raj@, 1 for the others. ARRAYFORMULA lets that happen in one formula, and the label goes on every copy, the first one included. The blank test keeps row 10 empty.

=ARRAYFORMULA(IF(A2:A11="","",IF(COUNTIF(A2:A11,A2:A11)>1,"Duplicate","")))

Result{10 rows × 1 col} "Duplicate", "Duplicate", "", "Duplicate", …

  1. A2:A1110 rows × 1 col: "ana@ruiz.example", "raj@patel.example", "lee@chen.example", "ana@ruiz.example", …

    A range: 10 cells in one column.

  2. A2:A11=""{10 rows × 1 col} FALSE, FALSE, FALSE, FALSE, …

    The = operator tests whether the two sides are equal.

  3. COUNTIF(A2:A11,A2:A11){10 rows × 1 col} 2, 2, 1, 2, …

    COUNTIF: Counts cells that meet one condition.

    • rangeA2:A1110 rows × 1 col: "ana@ruiz.example", "raj@patel.example", "lee@chen.example", "ana@ruiz.example", …
    • criteriaA2:A1110 rows × 1 col: "ana@ruiz.example", "raj@patel.example", "lee@chen.example", "ana@ruiz.example", …
  4. COUNTIF(A2:A11,A2:A11)>1{10 rows × 1 col} TRUE, TRUE, FALSE, TRUE, …

    The > operator tests greater than.

  5. IF(COUNTIF(A2:A11,A2:A11)>1,"Duplicate",""){10 rows × 1 col} "Duplicate", "Duplicate", "", "Duplicate", …

    IF: Returns one value if a test is true, another if false.

    • logical_testCOUNTIF(A2:A11,A2:A11)>1{10 rows × 1 col} TRUE, TRUE, FALSE, TRUE, …
    • value_if_true"Duplicate"
    • value_if_false""
  6. IF(A2:A11="","",IF(COUNTIF(A2:A11,A2:A11)>1,"Duplicate","")){10 rows × 1 col} "Duplicate", "Duplicate", "", "Duplicate", …

    IF: Returns one value if a test is true, another if false.

    • logical_testA2:A11=""{10 rows × 1 col} FALSE, FALSE, FALSE, FALSE, …
    • value_if_true""
    • value_if_falseIF(COUNTIF(A2:A11,A2:A11)>1,"Duplicate",""){10 rows × 1 col} "Duplicate", "Duplicate", "", "Duplicate", …
  7. ARRAYFORMULA(IF(A2:A11="","",IF(COUNTIF(A2:A11,A2:A11)>1,"Duplicate",""))){10 rows × 1 col} "Duplicate", "Duplicate", "", "Duplicate", …

    ARRAYFORMULA: Runs a formula over whole ranges and returns all the results.

    • array_formulaIF(A2:A11="","",IF(COUNTIF(A2:A11,A2:A11)>1,"Duplicate","")){10 rows × 1 col} "Duplicate", "Duplicate", "", "Duplicate", …

In Excel

In Excel 2021 and later the same formula without ARRAYFORMULA spills: =IF(A2:A11="","",IF(COUNTIF(A2:A11,A2:A11)>1,"Duplicate","")).

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=ARRAYFORMULA(IF(A2:A11="","",IF(COUNTIFS(A2:A11,A2:A11,ROW(A2:A11),"<="&ROW(A2:A11))>1,"Repeat","")))spills 10 × 1Different resultFlags only the second and later copies, so you can delete those rows and keep the first. Only rows 5 and 8 say Repeat.
=IF(A2:A11="","",IF(COUNTIF(A2:A11,A2:A11)>1,"Duplicate",""))Excel onlyspills 10 × 1The Excel version, which spills on its own.

Common mistakes

  • COUNTIF ignores case, so "Ana@Ruiz.example" counts as a duplicate of "ana@ruiz.example". That's usually right for emails.
  • A trailing space makes a copy look different. Wrap the column in TRIM inside the formula if the data was pasted.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Flag duplicates in a column

In B2, write one formula that shows "Duplicate" next to every email in A2:A11 that appears more than once, and nothing next to the rest.

Questions

How do I highlight duplicates instead?

Use conditional formatting with the custom formula =COUNTIF($A$2:$A$11,A2)>1.

How do I remove the duplicates?

List each email once in another column with =UNIQUE(A2:A11), or use Data > Data cleanup > Remove duplicates.

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine.