How to use UNIQUE in Excel
Lists each value once. The result spills down.
Syntax
arrayA range[by_col] optional[exactly_once] optionalExample
Here’s UNIQUE on a small table of orders. The highlighted cells are the ones the formula reads.
=UNIQUE(B2:B9)Edit this formula live Change the ranges and watch the answer update. Free, no sign-up.
Common mistakes with UNIQUE
It spills
UNIQUE returns as many rows as there are distinct values. Leave the cells below empty, or you’ll get #SPILL!.
Needs Excel 2021 or Microsoft 365
Older versions don’t have dynamic array functions.
What the error messages mean
Type these slips into Gridwise and this is the feedback you get:
Leaving off the closing bracket
=UNIQUE(B2:B9
Parentheses don’t match. You have 1 opening and 0 closing parentheses. Every ( needs a ).
Questions
What does the UNIQUE function do in Excel?
Lists each value once. The result spills down. The syntax is UNIQUE(array, [by_col], [exactly_once]).
Why is my UNIQUE formula giving the wrong answer?
It spills: UNIQUE returns as many rows as there are distinct values. Leave the cells below empty, or you’ll get #SPILL!. Needs Excel 2021 or Microsoft 365: Older versions don’t have dynamic array functions.
Does UNIQUE work in Google Sheets?
Most Excel functions, including the core ones on this page, work the same way in Google Sheets. A few newer functions arrived in Sheets later or behave slightly differently, so test it on your own data.