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. 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. The FILTER function removes data that does not meet required criteria, and the UNIQUE function further limits results to unique values only....
Although FILTER is commonly used to filter rows, you can also filter columns. The trick is to use an include argument that operates on columns instead of rows. In this example, we use a hard-coded array constant 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...
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...
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. 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...
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. 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,...
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...
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 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 introduce the UNIQUE function. The UNIQUE function lets you extract unique values in a variety of ways.
In this video, we'll look at how to list duplicate values with the FILTER function. In other words, values that appear more than once in a set of data.
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 look at a core concept in dynamic array behavior - spilling and the spill range.
Related functions
See also
Download 200+ Excel Shortcuts
Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.
