Summary
The Excel UNIQUE function extracts unique values from a range or array, allowing for efficient data cleaning and organization. It can handle various data types, including text, numbers, dates, and times. This function is especially valuable in consolidating data and identifying distinct entries in a dataset.
Syntax
=UNIQUE(array, [by_col], [exactly_once])
- array: The range or array from which to extract unique values.
- [by_col]: [Optional] Specifies how to compare and extract. By row (
FALSE, default); by column (TRUE). - [exactly_once]: [Optional]
TRUEto return values that occur exactly once,FALSE(default) to return all unique values.
Return value
An array of unique values.
How to use
Use UNIQUE by specifying the array from which to extract unique values. Optionally, adjust the function to extract based on unique columns (by_col) and to return values that appear only once (exactly_once).
Examples
Simple UNIQUE
Extracting Unique Values from a Range: To get unique values from a vertical range:
=UNIQUE(A2:A10)
This formula returns all unique values from the range A2:A10.
UNIQUE by Column
Extracting Unique Values Across Columns: To extract unique values from a horizontal range:
=UNIQUE(A1:E1, TRUE)
This formula extracts unique values from the horizontal range A1:E1, comparing across columns.
UNIQUE with Exactly Once Criteria
Extracting Values that Appear Only Once: To return values that occur exactly once in an array:
=UNIQUE(A2:A10, FALSE, TRUE)
This formula returns values from A2:A10 that appear only once in the range.
UNIQUE for Horizontal Data
Extracting Unique Values from a Row: To find unique values in a row:
=UNIQUE(A2:E2, TRUE)
This formula checks horizontally across A2:E2 and extracts unique values.
Advanced UNIQUE with FILTER
Advanced Unique Value Extraction with Criteria: Combining UNIQUE with FILTER to extract values based on a condition:
=UNIQUE(FILTER(A2:B10, B2:B10 = "Approved"))
This extracts unique values from A2:B10 where the corresponding value in B2:B10 is “Approved”.
Additional Notes
- The UNIQUE function is not case-sensitive; it considers “APPLE”, “Apple”, and “apple” as the same value.
- The function is dynamic and updates automatically if the source data changes.
- UNIQUE can be used with other functions like FILTER to perform more complex data extraction based on specific criteria.