Excel UNIQUE Function

The UNIQUE function extracts unique values from a range of data. The data can include text, numbers, dates, error values, etc.

Syntax

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

Arguments

Return Value

The UNIQUE function returns an array.

Function Notes

• array can be supplied as a row of values, a column of values, or a combination of rows and columns of values.
• The result returned by UNIQUE will spill into a range of cells in the way your original data of array is organized.
• If you have zero (0) and a blank cell in array, UNIQUE will return two zeros: One represents an empty cell, and the other represents the zero itself. Note: A blank is also considered as a distinct value.
• UNIQUE can be used between different workbooks. However, make sure the referenced workbooks are open. Otherwise, a #REF! error will be returned if you refresh the result cell.
• The results of UNIQUE are dynamic, meaning that they update automatically when values in the source data change. However, the results will not be updated if you add new entries to array. To fix this, you should use structured references as array argument.

Example

To extract the distinct values from the list as shown in the column B below, please copy or enter the formula below in the top cell of the result table and then press Enter to get the result:

=UNIQUE(B3:B16)

Note: The by_col and exactly_once arguments are omitted because they default to compare the rows against each other and return all the different values in the array.

Example to Retrieve Unique Values in a Row

To extract unique values from the data in the row C as shown below, please copy or enter the formula below in the left-most cell of the result table and then press Enter to get the result:

=UNIQUE(C3:J3,TRUE)

Note: In the results row, UNIQUE returns the empty string (“”) itself and zero (0) for the blank cell.

Related functions