The UNIQUE function is a Dynamic Array function that returns distinct (non-duplicate) values from a range or array.
It automatically removes duplicates and updates when the source data changes.
Syntax
=UNIQUE(array, [by_col], [exactly_once])
Arguments:
- array → The range from which you want unique values
- by_col (optional) → TRUE = compare by column, FALSE = compare by row
- exactly_once (optional) → TRUE = return values that appear only once
Basic Example
If A2:A10 contains:
IT
HR
IT
Finance
HR
Formula:
=UNIQUE(A2:A10)
Result:
IT
HR
Finance
Duplicates are removed automatically.
UNIQUE with Multiple Columns
If you have:
| Name | Department |
=UNIQUE(A2:B10)
Returns unique combinations of Name and Department.
Return Values That Appear Only Once
=UNIQUE(A2:A10, FALSE, TRUE)
Returns only values that appear exactly one time.
Example:
IT (2 times) → Removed
Finance (1 time) → Returned
UNIQUE with Tables (Recommended)
If your data is in a table named Employees:
=UNIQUE(Employees[Department])
Structured references make formulas dynamic and clean.
Combine UNIQUE with SORT
=SORT(UNIQUE(A2:A10))
Returns sorted unique values.
Combine UNIQUE with FILTER
=UNIQUE(FILTER(A2:A10, B2:B10="IT"))
Returns unique values only from IT department.
Common Errors
#SPILL!
Occurs when output area is blocked.
Important Notes
- Available in Excel 365 and Excel 2021+
- Automatically spills results
- Updates dynamically when data changes
- Case-insensitive by default
Benefits of UNIQUE
- Removes duplicates instantly
- Creates dynamic dropdown lists
- Simplifies reporting
- Works perfectly with FILTER and SORT
- Ideal for dashboards
UNIQUE vs Remove Duplicates Tool
| UNIQUE Function | Remove Duplicates |
|---|---|
| Dynamic | Manual |
| Updates automatically | Static result |
| Formula-based | One-time action |
Conclusion
The UNIQUE function is a powerful and modern Excel feature that automatically extracts distinct values. It improves efficiency, simplifies reporting, and makes data analysis more dynamic and professional.