UNIQUE function

The UNIQUE function returns a list of unique values in a list or range. 

Return unique values from a list of values

Return unique names from a list of names

UNIQUE function example: =UNIQUE(D2:D11)

UNIQUE function in use to sort a list of names

Note: September 24, 2018: The UNIQUE function is one of several beta features, and currently only available to a portion of Office Insiders at this time. We'll continue to optimize these features over the next several months. When they're ready, we'll release them to all Office Insiders, and Office 365 subscribers.

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

Argument

Description

array

Required

The range or array from which to return unique values

[by_col]

Optional

Is a logical value indicating how to compare; By row = FALSE or omitted; By column = TRUE

[occurs_once]

Optional

Is a logical value: only return unique values that occur once = TRUE; include all unique values = FALSE or omitted

Notes: 

  • An array can be thought of as a row of values, a column of values, or a combination of rows and columns of values. In the example above, the array for our UNIQUE formula is range D2:D11.

  • The UNIQUE function will return an array, which will spill if it's the final result of a formula. This means that Excel will dynamically create the appropriate sized array range when you press ENTER. If your supporting data is in an Excel Table, then the array will automatically resize as you add or remove data from your array range if you're using Structured References. For more details, see this article on Spilled Array Behavior.

  • Excel has limited support for dynamic arrays between workbooks, and this scenario is only supported when both workbooks are open. If you close the source workbook, any linked dynamic array formulas will return a #REF! error when they are refreshed.

  • You can download a workbook with UNIQUE function examples.

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

See Also

FILTER function

RANDARRAY function

SEQUENCE function

SINGLE function

SORT function

SORTBY function

#SPILL! errors in Excel

Dynamic arrays and spilled array behavior

Expand your Office skills
Explore training
Get new features first
Join Office Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×