The UNIQUE function in Excel is a powerful tool that allows you to extract unique values from a range or array. This function is particularly useful when you want to remove duplicates or identify distinct values in your data. In this article, we will explore the various applications of the UNIQUE function and provide examples to help you understand its functionality.
Table of Contents
- The arguments of the UNIQUE function
- Examples of using the UNIQUE function
- The difference between unique and distinct
- UNIQUE linked to an Excel table
- UNIQUE across multiple columns
- Using UNIQUE across columns
- Combining UNIQUE with SORT in a data validation list
- Simple formula-based Pivot Report
The Arguments of the UNIQUE Function
The UNIQUE function in Excel has three arguments: array, [by_col], and [exactly_once]. The array argument specifies the range or array from which you want to extract unique values. The [by_col] argument is optional and determines whether the comparison should be done by row or by column. If omitted, it defaults to comparing by row. The [exactly_once] argument is also optional and determines whether the function should return only the items that appear once or a distinct list of all items. If omitted, it defaults to returning a distinct list.
Examples of Using the UNIQUE Function
Let's explore some examples to understand how the UNIQUE function works in different scenarios.
Example 1 - The Difference Between Unique and Distinct
In this example, we have a list of names in column B, and we want to extract the unique values. The formula in cell C3 is =UNIQUE(B3:B10). Since we haven't used the [exactly_once] argument, the function returns a distinct list of names that appear in the range.
Example 2 - UNIQUE Linked to an Excel Table
In this example, we have an Excel table named "tblExam" with a column named "First." We want to extract the unique values from this column. The formula in cell G3 is =UNIQUE(tblExam[First]). The UNIQUE function automatically expands to include new values added to the table.
Example 3 - UNIQUE Across Multiple Columns
The UNIQUE function is not limited to a single column; it can also assess the unique combination of multiple columns. In Method 1, we include both the First and Last name columns in the array: =UNIQUE(B3:C10). This returns a unique list with both columns included. In Method 2, we concatenate the columns before applying the UNIQUE function: =UNIQUE(B3:B10&" "&C3:C10). This returns a single column of unique values.
Example 4 - Using UNIQUE Across Columns
By default, Excel assumes that the UNIQUE function should be applied to a vertical list. However, it can also work on a horizontal list. In this example, we have a horizontal list in cells D2:I2, and we want to extract the unique values. The formula in cell K3 is =UNIQUE(D2:I2,TRUE), where the [by_col] argument is set to TRUE to indicate that the data is in a horizontal format. We can use the TRANSPOSE function to convert the horizontal list to a vertical list if needed.
Example 5 - Combining UNIQUE with SORT in a Data Validation List
In this example, we combine the UNIQUE and SORT functions to create a sorted, unique list for use in a data validation drop-down list. The formula in cell G3 is =SORT(UNIQUE(C3:C10&", "&B3:B10)), where we concatenate the Last and First names and then sort the resulting list alphabetically. We can use the spill range reference G3# as the source for the data validation list.
Example 6 - Simple Formula-Based Pivot Report
In this final example, we demonstrate how to create a simple Pivot Report using the UNIQUE function combined with other common functions. The formula in cell G3 is =UNIQUE(E3:E10), which returns the unique values in the Pass/Fail column. The formula in cell H2 is =TRANSPOSE(UNIQUE(D3:D10)), which switches the output of UNIQUE from rows to columns. Finally, the formula in cell H3 is =COUNTIFS(E3:E10,G3#,D3:D10,H2#), which uses the # references to automatically spill the results based on the dependent cells.
Conclusion
The UNIQUE function in Excel is a powerful tool for extracting unique values from a range or array. It can be used in various scenarios, such as removing duplicates, identifying distinct values, and creating dynamic reports. By understanding the arguments and examples provided in this article, you can leverage the UNIQUE function to enhance your data analysis and reporting capabilities in Excel.
Note: The examples and explanations provided in this article are based on the functionality of the UNIQUE function in Excel. Please ensure that you have the appropriate version of Excel (Microsoft 365, Excel Online, or Excel 2021) to use this function.
*This article is intended to provide a comprehensive guide to using the UNIQUE function in Excel. It is written in a clear and concise manner to help readers understand the concepts and apply them effectively.