The UNIQUE function was officially introduced with Excel 2021, but thanks to Modern Functions Update (MFU) you can now run it on your Excel 2007, 2010, 2013, 2016 and 2019 versions. It works exactly as its original Microsoft counterpart.
Description
The UNIQUE function returns a list of unique values from a range or array. The result is a dynamic array that automatically spills onto the worksheet and updates when the source data changes.
Think of it as the ultimate duplicate remover. Instead of using complex array formulas or the “Remove Duplicates” feature, a single formula gives you a clean, live list of unique values.
This is particularly useful for:
- Data cleaning: Quickly extract a list of distinct products, customers, or categories
- Creating dropdown menus: Generate clean, duplicate-free source lists for data validation
- Dashboard building: Create dynamic lists that update automatically when source data changes
- Data analysis: Count unique values by combining UNIQUE with COUNTA
- Report generation: Build lists that never contain duplicates
Syntax
=UNIQUE(array, [by_col], [exactly_once])
| Argument | Description |
|---|---|
array | Required. The range or array from which to extract unique values. |
[by_col] | Optional. A logical value specifying how to compare: FALSE (default) compares by row; TRUE compares by column. |
[exactly_once] | Optional. A logical value: FALSE (default) returns all unique values; TRUE returns only values that appear exactly once. |
Important Notes
- The
UNIQUEfunction is case-insensitive: “APPLE”, “Apple”, and “apple” are treated as identical. - If there are blank cells in the range,
UNIQUEconsiders the blank as a valid unique value. - The result is a dynamic array that spills into adjacent cells automatically.
UNIQUEtreats text and numbers as different types. “10” (text) and10(number) are considered different.
Examples
Basic Usage – Unique Values from a Column
To extract a list of unique values from the range all Column E:
=UNIQUE(E:E)
Unique Rows from Multiple Columns
To extract unique combinations from two columns:
=UNIQUE(B5:C15)
Returns all unique combinations of Group and Color.
Distinct Values (Appear Exactly Once)
To return only values that appear exactly once in the data:
=UNIQUE(B5:B16,,TRUE)
Returns only values that occur once, not those that appear multiple times.
Count Unique Values
To get a count of unique values, combine UNIQUE with COUNTA:
=COUNTA(UNIQUE(B5:B16))
Returns the number of unique values in the range.
Unique Values from Horizontal Data
If your data is arranged in columns rather than rows, set by_col to TRUE:
=UNIQUE(C4:I4,TRUE)
Extracts unique values from a horizontal range and spills the result across columns.
Sort Unique Values Alphabetically
Combine UNIQUE with SORT:
=SORT(UNIQUE(B5:B16))
Returns the unique values sorted in alphabetical order.
Unique Values Ignoring Blanks
To exclude blank cells from the result:
=UNIQUE(FILTER(B5:B16, B5:B16<>""))
The FILTER function removes blanks before UNIQUE processes the data.
Unique Values with Criteria
To extract unique values that meet specific criteria:
=UNIQUE(FILTER(B5:B16, C5:C16=E4))
Returns unique values from column B where the corresponding value in column C matches the criteria in E4.
Practical Use Cases
| Scenario | Formula |
|---|---|
| Extract unique products | =UNIQUE(A2:A100) |
| Count unique customers | =COUNTA(UNIQUE(B2:B100)) |
| Unique combinations | =UNIQUE(A2:B100) |
| Values appearing once | =UNIQUE(A2:A100,,TRUE) |
| Unique horizontal data | =UNIQUE(A1:E1,TRUE) |
| Sorted unique list | =SORT(UNIQUE(A2:A100)) |
| Unique with criteria | =UNIQUE(FILTER(A2:A100, B2:B100="USA")) |
| Ignoring blanks | =UNIQUE(FILTER(A2:A100, A2:A100<>"")) |
Common Problems
UNIQUE vs Legacy Methods
| Feature | UNIQUE | Legacy Methods (Array Formulas) |
|---|---|---|
| Syntax | Simple, readable | Complex and hard to maintain |
| Dynamic updates | ✅ Auto-updates | ❌ Manual intervention required |
| Spill range | ✅ Yes | ❌ No |
| Array formula required | ❌ No | ✅ Yes (Ctrl+Shift+Enter) |
Notes
- ✅ Fully tested – The implementation in Modern Functions Update works identically to Microsoft’s original function introduced in Excel 2021.
- ✅ The function is fully compatible with Excel 2007, 2010, and all later versions.
- ℹ️
UNIQUEis a dynamic array function, meaning results spill into adjacent cells. - ℹ️ If you want to convert the dynamic array to a fixed list, copy and paste as values.
- ⚠️
UNIQUEcannot be used inside an Excel Table; it will cause a#SPILL!error. - ℹ️ The function works with text, numbers, dates, and other data types.