UNIQUE Function: syntax, examples, mistakes and translation notes
Returns unique values.
Syntax
=UNIQUE(array, [by_col], [exactly_once])
Arguments
array
Range or array to deduplicate.
by_col
Optional TRUE to compare columns instead of rows.
exactly_once
Optional TRUE to return only values appearing exactly once.
Worked examples
=UNIQUE(A2:A100)
Returns the distinct values in A2:A100.
=SORT(UNIQUE(A2:A100))
Returns distinct values sorted alphabetically/numerically.
Practical tips
- UNIQUE spills results into neighboring cells.
- Wrap with SORT when a predictable display order matters.
- Use exactly_once when you need values that occur only one time, not ordinary deduplication.
Common mistakes
- Confusing “unique values” with “values occurring exactly once.”
- Placing the formula where the spill range is blocked.
Related functions
Frequently asked questions
What does UNIQUE do?
Returns unique values.
Why might a UNIQUE formula return an unexpected result?
Confusing “unique values” with “values occurring exactly once.” Start by testing the smallest referenced range or condition independently before adding wrappers or fallbacks.
Check the complete formula
Function syntax is only one part of a working workbook. Paste the finished expression into the Formula Checker for structural warnings, the Formatter for readability or the Translator when a supported locale mapping is involved.
Testing note: examples are educational. Spreadsheet versions, locale settings, tables, named ranges and workbook data can change behavior, so validate important formulas with representative data.