What it does
UNIQUE returns distinct values from a list or range.
Syntax or pattern
=UNIQUE(array, [by_col], [exactly_once])5 practical examples
List unique products
Return one copy of each product name.
=UNIQUE(Sales[Product])Useful for dropdown sources.
Unique regions sorted
Return a sorted region list.
=SORT(UNIQUE(Sales[Region]))Sorting makes lists easier to scan.
Unique combinations
Return unique product-region pairs.
=UNIQUE(CHOOSE({1,2},Sales[Product],Sales[Region]))Dynamic arrays can return unique rows.
Values appearing once
Return values that appear exactly once.
=UNIQUE(A2:A100,,TRUE)The third argument controls exactly-once behavior.
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.
Here are some ideas for you
Optional resources that may help if you are learning formulas, building reports, or working in spreadsheets often.
- Excel formula booksSee ideas
Practice formulas with structured examples you can keep beside your desk.
- Excel shortcut guidesSee ideas
Build speed with keyboard shortcuts for selection, formatting and navigation.
- Numeric keypadsSee ideas
Helpful if you enter many numbers on a laptop or compact keyboard.
- External monitorsSee ideas
Useful for viewing large worksheets, formulas and reference tables side by side.
- Desk notebooksSee ideas
Sketch formula logic, report ideas and table structures before building.
- Laptop standsSee ideas
Make long spreadsheet sessions more comfortable and ergonomic.
Some links in this section may be affiliate links. Choose only what is useful for your own work.