UNIQUE vs Remove Duplicates in Google Sheets
Short answer: Use the UNIQUE formula when you want a live, self-updating list that leaves the original data untouched. Use the Remove Duplicates menu tool when you want to permanently clean the data in place, once.
Copy the one most people want (UNIQUE)
=UNIQUE(A2:A)
Returns every distinct value from A2 downward in a new spot, updating automatically as you add rows. The source column is never altered.
UNIQUE vs Remove Duplicates — the differences that matter
| Aspect | UNIQUE | Remove Duplicates |
|---|---|---|
| Live vs one-off | Live formula — recalculates as data changes. | One-time edit; run again after new data arrives. |
| Original data | Untouched — output goes to a new range. | Modified in place — duplicate rows are deleted. |
| Where it lives | A cell (spills a range). | Data â–¸ Data cleanup â–¸ Remove duplicates menu. |
| Reversible | Delete the formula, source is intact. | Only via Undo right after; otherwise the rows are gone. |
| Whole rows vs one column | Works on a column or multiple columns as a set. | Lets you choose which columns define a duplicate. |
Which one should you use?
Use UNIQUE when…
You need a distinct list that stays current — a dropdown source, a summary of categories, a de-duplicated feed — without changing the raw data.
Use Remove Duplicates when…
You're doing a final cleanup of a static list and genuinely want the duplicate rows deleted from the sheet for good.
Same task, both formulas
Get a list of the distinct customer names in column A.
=UNIQUE(A2:A)
Select column A â–¸ Data â–¸ Data cleanup â–¸ Remove duplicates
UNIQUE leaves column A alone and gives you a live copy elsewhere; Remove Duplicates permanently strips repeats from column A itself.
FAQ
Does UNIQUE change my original data?
No. It outputs a separate distinct list and leaves the source range exactly as it is.
Can I undo Remove Duplicates?
Only with Ctrl+Z immediately after running it. Once you make other edits, the deleted rows are unrecoverable.
Which is better for a dropdown list?
UNIQUE — because it updates automatically, your dropdown source stays current as new values appear.
→ Google Sheets formulas cheat sheet — 34 copy-paste formulas