Excel UNIQUE Function
Summary
The Excel UNIQUE function returns a list of unique values in a list or range. Values can be text, numbers, dates, times, etc.
Purpose
Extract unique values from range
Return value
Array of unique values
Syntax
=UNIQUE (array, [by_col], [exactly_once])
Arguments
- array - Range or array from which to extract unique values.
- by_col - [optional] How to compare and extract. By row = FALSE (default); by column = TRUE.
- exactly_once - [optional] TRUE = values that occur once, FALSE= all unique values (default).
Version
Usage notes
The Excel UNIQUE function extracts a list of unique values from a range or array. The result is a dynamic array of unique values. If this array is the final result (i.e. not handed off to another function), array values will "spill" onto the worksheet into a range that automatically updates when new uniques values are added or removed from the source range.
Examples
To return unique values from in the range A1:A10, you can use a formula like this:
=UNIQUE(A1:A10)
To return unique values from the horizontal range A1:E1, set the by_col argument to TRUE or 1:
=UNIQUE(A1:E1,1) // extract unique from horizontal array
The UNIQUE function has an optional argument called exactly_once that controls how the function deals with repeating values. By default, exactly_once is FALSE. This means UNIQUE will extract unique values regardless of how many times they appear in the source data. If set to TRUE or 1, UNIQUE will extract only unique values that appear just once in the source data:
=UNIQUE(A1:A10,0,1) // values that appear once only
UNIQUE is a new function available in Excel 365 only.
UNIQUE formula examples
This example uses the UNIQUE function together with the FILTER function. You can see a more basic example here.
The trick in this case is to apply criteria to the FILTER function to only allow values based on count of...
At the core, this formula uses the UNIQUE function to extract unique values, and the FILTER function apply criteria.
Working from the inside out, the FILTER function is used to apply criteria and extract only names...
This example uses the UNIQUE function together with the FILTER function. Working from the inside out, the FILTER function is first used to remove limit data to values associated with group A only:
FILTER(B5:B16,C5:...
This example uses the UNIQUE function. With default settings, UNIQUE will output a list of unique values, i.e. values that appear one or more times in the source data. However, UNIQUE has an optional third argument,...
This example uses the UNIQUE function together with the FILTER function. Working from the inside out, the FILTER function is first used to remove any blank values from the data:
FILTER(B5:B16,B5:B16<>"...
This example uses the UNIQUE function, which is fully automatic. When UNIQUE is provided with the range B5:B16, which contains 12 values, it returns the 7 unique values seen in D5:D11.
UNIQUE is a dynamic function. If...
Traditionally, counting unique items with an Excel formula has been a tricky problem, because there hasn't been a dedicated unique function. However, that changed when dynamic arrays were added to Excel 365, along with...
This example uses the UNIQUE function to extract unique values. When UNIQUE is provided with the range B5:B16, which contains 12 values, it returns the 7 unique values seen in D5:D11. These are returned directly to the...
The goal in this example is to return the minimum value that is unique, i.e. the minimum value that occurs only once in the data.
The UNIQUE function, new in Excel 365, will return a unique list of values from a set...
This example uses the UNIQUE function together with the FILTER function. The FILTER function removes data that does not meet required criteria, and the UNIQUE function further limits results to unique values only....
The FILTER function accepts an array of values and an "include" argument which filters the array based on a logical expression or value.
In this case, the array is provided as the named range "list1", which contains...
Related videos
In this video, we'll look at how to use the FILTER function together with the UNIQUE function to limit results using logical criteria.
In this video, we'll introduce the UNIQUE function. The UNIQUE function lets you extract unique values in a variety of ways.
In this video, we'll build a dropdown list using dynamic arrays to filter data by color. The dropdown list is created with data validation.
In this video, we'll look at a few ways to see or visualize arrays in a formula. One of the best things about the new dynamic array formula engine in Excel is that it's much easier to see and visualize arrays.
In this video, we’ll look at a core concept in dynamic array behavior - spilling and the spill range.
In this video, we'll look at how to count the unique values returned by the UNIQUE function, together with other functions like FILTER and COUNTA.
Related functions
See also
Download 200+ Excel Shortcuts
Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.
