UNIQUE
Returns a list of unique values from a range or array.
=UNIQUE(array, [by_col], [exactly_once])This static page keeps the function indexed for search, while the spreadsheet app handles interactive exploration and saved formulas.
What the function does
Overview
Returns a list of unique values from a range or array.
The UNIQUE function is a powerful dynamic array formula that evaluates a range or array and returns only the distinct values found within it. By default, it scans rows vertically and returns a unique list in a spill range. However, by setting the [ol] argument to TRUE, the function can compare data across columns instead. A key technical feature is the [nce] argument; when set to TRUE, it filters the results to only include items that appear a single time in the source array, which is ideal for identifying non-repeating entries. Best practices involve nesting UNIQUE within the SORT function to ensure the resulting list is organized alphabetically or numerically. Note that UNIQUE is case-insensitive (e.g., 'DATA' and 'data' are treated as identical) and will return a zero for any blank cells found in the source range. Ensure the output area is clear, as the function requires an unobstructed spill range to display all results.
Quick reference
Syntax
=UNIQUE(array, [by_col], [exactly_once])Inputs
Arguments
Example: A2:A100
Example: FALSE
Example: FALSE
Formula patterns
Examples
Extract a distinct list of product names
=UNIQUE(A2:A100)Find items that occur only once in a list
=UNIQUE(B2:B50, FALSE, TRUE)Get unique rows based on multiple columns
=UNIQUE(A2:C20)Avoid these issues
Common Errors
#SPILL!
Cause: The range where the unique results need to be displayed contains existing data or merged cells.
Fix: Delete any data in the cells below or to the right of the formula cell to provide space for the results.
#CALC!
Cause: Occurs if the [exactly_once] argument is set to TRUE, but every value in the source range appears more than once.
Fix: Set the third argument to FALSE or omit it if you want all distinct values regardless of frequency.
Platform support
Compatibility
Dynamic array function. Excel 2021+ and Microsoft 365
Source: Microsoft Support
Common questions
Frequently Asked Questions
Returns a list of unique values from a range or array.
array: The range to extract unique values from ol: TRUE to compare columns, FALSE to compare rows nce: TRUE to return only values that appear once
#SPILL!: Delete any data in the cells below or to the right of the formula cell to provide space for the results. #CALC!: Set the third argument to FALSE or omit it if you want all distinct values regardless of frequency.