How it works.
UNIQUE scans a list or table, eliminates duplicate values, and returns a sorted list of unique entries. It is a dynamic array function that spills down to output cells automatically.
💡 In simple terms.
Looks at a list with duplicate entries and returns a clean list containing only one copy of each item.
Analogy
"Think of this like sorting cards. If you have five duplicate red aces, it keeps only one copy and throws the rest away."
Syntax guide.
=UNIQUE(array, [by_col], [exactly_once])
Parameters
arrayRequired
The range or array to extract unique values from.
by_colOptional
FALSE = Compare rows (default), TRUE = Compare columns.
Default: FALSEexactly_onceOptional
FALSE = Return all unique values (default), TRUE = Return only values that appear exactly once in the list.
Default: FALSEInteractive formula builder.
Fill in the input arguments below to construct your custom formula string. Copy it directly to paste into your Excel spreadsheets.
Generated Output
Best Practices
- Nest UNIQUE inside SORT to get a alphabetically or numerically sorted list of unique values (e.g. `=SORT(UNIQUE(A2:A20))`).
- You can retrieve unique rows across multiple columns (like unique First/Last name combos).
Common Gotchas
- Requires Excel 365 or 2021+. Returns #SPILL! if any cell in the output range is already occupied.