Cleanup formula page
Unique List Formula
Use this when you need a clean list of owners, categories, IDs, or emails from a source column.
Create a unique list from a column.
The output contains each nonblank value once.
Copy formulas
=UNIQUE(FILTER(A2:A100, A2:A100<>""))=IFERROR(UNIQUE(FILTER(A2:A100, A2:A100<>"")), "No values")Both formulas remove blank cells before UNIQUE. Google Sheets adds IFERROR so an entirely blank range displays No values instead of a no-match error.
Example data
| Region | Status | |
|---|---|---|
| maya@example.com | East | Active |
| noah@example.com | West | Active |
| maya@example.com | East | Active |
| West | Inactive |
The output contains each nonblank value once.
How the formula works
- FILTER removes blanks.
- UNIQUE returns each remaining value once.
- The output spills into cells below the formula.
| Syntax piece | Role in the formula |
|---|---|
| FILTER(A2:A100, A2:A100<>"") | The source list with blank values removed. |
| UNIQUE | The function that keeps one instance of each remaining value. |
| spill output | The dynamic result that fills the cells below the formula. |
Verified examples
=UNIQUE(FILTER(A2:A100, A2:A100<>""))Excel: Use the sample email column containing maya@example.com and noah@example.com. Returns: maya@example.com, then noah@example.com
=SORT(UNIQUE(FILTER(A2:A100,A2:A100<>"")))Google Sheets: Use the same sample email column. Returns: maya@example.com, then noah@example.com, alphabetically
=IFERROR(UNIQUE(FILTER(A2:A100, A2:A100<>"")), "No values")Google Sheets: Leave A2:A100 blank. Returns: No values
Common errors and fixes
| Issue | Likely cause | Fix |
|---|---|---|
| The unique list shows a spill error | One or more cells below the formula already contain values. | Clear the spill area or move the formula to an empty column. |
| Apparent duplicates remain | Values differ by hidden spaces or other invisible characters. | Normalize the source with TRIM or CLEAN before applying UNIQUE. |
| UNIQUE is unavailable | The Excel version predates dynamic-array functions. | Use a supported Excel version or a legacy advanced-filter workflow. |
When not to use this formula
- Do not use a unique spill list when the destination is a fixed export range that cannot accept dynamic output.
Alternatives
| Alternative | When to use it |
|---|---|
| Count Unique Values Formula | Use when only the number of distinct nonblank values is needed. |
| COUNTUNIQUE Formula Builder | Use when a Google Sheets distinct-count calculation needs guided setup. |
Related formulas
Official references
- UNIQUE function from Microsoft
- COUNTUNIQUE function from Google
FAQ
Does UNIQUE preserve first-seen order?
Yes. The unsorted formula preserves the order of the first occurrence; add SORT when alphabetical order is required.
Where does the unique output appear?
The result spills into the cells below the formula, so that area must be clear.