UNIQUE

Excel Functions

Extract unique values from range

Syntax

EXCEL

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

Arguments

Parameter	Description
`array`	Range or array from which to extract unique values.
`by_col`	[optional] How to compare and extract. FALSE = by row (default); TRUE = by column.
`exactly_once`	[optional] TRUE = values that occur once, FALSE= all unique values (default).

Return Value

Array of unique values

Details

The UNIQUE function extracts a list of unique values from a range or array. The result is a dynamic array that spills onto the worksheet, automatically updating when source data changes. UNIQUE is often combined with other dynamic array functions like FILTER and SORT. For example, you can filter data before extracting unique values, or sort the results alphabetically. Use the ROWS function or COUNTA function to count the unique values returned by UNIQUE. Note that UNIQUE won't automatically adjust the source range if data is added or deleted. To use UNIQUE with a range that automatically resizes to fit the data, use an Excel Table or a dynamic range created with TRIMRANGE or the dot operator. Using the UNIQUE function is straightforward. Just provide a range or array: Here are a few variat

Examples

Basic usage

Using the UNIQUE function is straightforward. Just provide a range or array:

EXCEL

=UNIQUE(A1:A10) // unique values from A1:A10

Basic usage

Here are a few variations, which are explained in more detail below:

EXCEL

=UNIQUE(A1:B10) // unique rows from two columns
=UNIQUE(A1:E1,TRUE) // unique values from horizontal range
=UNIQUE(A1:A10,,TRUE) // unique values that appear exactly once
=SORT(UNIQUE(A1:A10)) // unique values, sorted

Unique values

In the worksheet below, the goal is to extract a list of unique colors from the range B5:B16. The formula in D5 is:

EXCEL

=UNIQUE(B5:B16)

Unique values by column

By default, UNIQUE compares values by row. To extract unique values from horizontal data (arranged in columns), set by_col to TRUE. In the worksheet b

EXCEL

=UNIQUE(C4:I4,TRUE)

Unique values by column

With by_col set to TRUE, UNIQUE compares values across columns instead of down rows. The result spills horizontally, returning the 5 unique colors: re

EXCEL

=TRANSPOSE(UNIQUE(C4:I4,TRUE))

Sort unique values

A common pattern is to combine UNIQUE with SORT to return unique values in alphabetical or numerical order. In the worksheet below, the goal is to ext

EXCEL

=SORT(UNIQUE(B5:B16))

UNIQUE

Syntax

Arguments

Return Value

Details

Examples

See Also