Excel UNIQUE Function

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.

excel unique function1

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])…
Related Posts

Excel TRANSPOSE Function

Excel TRANSPOSE formula allows you to rotate (swap) values from rows to columns and vice versa in Excel. Its goal, as a component of the Excel lookup and reference functions, is to arrange data in the appropriate manner. To run ...

Extract Common Values in Two Lists

Just assume that you have two lists containing values/words in the few cells, and you want to extract the same or common values/words from the two lists into another separate list; then you might think that it's not a big ...

How to Count Unique Dates in Excel

This post will guide you how to count unique dates in a given range in Excel 2013/2016 or Excel office 365. How do I count the unique dates in a list of dates with some duplicate dates using a formula ...

Excel Vlookup Function

This post will guide you how to use Excel VLOOKUP function with syntax and examples in Microsoft excel. Description The Excel VLOOKUP function lookup a value in the first column of the table and return the value in the same ...

Excel Rows Function

This post will guide you how to use Excel ROWS function with syntax and examples in Microsoft excel. Description The Excel ROWS function returns the number of rows in a cell reference. The ROWS function is a build-in function in Microsoft ...

Sidebar