UNIQUE function - quick way to find unique values in Excel

The introduction of the UNIQUE function in Excel 365 has changed everything! What used to be a rocket science becomes as easy as ABC. Now, you don't need to be a formula expert to get unique values from a range, based on one or multiple criteria, and arrange the results in alphabetical order. All is done with simple formulas that everyone can read and adjust for your own needs.

Excel UNIQUE function

The UNIQUE function in Excel returns a list of unique values from a range or array. It works with any data type: text, numbers, dates, times, etc.

The function is categorized under Dynamic Arrays Functions. The result is a dynamic array that automatically spills into the neighboring cells vertically or horizontally.

The syntax of the Excel UNIQUE function is as follows:

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

Where:

Array (required) - the range or array from which to return unique values.

By_col (optional) - a logical value indicating how to compare data:

• TRUE - compares data across columns.
• FALSE or omitted (default) - compares data across rows.

Exactly once (optional) - a logical value that defines what values are considered unique:

• TRUE - returns values that occur only once, which is the database notion of unique.
• FALSE or omitted (default) - returns all distinct (different) values in the range or array.

Basic UNIQUE formula in Excel

Below is an Excel unique values formula in its simplest form.

The goal is to extract a list of unique names from the range B2:B10. For this, we enter the following formula in D2:

=UNIQUE(B2:B10)

Please notice that the 2nd and 3rd arguments are omitted because the defaults work perfectly in our case - we are comparing the rows against each other and wish to return all the different names in the range.

When you press the Enter key to complete the formula, Excel will output the first found name in D2 spilling the other names into the cells below. As the result, you have all the unique values in a column:

In case your data is across the columns from B2 to I2, set the 2nd argument to TRUE to compare the columns against each other:

=UNIQUE(B2:I2,TRUE)

Type the above formula in B4, press Enter, and the results will spill horizontally into the cells to the right. Thus, you'll get the unique values in a row: