What is the UNIQUE Function
The UNIQUE function returns a list of distinct values from a range or array. It automatically removes duplicates and returns a dynamic array. UNIQUE is available in Excel 365 and 2021 and later.
Formula: =UNIQUE(array, [by_col], [exactly_once])
Practical Example 1
A2:A100 has customer names with duplicates.
Type: =UNIQUE(A2:A100)
Result: a list of unique customers only
Practical Example 2
Sort the unique list alphabetically.
Type: =SORT(UNIQUE(A2:A100))
Result: unique customers sorted A to Z
Practical Example 3
Return only values that appear exactly once.
Type: =UNIQUE(A2:A100, FALSE, TRUE)
Result: values that appear only once
When to Use It
UNIQUE is used for dropdown lists of categories, for customer deduplication, for distinct product lists, for removing duplicates without the Remove Duplicates tool, and for any report where you need a unique value list.