How to Remove Duplicates in Google Sheets

Paste =UNIQUE(A2:A) into an empty column and Google Sheets returns your list with every duplicate stripped out — the original stays untouched.

=UNIQUE(A2:A)

Returns the values in A2:A with duplicates removed, in their first-seen order. Put it in a blank column so the source list is preserved.

How it works

UNIQUE scans the range top to bottom and outputs each value the first time it appears, skipping any later repeats. Because it is a formula, the result is a live copy — edit the source and the deduped list updates itself. If you would rather delete duplicates in place instead of getting a copy, use the menu route: Data → Data cleanup → Remove duplicates. The formula is better when you want the original list intact for auditing.

Variations

Dedupe several columns together (whole rows)

=UNIQUE(A2:C)

Treats each row as a unit; a row is a duplicate only if every column matches.

Remove duplicates and sort the result

=SORT(UNIQUE(A2:A))

Wrap UNIQUE in SORT to get a clean alphabetical or numeric list.

Count how many unique values there are

=COUNTA(UNIQUE(A2:A))

Counts the deduped list without needing a helper column.

Examples

Scenario	Formula
Unique customer emails from a signup log	`=UNIQUE(B2:B)`
Distinct product names, sorted	`=SORT(UNIQUE(A2:A))`

FAQ

Does UNIQUE change my original data?

No. It writes a deduplicated copy into the cell where you type it. Your source range is never modified.

How do I remove duplicates permanently?

Use the menu: Data → Data cleanup → Remove duplicates. That deletes the extra rows in place. Use =UNIQUE when you want to keep the original.

Why does UNIQUE return blank rows?

Empty cells in the range count as a value. Trim the range to your data, e.g. =UNIQUE(A2:A100), or wrap with FILTER to drop blanks: =UNIQUE(FILTER(A2:A,A2:A<>"")).

Related formulas

→ See all 34 Google Sheets formulas in one cheat sheet