Contents
Excel UNIQUE Function
When to Use the Excel UNIQUE Function in Excel
The UNIQUE function1 is super helpful when you want to find and pull out unique values from your data. This feature is particularly useful when dealing with datasets where you want to create a list of unique items for focused analysis or reporting.
What Excel UNIQUE Function Returns
The Excel UNIQUE function returns an array of unique values from the provided range or array.
Syntax of Excel UNIQUE Function
The syntax of the Excel UNIQUE function is as follows:
=UNIQUE(array, [by_col], [exactly_once])
Input Arguments
Here are the input arguments for the Excel UNIQUE function:
â array (Required): The range or array from which to return unique values.
â [by_col] (Optional) : A logical value indicating how to compare.
- TRUE: Compares columns against each other and returns unique columns.
- FALSE (or omitted): Compares rows against each other and returns unique rows.
â [exactly_once] (Optional) : A logical value that returns rows or columns occurring exactly once in the range or array.
- TRUE: Returns all distinct rows or columns occurring exactly once.
- FALSE (or omitted): Returns all distinct rows or columns.
Note: Excel UNIQUE function is exclusively accessible in Microsoft 365’s Excel and Excel for the web. It is not supported in pre-dynamic array versions of Excel (Excel 2019 and earlier) because they do not support array constants. To learn more about how to use Excel for Web for free. Watch đ this video
How to Get Microsoft Excel for Free | Free Microsoft Office | Microsoft Office 365
Examples of the Excel UNIQUE Function
Here are a few examples of using the Excel UNIQUE function:
Example 1: Basic Usage
Imagine you have a list of fruit names in column A, and some names repeat. If you want to make a list with only unique names, you can use this formula:
This formula returns a unique list of numbers from the range A1:A10.
Example 2: Unique Values by Column
We have seen in the previous example that by default, the Excel UNIQUE function gives us unique values from different rows. However, if your data is in different columns and you want unique values from them, then you have to write a formula like the one below:
This formula returns unique values column-wise from the range A1:J1.
Example 3: Extract Unique Values That Occur Exactly Once
To make this example simple, we listed the winners of the Cricket World Cup2. I want to find those who won the World Cup only once. To do that, we use this formula: =UNIQUE(B2:B14, ,TRUE)
We skip (omitted) the second argument here because, by default, the UNIQUE function compares rows.
The third argument, which is set to TRUE, indicates that we want to include only those values that occur exactly once. This means the formula will return a list of values that appear only once in the specified range (B2:B14).
Example 4: Extract Unique Values That Occur More Than Once
Now, we want to find teams that won the Cricket World Cup more than once. For that, we are using the following formula.
Let’s break down the formula =UNIQUE(FILTER(B2:B14, COUNTIF(B2:B14, B2:B14) > 1))
- COUNTIF(B2:B14, B2:B14): This part of the expression uses the COUNTIF function. It counts how many times each value in the range B2:B14 appears in the same range. The result is an array of counts corresponding to each value.
- COUNTIF(B2:B14, B2:B14)>1: The comparison > 1 is then applied to each count in the array. This creates a new array of Boolean values (TRUE or FALSE) where TRUE indicates that the corresponding value appears more than once, and FALSE indicates that it appears only once. (To view the array in the formula bar, select the section of the formula and press the F9 key on the keyboard.)
- FILTER(B2:B14, COUNTIF(B2:B14, B2:B14) > 1): The FILTER function uses the Boolean array to filter values from the range B2:B14. It includes only those values for which the corresponding count is greater than 1, meaning they appear more than once in the original range.
- UNIQUE(âĻ): The filtered result is then passed to the UNIQUE function. This function extracts unique values from the filtered list, ensuring that each team appears only once in the final result.
So, in simple terms, the formula is finding and listing the unique teams from the range B2:B14 that have won the World Cup more than once.
Extra Notes
â The UNIQUE function preserves the order of the first occurrence of each unique value.
â If there are blank cells in the specified range, the UNIQUE function considers them as a unique value and gives zero in the result.
â UNIQUE can handle both single-column and multi-column ranges.
Time to Apply Your Knowledge.
Get hands-on with mastering the Excel UNIQUE function! Use the provided Excel file to try it out and see how it works with sample data. If you have questions or find interesting insights, feel free to share in the comments.
Enjoy learning through practical experience! (Total 3 Sheets are there)
Conclusion
In conclusion, the Excel UNIQUE function is an essential tool for anyone working with data that requires the identification and extraction of unique values. Whether you are dealing with large datasets, managing inventory, or conducting data analysis, the UNIQUE function streamlines your tasks and provides valuable insights.
As with any Excel function, practice is key to mastering its usage. Experiment with different scenarios and datasets to become more proficient in applying the UNIQUE function effectively. Excel’s diverse functions empower users to elevate their data analysis capabilities, and the UNIQUE function is a valuable addition to your Excel toolkit.
Frequently Asked Questions (FAQs)
Q1: Can the UNIQUE function handle both single-column and multi-column ranges?
A1: Yes, the UNIQUE function is versatile and can efficiently handle both single-column and multi-column ranges, providing flexibility for different data scenarios.
Q2: How does the UNIQUE function treat blank cells in the specified range?
A2: If there are blank cells in the specified range, the UNIQUE function considers them as unique values and includes them in the result.
Q3: Is the order of unique values maintained in the result array?
A3: Yes, the UNIQUE function preserves the order of the first occurrence of each unique value in the result array.
Q4: Is the UNIQUE function case-sensitive when comparing text values?
A4: No, the UNIQUE function operates in a case-insensitive manner, treating uppercase and lowercase letters as identical.
Q5: How can I share my questions or insights about the UNIQUE function?
A5: Feel free to share your questions or insights in the comments. I am here to assist you and provide guidance on using the UNIQUE function effectively in Excel!
Other Related Excel Functions
Mastering Excel VLOOKUP Function: A Comprehensive Guide to Enhance Your Data Analysis Skills
Mastering Excel FILTER Function: A Comprehensive Guide for Data Filtering
Join me on Instagram, YouTube and WhatApp Channel for your daily dose of valuable tips and tricks! Catch insightful videos that will enhance your knowledge and skills. Don’t miss out â follow me now!