# How to get a list of unique values in Excel

Use =UNIQUE(range) to list each value once, and wrap it in SORT for alphabetical order: =SORT(UNIQUE(range)).

**Excel:** `=SORT(UNIQUE(B2:B10))` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Order | Customer |  | Customers |
| A-1001 | Northwind |  |  |
| A-1002 | Contoso |  |  |
| A-1003 | Northwind |  |  |
| A-1004 | Fabrikam |  |  |
| A-1005 | Litware |  |  |
| A-1006 | Contoso |  |  |
| A-1007 | Adatum |  |  |
| A-1008 | Northwind |  |  |
| A-1009 | Fabrikam |  |  |

Formula in D2: `=SORT(UNIQUE(B2:B10))`

Result (spills 5 × 1): Adatum; Contoso; Fabrikam; Litware; Northwind

## 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.

## Other ways

- `=UNIQUE(B2:B10)`: Without SORT, the customers appear in the order they first show up. (Order of first appearance.)
- `=UNIQUE(B2:B10,FALSE,TRUE)`: 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.

## 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.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-29.

Source: https://spreadsheetpractice.com/formulas/list-unique-values