How it works.
UNIQUE scans a list or table, eliminates duplicate values, and returns a sorted list of unique entries. It is a dynamic array function that spills down to output cells automatically.
💡 In simple terms.
Looks at a list with duplicate entries and returns a clean list containing only one copy of each item.
"Think of this like sorting cards. If you have five duplicate red aces, it keeps only one copy and throws the rest away."
Formula syntax.
Standard Excel Notation=UNIQUE(array, [by_col], [exactly_once])[like_this] are optional; all other arguments are required.Formula arguments.
Every parameter accepted by the UNIQUE function is detailed below.
The range or array to extract unique values from.
Example value:A2:A6FALSE = Compare rows (default), TRUE = Compare columns.
Default: FALSEFALSE = Return all unique values (default), TRUE = Return only values that appear exactly once in the list.
Default: FALSESimple example.
Step-by-Step ScenarioExtract Sales Categories
Extract a clean list of unique product departments that recorded sales.
| Sale Department | Amount | Unique list | ||
|---|---|---|---|---|
| Electronics | 120 | — | — | — |
| Home Decor | 45 | — | — | — |
| Electronics | 250 | — | — | — |
=UNIQUE(A2:A6)Evaluates UNIQUE over the sample table cells, placing the verified output in cell C2:C6.
Formula generator.
Full Generator WizardCustomize the argument fields below to generate a tailored UNIQUE formula ready to paste directly into your worksheet.
=UNIQUE()Expected result behavior.
Returns "Calculated output" based on sample evaluation criteria and active cell references.
Best Practices
- Nest UNIQUE inside SORT to get a alphabetically or numerically sorted list of unique values (e.g. `=SORT(UNIQUE(A2:A20))`).
- You can retrieve unique rows across multiple columns (like unique First/Last name combos).
Common Mistakes
- Requires Excel 365 or 2021+. Returns #SPILL! if any cell in the output range is already occupied.
Platform & version compatibility.
Verified platform compatibility matrix based on actual spreadsheet calculation engine specifications.
In Excel 2019, use Data > Remove Duplicates or an Advanced Filter with "Unique records only".
Introduced in Excel 365 and Excel 2021. Not available in Excel 2019.
Native Google Sheets function for deduplicating ranges with automatic multi-cell spilling.
Frequently asked questions.
Technical Q&AHow does UNIQUE handle empty or blank cells?↓
By default, UNIQUE will return a 0 for blank cells in the source range. You can nest FILTER inside UNIQUE to exclude empty cells before deduplicating.
Can UNIQUE extract items that appear exactly once?↓
Yes. Setting the third argument exactly_once to TRUE will filter out any items that appear more than once, leaving only distinct unique entries.