Lookup & Reference Modern

UNIQUE

Returns the distinct values from a range, removing duplicates.

Available in Microsoft 365 · Excel 2024 · Excel 2021 · Excel for the web. Not in 2019/2016.

Syntax

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

What it returns

A spilled array of the distinct values.

Arguments

ArgumentRequiredDescription
array Yes The range to get distinct values from.
by_col No FALSE = compare rows (default), TRUE = compare columns.
exactly_once No TRUE = return only values that appear exactly once.

Example

Source
AB
1Region
2North
3South
4North
5East
6South
7North
8
Result
AB
1Region
2North
3South
4East
5
=UNIQUE(A2:A8)

Important to know

Spills into multiple cells.

Updates automatically as the source changes.

Often nested with SORT: =SORT(UNIQUE(...)).

365 / 2021+ only.

Related functions

FILTER SORT

Related learning

No MEE lesson covers this function yet.