UNIQUE Function in Excel is a quite easy-to-use function and is one of the useful Dynamic Array Functions. The usage of the UNIQUE Function is irreplaceable since it makes the toughest of jobs be done in a fraction of seconds.
So let’s start learning.
What is the UNIQUE Function in Excel?
UNIQUE Function returns a dynamic array ( range of cells ) that has all unique or distance values from our source data set. There are times when we have duplicacy in our data set, which can be altered by using the UNIQUE Function.
We can extract the values that exist only once in our data set or the distinct (different) values.
The UNIQUE formula is typed into one cell and when we click enter, the formula results spill into the dynamic range of cells.
Note – The UNIQUE Function of Excel is new and is supported by Microsoft Excel 365 and Excel 2022 versions. In the earlier versions, using the =UNIQUE formula will result into #NAME! error
Syntax and Arguments
The following points will explain to you, the function arguments required by the UNIQUE Formula of Excel.
- array – This is the range of cells from which we want to get the unique values. The range of cells can contain numbers, texts, or dates.
- [by_col] – This is an optional argument and its default value is 0 or FALSE. It can have two values:-
- TRUE / 1 – The formula works across columns.
- FALSE / 0 – The formula works across rows.
- [exactly _once] – This is also an optional argument and its default value is 0 or FALSE. It can have two values:-
- TRUE / 1 – The formula returns the values that exist only one time in our data set.
- FALSE / 0 – This makes the formula return the values that are distinct ( different ).
Examples to Understand the UNIQUE Function
In this section of the blog, we would now understand the practical form of the UNIQUE Function which is quite interesting and super easy to implement.
Example 1 – Simplest Example for UNIQUE Function
Let us say we have a list of winners of chess competitions held in the past 10 years as follows:-
From this list, we want to get the names of all the winners since one student has won multiple times. Use this formula in cell D2.
As a result, you can see that the formula has successfully returned the distinct winner names from the list.
Explanation – We have passed the range B2:B11 as an array argument so that the formula considers the winner names to give us the unique names. We have not specified the by_col and exactly_once argument which is why the default values for both the arguments ( FALSE ) are considered by the formula. The function finds the distinct ( exactly_once=FALSE) winner names across the winner names ( rows, by_col=0) contained in rows numbered from 2 to 11.
Example 2 – Finding UNIQUE Values across Columns
In this example, let us say we have the following list of winners:-
We have got each column ( from column B to column K ) telling the winner’s name for that year. We want to extract the name of the winners that won exactly once. Use the following formula in cell B4.
As a result, the formula has returned the name of winners that won exactly once. There are two students Ajay and Rihana that didn’t win multiple times past ten years.
Explanation – We have passed the range of cells B2:K2 containing the names of winners as the array argument of the UNIQUE Function. The by_col argument is TRUE as the names of winners are arranged column-wise ( from column B to column K ). The last argument exactly_once is also TRUE as we need the name of winners that won exactly one time.
You can learn the advanced usage of the UNIQUE formula from here.
This brings us to the end of the blog.
Thank you for reading.
- Dynamic Array Formulas in Excel – Usage, Example, #SPILL
- FILTER Function in Excel – Dynamic Filtered Range
- CHOOSE Function in Excel – Choosing Value according to Index
- Applications – FILTER Function in Excel
- ROWS Function in Excel – Get Number of Rows
- MATCH Function in Excel – Find Cell Position in Array