With Office 2021, Microsoft added some new functions to the Excel program. Many seem unaware of these functions’ excellent potential and applications. Excel UNIQUE function is one of those new functions we will discuss in this guide, along with some practical examples.
The UNIQUE function has made the lives of Excel users much easier. Before Microsoft introduced this function, Excel users needed to write long nested formulas to get unique values without duplicates from a selected range. Now, the UNIQUE function does it with a short line of formula!
In this guide, we will thoroughly discuss the UNIQUE function. We are including everything from the syntax of the function to some excellent uses. We also showed each process in pictures for better understanding. By the time you reach the concluding section of this guide, you will become a master of Excel’s UNIQUE function!
Let’s jump in!
What is the UNIQUE Function in Excel?
The UNIQUE function in Excel is one of the recently added Dynamic Array Functions. It gives you an array or range of unique values from the defined data in the formula. This function belongs to the Lookup & Reference category. It is available only on Excel 365 subscriptions and in Excel 2021.
You can find and use the UNIQUE function in Excel in three ways. Let’s see each of them so you can pick your preferred way.
1. You can go to the Formulas tab and click on Lookup & Reference in the Function Library group. From the drop-down list, scroll down to find the UNIQUE function and click on it.
2. You may also go to the Formulas tab to find the Insert Function button in the Function Library. After clicking on this option, an Insert Function window will pop open.
Now, in the Search for a function box, write “UNIQUE” and click the Go button to find the function in the list. Then click on the found function under the Select a function section. Finally, click OK.
After following either option, you will get a Function Arguments box for inputting arguments for the formula. See the image below.
3. The old way of writing formulas in cells is the third way to use this function. This is the most popular method as well.
Write down the formula by putting the equal sign in a cell, followed by the function UNIQUE. Then include open parentheses, put your formula arguments, close parentheses, and hit Enter to complete the process.
Following the first two ways may seem uncomplicated if you are a new Excel user. And when you get used to the function, follow the formula writing method for a faster workflow.
Excel UNIQUE Function: Syntax Breakdown
Knowing the syntax of an Excel function before using it is a must. It helps an user learn about the arguments that are a part of the formula for the respective function. We are also following this strategy here, so we will now do a syntax breakdown of the Excel UNIQUE function below.
The formula for the function is =UNIQUE(array,[by_col],[exactly_once])
Here,
- UNIQUE = The function.
- array = The range of cells from where unique values will be returned.
- by_col = It is an optional argument referring to “by column” comparison of data. It takes boolean values. The TRUE value in this argument will look for unique values across columns and return them in cells. On the other hand, the FALSE argument will look for unique data from up to the down manner in the selected array and return the found unique values in cells. If this argument is omitted in the formula, the function selects FALSE as default.
- exactly_once = This argument is also optional in this function. Like in the previous argument, this one, too, takes boolean values. TRUE value will look for value(s) occurring only once in the selected range and return those in the cell. Contrarily, the FALSE value in this argument will return all the distinct values from the array. The UNIQUE function takes the FALSE value by default when this argument is not defined in the formula.
Keep reading the rest of this article to understand the arguments further and see the UNIQUE function in action. We will use it to show you six excellent uses.
How Does the UNIQUE Function Work?
The UNIQUE function considers three arguments when in operation. The first argument is array, the only mandatory argument in this function. The other two arguments may be optional, but they play an important role in the process.
Since the function has three arguments, it also works in three steps.
1. First, the UNIQUE function scans the selected range of cells or arrays.
2. Then it follows the next argument, by_col. If nothing is mentioned in the argument or a FALSE value is selected, it searches for unique values in rows of columns within the selected range. But if TRUE is selected, the function finds unique values by going to cells in a horizontal direction across columns.
3. In the third and final step, the function finds out what type of argument is to be picked up and returned. Here, the UNIQUE function follows the exactly_once argument’s value. If nothing is defined in this argument or FALSE is selected, the function will pick up all the values only once from the array. Therefore, there will be no duplicate values in the result. For instance, if a value occurs more than once in the array, the function will return it one time in the destination cell.
On the other hand, if the TRUE value is used here, the function will find and return values that occur only once in the defined array. In other words, The UNIQUE function will find values that have no duplicates in the selected range.
Let’s follow the pictures below for an example. First, we selected a cell to write the formula.
Then we wrote the UNIQUE formula and selected a range of cells or arrays for the array argument. As you can see, we are going with the default values for by_col and exactly_once arguments by not including them in the formula.
After we pressed the Enter button, the UNIQUE function gave us values from the defined array. It picked up everything but returned them as one instance.
The next section will show some practical examples of this UNIQUE function. They are interesting and will come in handy for any Excel user.
6 Great Uses of Excel UNIQUE Function
When the UNIQUE function was added in Excel 2021 and Excel 365, it delivered an option all the Excel users wanted for a long time. Before the introduction of this function, Pivot Tables and nested formulas with INDEX, MATCH, and FILTER functions were used for the same operation.
But now, the UNIQUE function has reduced the headache of professionals and has also been a regular part of an Excel user’s favorite functions. There are hundreds of applications of this top-level function. However, we are going to show you six important uses for it. They will give you a clear idea of working with this function.
We will use the dataset shown below for the demonstrations here.
Let’s start with the first application, then!
Use 1: Extracting Once-Only Unique Values
In the previous section on how this UNIQUE function works, we discussed how it determines what to pick up from the defined array. We also explained how the exactly_once argument works in this function. Here, we will see an excellent demonstration of it.
Let’s say you have a range of cells from which you must find non-repeating values. Now, to do it, take an empty column. Then in an empty cell, start writing the formula for it. For the array argument, select your range of cells. Then, for the by_col argument, put nothing as the value. For the exactly_once argument, select or write TRUE. Hit Enter to run the formula.
For instance, in the dataset below, we need to find values occurring only once from the Product column. We selected cells under the Product heading for the array argument.
Afterward, you will see that the UNIQUE function has returned only non-repeating values from your selection.
See the image below for this demonstration.
The UNIQUE function returned only the values that occurred only once in the selected range. No complications!
Use 2: Extracting Distinct Values
In this application of the UNIQUE function, we will show you how to get a single instance of values from repeating values. It is useful when an Excel user needs to create a list of items from a dataset with duplicate values.
This is the simplest way you can use the UNIQUE function. You need to write the formula for this operation and include only the array argument. Then press Enter to let the function show its magic to you.
See the images below for our demonstration. We wanted to create a list of products from the sales data. It has repeated sales of the same product. But we need only a list where just the product names are available.
We wrote the formula for the UNIQUE function and included the Product column in the array argument. Then we closed the bracket and hit Enter.
Straightaway, we got a list of products with no repeats in them.
Cool, isn’t it?
Use 3: Extracting Data with Alphabetization
What if you are asked to have your UNIQUE extracted list of items sorted? Do you extract the values and then sort them with multiple more steps? That’s the popular way, but let’s avoid it and use a handy approach. We are talking about Excel!
The solution is to nest the UNIQUE formula inside a SORT formula. It is pretty simple. Write your UNIQUE formula and then put it inside a SORT formula before pressing Enter after you are finished. Then your extracted items will be automatically alphabetized or sorted in ascending order.
To demonstrate, we are using the same dataset. Suppose we want to alphabetize our extracted non-repeating list of products when the UNIQUE function returns us with the values. See the picture below. We wrote the UNIQUE formula and put it inside SORT.
This way, the function returned us with an alphabetized product list.
Interesting, right?
Use 4: Using the UNIQUE Function Across Columns
So far, we have discussed applications where the requirement was to extract unique values from a column. In all these cases, the UNIQUE function did a vertical search from top to bottom and returned values based on defined arguments.
However, the requirement can change, and you may have to find unique values across columns with a horizontal search. The UNIQUE function lets you do it without too much effort. The value for the by_col argument determines this process.
Let’s understand it with a demonstration. Observe the picture here. We wanted to extract the names of the sales assistants, but they are used as column headers. Therefore, we need to search across columns (or do a horizontal search in a selected array). We put TRUE in the by_col argument to make it work this way. We kept the last argument exactly_once empty.
And see the result below!
The returned values will be put in a row as the search occurs across columns. If you want the result in columns, put the UNIQUE formula inside a TRANSPOSE formula.
As a result, although the formula extracts values from rows, you will get returned values in a column. See the image below.
So much work was done in under a few seconds!
Use 5: Using UNIQUE with an Array
The UNIQUE function’s work is not limited to a single column or row. It is capable of returning an array as well. When you define your array argument in the formula, you must select multiple columns according to your need.
We will show it with an easy demonstration here. We selected two columns for the array argument while writing the formula.
See the picture here. The UNIQUE function also returned values in an array. As our selection included Product and Delivery columns, it picked up single instances of all the products and accompanied corresponding Delivery values accordingly.
As a Dynamic Array Function, the UNIQUE function delivers big time!
Use 6: Combining Two Columns in One with the UNIQUE Function
How about extracting info from multiple columns but joining them together? Let’s give the CONCATENATE function some much-needed rest and use the UNIQUE function to complete two jobs simultaneously!
This application is advantageous when you want to extract names and join them together. However, since we are using a common dataset for demonstrations in this guide to maintain consistency, we will use it here too.
The process is really simple. You write the formula, select the first columns in the array argument, put an ampersand (&), then write your delimiter inside quotations. Then put another ampersand and select the second column. This whole part is the array argument in the formula.
Let’s see it in pictures. As you can see below, we wrote the formula for this operation. The first selected range was C3:C12, and the second one was D3:D12. The delimiter was an underscore which we wrote inside closed quotation marks. We put ampersands before and after the delimiter.
As a result, we got unique values from the first selected range of cells followed by the delimiter (in our case, it was an underscore). Then the corresponding values from the second selected range against the first selection were included in the result.
That’s it! If you want to use Space characters or Commas as delimiters, put them inside quotation marks. Your values from selected ranges will be separated by whatever you choose as your delimiter.
Wrapping-Up
In this visual guide on Excel UNIQUE Function, we discussed everything there is about this function with necessary demonstrations. We explained the syntax and how it works and included six of the most important applications of this UNIQUE function.
If you are an Excel 2021 user or have Excel 365 subscriptions, this UNIQUE function will add more dimensions to your work! Versions below Excel 2021 do not support this latest powerful Dynamic Array Function.
For effective learning, we strongly suggest that you use this UNIQUE function at least ten times. This way, you will get used to the arguments of the function. And whenever there is a situation where you can use this function, you will be able to do it without even thinking about it! If you don’t have a workbook to practice this function on, use the workbook we shared with you in this guide.
Learn the UNIQUE function, practice it enough, and take your Excel mastery to the next level!
Similar Post: