SheetsdrillUNIQUE
UNIQUE(range, [by_column], [exactly_once]) returns the distinct rows of a range, keeping the first occurrence of each and dropping repeats. With one column it gives a deduplicated list; with several columns it removes rows that are duplicates across all of them.
Syntax
=UNIQUE(range, [by_column], [exactly_once])| Part | What it is |
|---|---|
range | The cells to deduplicate, for example A2:A100 or A2:C100. |
by_column | Optional. TRUE compares columns instead of rows. Default FALSE. |
exactly_once | Optional. TRUE returns only values that appear a single time. Default FALSE. |
Examples
=UNIQUE(A2:A100)Result: Each customer name once, in order of first appearance
=SORT(UNIQUE(A2:A100))Result: The same list sorted A to Z
=COUNTA(UNIQUE(A2:A100))Result: How many distinct customers there are
Blank cells count as a value; use COUNTA(UNIQUE(FILTER(A2:A100, A2:A100<>""))) to exclude them.
Common mistakes
- Trailing spaces or different capitalization make values look duplicated but stay separate; UNIQUE is case sensitive, so wrap the range in ARRAYFORMULA(TRIM(LOWER(...))) or clean the data first.
- An empty cell in the range becomes a blank entry in the result; filter blanks out first.
- #REF! because the list would spill over filled cells; leave space below the formula.
Practice UNIQUE
These drills check your answer on live data, free, no account needed.
- Distinct customersHow many different customers placed an order? Use UNIQUE and count its result. Answer in F1.
Questions
How do I count unique values in Google Sheets?
Use =COUNTUNIQUE(A2:A100), or =COUNTA(UNIQUE(A2:A100)) if you also want the list.
How do I get unique values with their counts?
Put =UNIQUE(A2:A) in E2 and =ARRAYFORMULA(COUNTIF(A2:A, E2:E)) in F2, or use =QUERY(A2:A, "select A, count(A) group by A").
Related
- SORT and SORTNHow SORT and SORTN in Google Sheets sort data by one or more columns with a formula and return the top or bottom N rows, with examples and common errors.
- FILTERHow the FILTER function in Google Sheets returns only rows that match one or more conditions, with examples for AND, OR, dates and text, plus common errors.
- COUNTIFHow COUNTIF in Google Sheets counts cells equal to a value, above a number, containing text or not blank, with examples, wildcards and the errors that give 0.
- QUERYHow the Google Sheets QUERY function works: the SQL-like syntax, select and where, group by with sum and count, order by, limit, and the errors people hit.