The UNIQUE function in Excel is a powerful tool that allows users to extract unique values from a range or array. This function simplifies the process of eliminating duplicates from data sets and is highly beneficial for data analysis and reporting tasks.
Syntax
| Argument | Description |
|---|---|
| array | The range or array from which to extract unique values. |
| by_col | (Optional) A logical value indicating whether to compare by columns (TRUE) or by rows (FALSE). |
| exactly_once | (Optional) A logical value indicating whether to return only unique values that appear exactly once in the dataset. |
The UNIQUE function has the following syntax:
UNIQUE(array, [by_col], [exactly_once])
Example
| Data |
|---|
| Apple |
| Banana |
| Apple |
| Cherry |
| Banana |
| Date |
Using the UNIQUE function on the above table to get unique values:
=UNIQUE(A1:A6)Practice Exercise 1
Task: Extract the unique values from the following data set.
Practice Exercise 2
Task: Extract unique values that appear exactly once from the following data set.
Hint: To return values that appear exactly once using the UNIQUE function, make sure to set the second argument to FALSE, even though it's the default. This is necessary because the third argument, which should be set to TRUE, filters the results to include only those values that appear exactly once.
Solution for Exercise 1
Using the UNIQUE function to extract unique names from the data set:
=UNIQUE(A2:A7)Solution for Exercise 2
Using the UNIQUE function to extract unique values that appear exactly once:
=UNIQUE(A2:A7, FALSE, TRUE)Conclusion
The UNIQUE function in Excel is a versatile tool that simplifies the process of identifying and extracting unique values from large data sets. By understanding and applying this function, users can enhance their data analysis capabilities and streamline their workflow.