What it does
UNIQUE returns distinct values from a list or range.
Syntax or pattern
=UNIQUE(array, [by_col], [exactly_once])✅
5 practical examples
1
List unique products
Return one copy of each product name.
=UNIQUE(Sales[Product])Useful for dropdown sources.
2
Unique regions sorted
Return a sorted region list.
=SORT(UNIQUE(Sales[Region]))Sorting makes lists easier to scan.
3
Unique combinations
Return unique product-region pairs.
=UNIQUE(CHOOSE({1,2},Sales[Product],Sales[Region]))Dynamic arrays can return unique rows.
4
Values appearing once
Return values that appear exactly once.
=UNIQUE(A2:A100,,TRUE)The third argument controls exactly-once behavior.
5
Unique after filtering
List unique products in one region.
=UNIQUE(FILTER(Sales[Product],Sales[Region]=H2))Combines filtering and distinct values.
Common mistakes to avoid
- Make sure spill ranges have empty space below or beside them.
- Use Excel Tables where possible for expanding source data.
- Older Excel versions may not support all dynamic array functions.
Related Excel examples
FAQ
What is a spill formula?
A spill formula returns multiple cells from one formula entered in a single cell.