How to Use the UNIQUE Function in Excel
Today, we’ll explore the UNIQUE function, a handy feature available in both Microsoft Excel and Google Sheets. This function is designed to extract unique values from a specified range or array, effectively eliminating any duplicates.
Basic Syntax
The syntax of the UNIQUE function is straightforward:
=UNIQUE(range/array, [by_col], [exactly_once])range/array: Specifies the range of cells or array from which you want to extract unique values.by_col(optional): Determines the orientation for comparison:TRUE(default) – Compares values column by column.FALSE– Compares values row by row.
exactly_once(optional): Defines the frequency of occurrence for inclusion:FALSE(default) – Includes all unique values.TRUE– Includes only those values that occur exactly once.
Examples of Using the UNIQUE Function
Example 1: Extracting Unique Values
Consider a list of fruit names from which we need to identify unique items:
| A | B |
|---|---|
| 1 | Apple |
| 2 | Apple |
| 3 | Orange |
| 4 | Banana |
| 5 | Orange |
By applying the formula =UNIQUE(A1:A5), we extract the distinct fruit names: Apple, Orange, and Banana.
Example 2: Unique Values That Occur Exactly Once
To identify fruits that appear only once in our list, we use the formula =UNIQUE(A1:A5, FALSE, TRUE). This produces ‘Banana’ as the result, since it is the only fruit that meets this criterion.
The UNIQUE function is invaluable for managing datasets by allowing quick identification of unique elements. It simplifies data analysis and enhances both efficiency and organization in your work.
More information: https://support.microsoft.com/en-us/office/unique-function-c5ab87fd-30a3-4ce9-9d1a-40204fb85e1e