The guide demonstrates how to use the UNIQUE function and dynamic arrays in Excel to create unique values. You’ll learn how to use a basic formula to locate unique values inside a column or row, across many columns, and depending on criteria, among other things.

Excel 365’s introduction of the Excel UNIQUE formula altered everything! What was once considered rocket science becomes as simple as ABC. Now, you don’t need to be an expert in formulas to extract unique values from a range based on one or more criteria and organize the results alphabetically. All of this is accomplished via the use of straightforward formulae that anybody can understand and adapt to their own requirements.

Notes on usage

The Excel UNIQUE function returns a list of values that are unique inside a range or array. As a consequence, a dynamic array of unique values is created. If this array is the end output (i.e. it is not passed to another function), array contents “spill” onto the worksheet into a range that updates automatically when new unique values are added or deleted from the source range.

The UNIQUE function accepts three arguments: an array, a by col parameter, and an exactly once argument. The first parameter, array, specifies the array or range from which unique values should be extracted. This is the only argument that is necessary. The second input, by col, specifies whether unique data are extracted by rows or columns. UNIQUE extracts unique values from rows by default. Set by col to TRUE or 1 to compel UNIQUE to extract unique data by column. The last option, exactly once, specifies the behavior for values that occur many times. By default, UNIQUE extracts all unique values, regardless of their number of appearances in the array. Set exactly once to TRUE or 1 to retrieve unique values that exist just once in the array.

 

UNIQUE function in Excel

In Excel, the UNIQUE function produces a list of distinct values from a range or array. It works with any form of data, including text, numbers, dates, and timings.

The function falls within the category of Dynamic Arrays functions. As a consequence, a dynamic array is created that naturally overflows onto adjacent cells, either vertically or horizontally.

The Excel UNIQUE function has the following syntax:

=UNIQUE(array, [by col], [once exactly])

Where:

• Array (needed) – the range or array from which unique values are to be returned.
• By col (optional) – a logical value specifying how data should be compared:
• TRUE – does cross-column comparisons.
• FALSE or omitted (default) – does cross-row comparisons.
• Exactly once (optional) – a logical value defining which values are regarded as unique:
• TRUE – yields values that only occur once, which corresponds to the database concept of uniqueness.
• FALSE or omitted (default) – returns the range or array’s distinct (different) values.

 

Important note:

At the moment, the UNIQUE feature is only accessible in Microsoft Excel 365 and Excel 2021. Excel 2019, 2016, and older versions do not allow dynamic array formulae, and so do not have the UNIQUE function.

Related Functions

• Excel Filter function
  The FILTER function extracts matched records from a collection of data using one or more logical checks. The include argument specifies logical tests, which might encompass a wide variety of formula conditions.==FILTER(array,include,[if empty])…