Dynamic arrays Updated November 2026

UNIQUE

UNIQUE(array, [by_col], [exactly_once]) returns the distinct values from a range as a spilled list, in the order they first appear. Wrap it in COUNTA to count distinct values or in SORT for an alphabetical list. It needs Excel 2021 or Microsoft 365.

Syntax

UNIQUE(array, [by_col], [exactly_once])

Part	What it is
`array`	The range or array to deduplicate. Several columns are treated as rows that must match completely.
`by_col`	FALSE (default) compares rows; TRUE compares columns.
`exactly_once`	FALSE (default) returns every distinct value; TRUE returns only values that occur exactly once.

Examples

=UNIQUE(A2:A100)

Result: Each distinct value in A2:A100 once, spilled downward

=COUNTA(UNIQUE(A2:A100))

Result: The number of distinct values

Add a FILTER to exclude blanks: =COUNTA(UNIQUE(FILTER(A2:A100, A2:A100 <> ""))).

=UNIQUE(A2:A100, , TRUE)

Result: Only the values that appear a single time

=SORT(UNIQUE(A2:B100))

Result: Distinct region and product pairs, sorted by the first column

Common mistakes

#SPILL!: the cells below the formula are not empty. Clear them or move the formula.
An empty cell in the source becomes a 0 in the output; filter blanks out first.
Values that look the same are listed twice because of trailing spaces or case differences in the source (UNIQUE is not case sensitive, but spaces count). TRIM the data.
#NAME? in Excel 2019 or earlier: UNIQUE is not available; use Remove Duplicates on the Data tab or a pivot table.

Practice it

The drills on Formuladrill check your answer on live data, free, no account needed.

Start practicing

Questions

How do I get a list of unique values in Excel?

=UNIQUE(A2:A100) in Excel 2021 or 365. In older versions use Data, Remove Duplicates on a copy of the column.

How do I count unique values in Excel?

=COUNTA(UNIQUE(A2:A100)). In older Excel: =SUMPRODUCT(1/COUNTIF(A2:A100, A2:A100)) on a range with no blanks.

Can I use the UNIQUE result in a data validation list?

Yes. Put =UNIQUE(A2:A100) in a cell such as H2, then set the validation source to =H2# which refers to the whole spilled range.

FILTERHow the Excel FILTER function returns all the rows of a range that meet a condition, with examples for one criterion, AND and OR, a not found value and SORT.
SORT and SORTBYHow the Excel SORT and SORTBY functions sort a range with a formula that updates itself, with examples for descending order, several columns and outside keys.
COUNTIFHow to use COUNTIF in Excel to count cells equal to a value, above a number, containing text or not blank, with examples, a duplicate check and fixes.
TEXTJOINHow the Excel TEXTJOIN function joins a range into one string with a separator and skips empty cells, with examples for comma lists and a conditional join.