Back to All Formulas
Dynamic ArraysLevel 4: Green (Lookups & Data Models)Difficulty: Intermediate
How to Use UNIQUE in Excel
Extracts an automatically spilling list of unique, deduplicated values from any column or range.
Formula Syntax
=UNIQUE(array, by_col, exactly_once)
Arguments Breakdown
| Argument | Requirement | Purpose & Details |
|---|---|---|
| array | Required | The range or array from which to return unique rows or columns. |
| by_col | Optional | FALSE to compare rows (default), TRUE to compare columns. |
| exactly_once | Optional | FALSE for all unique values, TRUE for items that appear only once. |
Step-by-Step Practical Business Example
Suppose you have the following dataset in Excel cells A1:D4:
| Customer City |
|---|
| Mumbai |
| Delhi |
| Mumbai |
| Bengaluru |
| Delhi |
Applied Formula:Result: Spills: Mumbai, Delhi, Bengaluru
=UNIQUE(A2:A6)
How it works: Removes duplicates automatically into a clean 3-row list without needing the Remove Duplicates button.
Common Errors & How to Fix Them
#SPILL!
Why it happens: The cells directly below the formula contain data blocking the dynamic array from spilling.
Fix: Clear all content from the spill range below the formula cell.
Ready to Master Excel?
Explore 7 LevelsMaster UNIQUE in Level 4
Learn UNIQUE alongside 500+ formulas, hands-on practice workbooks, and verifiable certificates.