Cleanup formula page

Unique List Formula

Use this when you need a clean list of owners, categories, IDs, or emails from a source column.

Best for

Create a unique list from a column.

What it returns

The output contains each nonblank value once.

Copy formulas

Excel formula

=UNIQUE(FILTER(A2:A100, A2:A100<>""))

Google Sheets formula

=IFERROR(UNIQUE(FILTER(A2:A100, A2:A100<>"")), "No values")

Excel / Google Sheets difference

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.

What it returns

The output contains each nonblank value once.

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.

Nonblank email list

=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

Sorted distinct emails

=SORT(UNIQUE(FILTER(A2:A100,A2:A100<>"")))

Google Sheets: Use the same sample email column. Returns: maya@example.com, then noah@example.com, alphabetically

All blank source

=IFERROR(UNIQUE(FILTER(A2:A100, A2:A100<>"")), "No values")

Google Sheets: Leave A2:A100 blank. Returns: No values

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.

Do not use a unique spill list when the destination is a fixed export range that cannot accept dynamic output.

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.

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.