How to get a list of unique values in Excel
Formula (Excel and Google Sheets)
=SORT(UNIQUE(B2:B10))Use =UNIQUE(range) to list each value once, and wrap it in SORT for alphabetical order: =SORT(UNIQUE(range)).
- Result on the example
- a 5-row list that spills from D2 in
D2 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in D2. Change any input and the result updates, or edit the formula to experiment.
How it works
UNIQUE removes the repeats and SORT puts the result in order. Because it's a formula, the list grows when a new customer appears in the data, unlike Remove Duplicates, which is a one-time edit.
=SORT(UNIQUE(B2:B10))Result{5 rows × 1 col} "Adatum", "Contoso", "Fabrikam", "Litware", …
B2:B109 rows × 1 col: "Northwind", "Contoso", "Northwind", "Fabrikam", …A range: 9 cells in one column.
UNIQUE(B2:B10){5 rows × 1 col} "Northwind", "Contoso", "Fabrikam", "Litware", …UNIQUE: Distinct rows of a range. Spills.- array
B2:B109 rows × 1 col: "Northwind", "Contoso", "Northwind", "Fabrikam", …
- array
SORT(UNIQUE(B2:B10)){5 rows × 1 col} "Adatum", "Contoso", "Fabrikam", "Litware", …SORT: Sorts rows by one or more columns. is_ascending is TRUE or FALSE.- range
UNIQUE(B2:B10){5 rows × 1 col} "Northwind", "Contoso", "Fabrikam", "Litware", …
- range
In Google Sheets
The same formula works in Google Sheets: =SORT(UNIQUE(B2:B10)). We ran it in Google Sheets mode on the same data and it returns the same results, spilling 5 rows.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =UNIQUE(B2:B10) | spills 5 × 1Different result | Without SORT, the customers appear in the order they first show up. Order of first appearance. |
| =UNIQUE(B2:B10,FALSE,TRUE)Excel only | spills 2 × 1Different result | The third argument TRUE lists only values that appear exactly once. Only Litware and Adatum appear once. |
Common mistakes
- Blank cells in the range add a 0 to the list. Filter them out:
=UNIQUE(FILTER(B2:B100,B2:B100<>"")). - UNIQUE ignores case, so "contoso" and "Contoso" are one value.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Get a list of unique values
D2, list each customer from B2:B10 once, in alphabetical order.Questions
How do I count the unique values?
Wrap the list in COUNTA: =COUNTA(UNIQUE(B2:B10)).
Can I get unique rows across several columns?
Yes, pass the whole table: =UNIQUE(A2:B10) returns each distinct combination.
Checked by running on 2026-10-29: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.