Excel UNIQUE function

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] TRUE to 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.

Related Functions

Excel RANDARRAY function

The Excel RANDARRAY function creates an array of random numbers, suited for advanced simulations and data analysis tasks.

Excel SORTBY function

The Excel SORTBY function sorts a range or array based on the values in one or more corresponding arrays, allowing for multi-criteria sorting.

Excel FILTER function

The Excel FILTER function dynamically isolates data in an array based on criteria, essential for targeted analysis and reporting.

Excel SORT function

The Excel SORT function dynamically organizes arrays/ranges in ascending or descending order, updating automatically with data changes.

Excel SEQUENCE function

The Excel SEQUENCE function generates a sequence of numbers, ideal for creating serial numbers and automated lists.

Content Navigation