Start free
Dynamic arrays

UNIQUE function

UNIQUE returns each different value in a range once, as a list that spills down the sheet and updates when the data changes.

Available in Excel for Microsoft 365, Excel 2021 and later, and Excel for the web
On this page

UNIQUE syntax

=UNIQUE(array, [by_col], [exactly_once])
ArgumentRequiredWhat it means
arrayYesThe range to take the values from.
by_colNoTRUE to compare columns instead of rows. Leave it out for a normal list.
exactly_onceNoTRUE to return only values that appear exactly once.

Examples

The regions in a sales list

Five rows, three different regions. The list spills from E2.

E2=UNIQUE(B2:B6)
RowABCDE
1RepRegionRegions
2AnaEastEast
3BenWestWest
4CaraEastNorth
5DanNorth
6EliEast
=UNIQUE(B2:B6) → East

Count the distinct values

COUNTA counts the items UNIQUE returns: 3 regions.

E2=COUNTA(UNIQUE(B2:B6))
RowABCDE
1RepRegionRegions
2AnaEast3
3BenWest
4CaraEast
5DanNorth
6EliEast
=COUNTA(UNIQUE(B2:B6)) → 3

Common mistakes

  1. Including the header. UNIQUE(B1:B6) returns "Region" as one of the values. Start the range below the header.
  2. A blocked spill range. If the cells below aren't empty, you get #SPILL!.

FAQ

How do I sort the unique list?

Wrap it in SORT: =SORT(UNIQUE(B2:B6)).

Is UNIQUE case-sensitive?

No. "east" and "East" count as the same value.

Guides that use UNIQUE

Worked problems with practice sheets.

Interview prepPractice sheet

FILTER, UNIQUE and SORT: an Excel interview question

List each customer once, A to Z, for the region someone picks, in one formula that updates itself. The dynamic-array answer interviewers look for, and a sheet that grades yours.

4 min read · Updated

Know UNIQUE? Prove it on a real sheet.

Practise UNIQUE and the functions around it in a live grid that grades your result and explains your mistakes.

Practise UNIQUE freeSee the lessons →
Runs in your browser · Graded on your result · No install