Find & Remove Duplicates in Google Sheets
Flag duplicates with COUNTIF, pull a clean list with UNIQUE, or highlight repeats with conditional formatting.
=COUNTIF(A$2:A2, A2)>1
Returns TRUE on the second and later times a value appears. Use it in a helper column or as a conditional-formatting rule.
How it works
There is no single "remove duplicates" formula — you pick the tool for the job. UNIQUE returns a de-duplicated list. COUNTIF flags which rows repeat. For highlighting, use Format > Conditional formatting > Custom formula with the COUNTIF rule. To delete them in place, use Data > Data cleanup > Remove duplicates.
Variations
Get a de-duplicated list
=UNIQUE(A2:A100)
Returns each distinct value once, in first-seen order.
Highlight duplicates (conditional formatting rule)
=COUNTIF(A:A, A1)>1
Apply to range A:A under Custom formula; every value that appears more than once is highlighted.
Count how many times a value repeats
=COUNTIF(A:A, A2)
Shows the total occurrences of the value in A2.
Unique across two columns
=UNIQUE(A2:B100)
Treats each row as a combination and returns distinct rows.
Examples
| Scenario | Formula |
|---|---|
| Flag first duplicate onward | =COUNTIF(A$2:A2, A2)>1 |
| List of duplicated values only | =FILTER(A2:A, COUNTIF(A2:A, A2:A)>1) |
| Count of distinct values | =COUNTA(UNIQUE(A2:A100)) |
FAQ
Is there a formula to remove duplicates in Google Sheets?
UNIQUE returns a clean list without changing the original data. To delete duplicates in place, use the menu: Data > Data cleanup > Remove duplicates.
How do I highlight duplicates?
Select the range, open Format > Conditional formatting, choose Custom formula, and enter =COUNTIF(A:A, A1)>1.
How do I count unique values?
Wrap UNIQUE in COUNTA: =COUNTA(UNIQUE(A2:A100)), or use =COUNTUNIQUE(A2:A100).