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 sort. By row = FALSE (default); by column = TRUE.
- exactly_once - [optional] TRUE = values that occur once, FALSE= all unique values (default).
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
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...
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...
At the core, this formula uses the UNIQUE function to extract unique values and the COUNTA function to count these values. The FILTER function is used to apply criteria.
Working from the inside out, FILTER is used to...
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...
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. The FILTER function removes data that does not meet required criteria, and the UNIQUE function further limits results to unique values only....
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 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 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...
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...
Related videos
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.
With the introduction of dynamic array formulas, Excel includes 6 brand new functions that directly leverage dynamic array behavior. In this video, we quickly demo all 6.
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 look at a core concept in dynamic array behavior - spilling and the spill range.
In this video, we'll look at how to create a two-way summary with dynamic array formulas. The result is a summary table that's very similar to a pivot table.
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.
Related functions
See also
