UNIQUE returns the distinct rows from a range and leaves the original data untouched. This page covers the basics, unique rows across several columns, the optional arguments, sorting, ignoring blanks, counting unique values, and the case-sensitivity detail that surprises people.
Row 1 holds headers. A2:B7 (customer, region):
Acme, East
Globex, West
Acme, East
Initech, East
Globex, North
acme, East
=UNIQUE(A2:A7)
This returns Acme, Globex, Initech, acme, in order of first appearance. It spills down from the cell you typed it in, so the cells below must be empty. The result updates when the source changes.
Give UNIQUE a range with several columns and it treats each whole row as the unit:
=UNIQUE(A2:B7)
This returns Acme East, Globex West, Initech East, Globex North, acme East. Acme East appears once because both columns match in rows 2 and 4. Globex appears twice because the regions differ. To get distinct values from each column separately, use one UNIQUE per column.
The full form is UNIQUE(range, [by_column], [exactly_once]). by_column set to TRUE compares columns instead of rows, which is rarely needed. exactly_once set to TRUE returns only values that appear a single time in the data, dropping anything repeated:
=UNIQUE(A2:A7,FALSE,TRUE)
On the sample, this returns Initech and acme. Initech appears once, and lower-case "acme" also counts as appearing once, because UNIQUE treats it as different from "Acme". Use it to find one-off entries, and lower-case the range first if capitals should not matter. On two columns, =UNIQUE(A2:B7,FALSE,TRUE) returns the rows that occur exactly once: Globex West, Initech East, Globex North and acme East.
UNIQUE keeps order of first appearance. Wrap it in SORT for alphabetical:
=SORT(UNIQUE(A2:A7))
This returns Acme, acme, Globex, Initech. SORT ignores case, so the two spellings sit together. Add FALSE as the second SORT argument for descending order: =SORT(UNIQUE(A2:A7),1,FALSE).
If your range runs past the data, such as A2:A100, UNIQUE includes one extra blank row for the empty cells. Filter the blanks out first:
=UNIQUE(FILTER(A2:A100,A2:A100<>""))
This returns the four values only. Sorted too:
=SORT(UNIQUE(FILTER(A2:A100,A2:A100<>"")))
=COUNTUNIQUE(A2:A7)
This returns 4. With blanks in the range, COUNTUNIQUE ignores empty cells, so it does not need FILTER. Careful with two columns: =COUNTUNIQUE(A2:B7) returns 7, because it counts distinct cell values (Acme, East, Globex, West, Initech, North, acme), not rows. To count unique rows, use =ROWS(UNIQUE(A2:B7)), which returns 5.
UNIQUE and COUNTUNIQUE are case-sensitive, so "acme" and "Acme" count as different values, which is why the sample returns four names rather than three. To treat them as one, lower-case the range first:
=UNIQUE(ARRAYFORMULA(LOWER(A2:A7)))
This returns acme, globex, initech. More on case in lookups and matching is at https://www.snakbacks.com/case-insensitive-match-google-sheets
Hidden differences matter too. "Acme" and "Acme " with a trailing space are distinct values, so clean first with ARRAYFORMULA(TRIM(...)). Our guide to hidden spaces is at https://www.snakbacks.com/trim-not-working-google-sheets
To list each name with how many times it appears, put the unique list in D2 and a count beside it in E2:
=UNIQUE(A2:A7)
=ARRAYFORMULA(COUNTIF(A2:A7,D2:D5))
This gives Acme 3, Globex 2, Initech 1, acme 3. COUNTIF ignores case, so "acme" is counted along with "Acme", which makes the counts higher than the list suggests. To avoid the mismatch, use the lower-cased unique list from the previous section so each name appears once with the right total. More ways to count repeats are at https://www.snakbacks.com/count-duplicates-google-sheets
UNIQUE only matches identical values. "Acme Pty Ltd" and "ACME Pty. Ltd" are different to it, as are typos and reworded names. Cleaning with TRIM and LOWER helps with spacing and case. Punctuation and spelling variants need similarity matching, not formulas.
Disclosure: Snakbacks, the publisher of this site, sells a paid script pack and makes two free add-ons for Google Sheets.
Everything above needs nothing but Google Sheets. For near-duplicates that UNIQUE keeps separate, the Data Cleanup Kit for Google Sheets (US$15, no add-on install): https://snakback.gumroad.com/l/data-cleanup-kit groups them with FUZZYDUPES, and adds ready-made tabs for merging and summing, keeping the latest row, comparing lists, splitting names and cleaning text.
Our two free add-ons, Fuzzy Match and Duplicate Remover, are coming soon to the Google Workspace Marketplace.
Snakbacks publishes the Duplicate Remover and Fuzzy Match add-ons.