FormulaCamp
Completed
← Google Sheets functions
Google Sheets function

UNIQUE in Google Sheets

UNIQUE spills its result natively in Google Sheets, so one formula in the top cell fills the cells below it.

Syntax (same in Google Sheets)

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

If something already sits in the way, the formula shows #REF! with a message that the array result was not expanded because it would overwrite data.

For a fuller explanation of the arguments and common errors, see the UNIQUE Excel guide.

Worked examples in Google Sheets

Distinct regions

ABC
1RegionRepAmount
2EastAna1200
3WestBen850
4EastCara430
5NorthDan970
6WestEli1500
7EastAna610

Formula: E2: =UNIQUE(A2:A7)

Result: Spills down: East, West, North

Keeps the first occurrence of each region, in order of appearance.

Count of distinct regions

ABC
1RegionRepAmount
2EastAna1200
3WestBen850
4EastCara430
5NorthDan970
6WestEli1500
7EastAna610

Formula: E2: =COUNTA(UNIQUE(A2:A7))

Result: 3

COUNTA counts the items in the unique list.

Values that appear once

ABC
1RegionRepAmount
2EastAna1200
3WestBen850
4EastCara430
5NorthDan970
6WestEli1500
7EastAna610

Formula: E2: =UNIQUE(B2:B7,,TRUE)

Result: Spills down: Ben, Cara, Dan, Eli

Setting exactly_once to TRUE drops Ana, who appears twice.

Try it

A tiny spreadsheet with example data. Edit the formula or any number and see the result.

Formula in F2: =UNIQUE(A2:A7)

Example spreadsheet for UNIQUE
ABCDEF
1RegionProductUnitsPriceResult
2EastPen1202.5East
3WestPen802.5West
4EastBook4512North
5NorthBook6012
6WestLamp3025
7EastLamp1525
Result in F2: East
Practice this in a lesson: UNIQUE and SORT

Frequently asked questions

What does "Array result was not expanded" mean for UNIQUE in Google Sheets?
The cells where the list should spill are not empty. Clear them or move the formula; the list then appears by itself.

Google Sheets is used here only as a descriptive product name. FormulaCamp is not affiliated with Google.