Dynamic ArrayLevel: ExpertAvailable in: Excel 365 / 2021+

In this article

1. Syntax
2. Examples
3. Example 1
4. Example 2
5. Example 3
6. Power combo
7. Related functions

UNIQUE returns the distinct values of a range, updating automatically as data changes.

Syntax

=UNIQUE(array, [by_col], [exactly_once])

Argument	What it means
array	Range to de-duplicate.
by_col	TRUE to compare columns instead of rows.
exactly_once	TRUE to return only values that appear once.

Examples

Example 1

=SORT(UNIQUE(B2:B500))

Sorted region list.

Example 2

=COUNTA(UNIQUE(C2:C500))

Number of distinct customers.

Example 3

=UNIQUE(A2:A500, , TRUE)

IDs that appear only once.

Power combo

=LET(r, UNIQUE(B2:B500), HSTACK(r, SUMIFS(E2:E500, B2:B500, r)))

A mini pivot: each region with its total, in one formula.

Related functions

FILTER · SORT · COUNTA

📚 Part of the free Excel course: Beginner → Expert · Try it in the Formula Lab or ask the AI Helper.

Keep reading

Excel Functions A–ZCONCAT Function in Excel: Join Text From Cells and RangesExcel Functions A–ZWEEKDAY Function in Excel: Day of the Week as a NumberExcel Functions A–ZSUBTOTAL Function in Excel: Totals That Respect Filters