# How to get unique values in Google Sheets

Drop the blanks from the open range, then remove repeats and sort: `=SORT(UNIQUE(FILTER(B2:B,B2:B<>"")))`.

**Google Sheets:** `=SORT(UNIQUE(FILTER(B2:B,B2:B<>"")))`

**Excel:** Excel has no open ranges like B2:B; give a range with room to grow: `=SORT(UNIQUE(FILTER(B2:B500,B2:B500<>"")))` in Excel 2021 and later.

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Date | Customer |  | Customers |
| 2026-06-01 | Northwind |  |  |
| 2026-06-02 | Contoso |  |  |
| 2026-06-03 | Northwind |  |  |
| 2026-06-04 | Fabrikam |  |  |
| 2026-06-05 | Litware |  |  |
| 2026-06-08 | Contoso |  |  |
| 2026-06-09 | Adatum |  |  |
| 2026-06-10 | Fabrikam |  |  |
| 2026-06-11 | Northwind |  |  |

Formula in D2: `=SORT(UNIQUE(FILTER(B2:B,B2:B<>"")))`

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

## How it works

`B2:B` covers the whole column below the header, so new orders are picked up. That range also contains every empty row, and UNIQUE would keep one empty value from them, so FILTER removes the blanks first. UNIQUE then lists each customer once and SORT orders them A to Z.

## Other ways

- `=SORT(UNIQUE(B2:B))`: Without FILTER, the empty rows add a blank at the end of the list. (An empty cell after Northwind.)
- `=UNIQUE(B2:B10)`: A closed range, unsorted: customers in the order they first appear. (Northwind, Contoso, Fabrikam, Litware, Adatum.)

## Common mistakes

- UNIQUE is case-sensitive in Google Sheets: "contoso" and "Contoso" are listed separately.
- Trailing spaces make near-identical names appear twice. Clean them with TRIM inside: `UNIQUE(ARRAYFORMULA(TRIM(…)))`.

## Questions

**How do I count the unique values?**

Use `=COUNTUNIQUE(B2:B)`, which ignores blanks.

**How do I get unique rows across two columns?**

Give UNIQUE both columns: `=UNIQUE(A2:B10)`.

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

Source: https://spreadsheetpractice.com/formulas/unique-values-from-a-whole-column