The UNIQUE Function? Basics
UNIQUE is the Google Sheets function that pulls the distinct values out of a messy list — one formula, and duplicates vanish into a clean spill of results. It updates live when the source changes, which beats manual deduping every time. This guide shows the syntax and the combinations that make it genuinely useful.
TL;DR / Quick Fix
Quick fix: UNIQUE in Google Sheets pulls the distinct values out of a range — =UNIQUE(A2:A100) spills the deduped list automatically. It updates live when the source changes. Wrap it in SORT for alphabetical order. It only removes exact duplicates; near-matches with extra spaces still count as different.
Follow these steps
- Learn the basic syntax. Type =UNIQUE(A2:A100) in an empty cell. Sheets spills the distinct values downward automatically — one formula, no dragging, no manual deduping.
- Understand the spill. The results occupy as many cells as needed below the formula. Keep that space clear — anything in the way triggers a #REF error until you move it.
- Wrap it in SORT. =SORT(UNIQUE(A2:A100)) gives you the distinct values in alphabetical order. This combo covers most 'clean list' needs in a single cell.
- Go multi-column. UNIQUE works on ranges with several columns — it dedupes whole rows. =UNIQUE(A2:B100) returns each distinct name-and-city pair once.
- Pair with COUNTUNIQUE. COUNTUNIQUE(A2:A100) tells you how many distinct values exist without listing them. Great for 'how many different customers' questions in dashboards.
- Clean near-duplicates first. UNIQUE only removes exact matches — 'Acme' and 'Acme ' (trailing space) count as different. Run TRIM on the source range first for honest deduping.
What NOT to do
- Do not put anything below the formula. The spill needs empty cells — data in the way breaks the formula with a #REF error.
- Do not expect fuzzy matching. UNIQUE is exact-match only; typos and extra spaces survive. Clean the data first, then dedupe.
- Do not copy-paste the spill as values blindly. Pasted values freeze — they stop updating when the source changes. Keep the formula live unless you need a snapshot.
- Do not use UNIQUE to delete source data. It creates a clean view elsewhere; the originals stay. To actually remove duplicates from the source, use Data, Remove duplicates instead.
Frequently asked questions
What is the difference between UNIQUE and Remove duplicates?
UNIQUE is a live formula that shows distinct values elsewhere; Remove duplicates permanently deletes them from the source. Formula for views, menu command for cleanup.
Why do I get a #REF error?
Something blocks the spill range below the formula. Clear the cells underneath and the results appear.
Can UNIQUE ignore blanks?
Wrap it: =UNIQUE(FILTER(A2:A100, A2:A100<>"")) drops the blanks before deduping. FILTER plus UNIQUE is a power combo.