Spreadsheet Practice

How to get unique values in Google Sheets

Google Sheets formula

=SORT(UNIQUE(FILTER(B2:B,B2:B<>"")))

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

Result on the example
a 5-row list that spills from D2 in D2
Excel
See below

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

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.

=SORT(UNIQUE(FILTER(B2:B,B2:B<>"")))

Result{5 rows × 1 col} "Adatum", "Contoso", "Fabrikam", "Litware", …

  1. B2:B49 rows × 1 col: "Northwind", "Contoso", "Northwind", "Fabrikam", …

    A range: 49 cells in one column.

  2. B2:B<>""{49 rows × 1 col} TRUE, TRUE, TRUE, TRUE, …

    The <> operator tests whether they differ.

  3. FILTER(B2:B,B2:B<>""){9 rows × 1 col} "Northwind", "Contoso", "Northwind", "Fabrikam", …

    FILTER: Rows of a range that meet every condition. #N/A when none match.

    • rangeB2:B49 rows × 1 col: "Northwind", "Contoso", "Northwind", "Fabrikam", …
    • condition1B2:B<>""{49 rows × 1 col} TRUE, TRUE, TRUE, TRUE, …
  4. UNIQUE(FILTER(B2:B,B2:B<>"")){5 rows × 1 col} "Northwind", "Contoso", "Fabrikam", "Litware", …

    UNIQUE: Distinct rows of a range. Spills.

    • arrayFILTER(B2:B,B2:B<>""){9 rows × 1 col} "Northwind", "Contoso", "Northwind", "Fabrikam", …
  5. SORT(UNIQUE(FILTER(B2:B,B2:B<>""))){5 rows × 1 col} "Adatum", "Contoso", "Fabrikam", "Litware", …

    SORT: Sorts rows by one or more columns. is_ascending is TRUE or FALSE.

    • rangeUNIQUE(FILTER(B2:B,B2:B<>"")){5 rows × 1 col} "Northwind", "Contoso", "Fabrikam", "Litware", …

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

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=SORT(UNIQUE(B2:B))spills 6 × 1Different resultWithout FILTER, the empty rows add a blank at the end of the list. An empty cell after Northwind.
=UNIQUE(B2:B10)spills 5 × 1Different resultA 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(…))).

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 unique values

In D2, list each customer from column B once, A to Z, using an open range so new orders are included, with no blank at the end.

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

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine.