Dynamic arrays · distinct values · Nov 2026

The UNIQUE function,
measured & explained.

Syntax, a live playground, a measured distinct-count gallery, the #SPILL! trap, exactly_once, and a sourced error library: the dynamic-array UNIQUE function.

D1
fx
=UNIQUE(A2:A8)
0
measured gallery rows
0
sourced errors fixed
0
platforms checked
// 01 — Anatomy

How UNIQUE works

Returns the distinct values from a range, spilled into neighboring cells. Available in Excel 365 / 2021 and Excel Online.

=UNIQUE(array, [by_col], [exactly_once])
arrayThe range or array to dedupe.
by_colFALSE (default) compares rows; TRUE compares columns.
exactly_onceFALSE keeps one of each; TRUE keeps only values appearing once.
.bool-moat td.res-text{color:var(--cyan);font-weight:700;font-family:var(--mono)}
// 02 — Distinct values

What UNIQUE actually returns

UNIQUE removes duplicates and spills the distinct list into neighboring cells. The live gallery uses aggregate wrappers (COUNTA, ROWS, INDEX) so each result is a single measurable value; the full spill is shown below. Every row was measured live in HyperFormula. Click any row to load it.

Distinct-count gallery — measured wrappers

ExpressionResultWhy
=COUNTA(UNIQUE(A2:A8))4Count the distinct names
=ROWS(UNIQUE(A2:A8))4Height of the spilled list
=COUNTA(UNIQUE(B2:B8))2Only two distinct regions
=INDEX(UNIQUE(A2:A8),1)AliceFirst unique value
=INDEX(UNIQUE(A2:A8),2)BobSecond unique value
=INDEX(UNIQUE(A2:A8),4)DaveFourth unique value
=SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8))4Pre-365 unique count (no spill)
=UNIQUE(A2:A8)AliceTop-left of the spill (Excel shows all 4)

The full spill: =UNIQUE(A2:A8) fills four cells

ExpressionResultWhy
A9Alicefirst occurrence kept
A10Bobsecond distinct value
A11Carolthird distinct value
A12Davefourth distinct value

No dynamic arrays? Count uniques the pre-365 way

ExpressionResultWhy
=SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8))4works in every Excel version
=UNIQUE(A2:A8,,TRUE)(only-once)exactly_once keeps values appearing once
⚠ UNIQUE spills: the formula sits in one cell but the result fills the cells below it. If those cells are occupied, Excel returns #SPILL!. Clear the spill range, or move the formula to an empty area.
UNIQUE keeps first-occurrence order (it does not sort). The optional 3rd argument exactly_once=TRUE keeps only values that appear a single time; the 2nd argument by_col=TRUE compares columns instead of rows.
// 03 — Try it live

Interactive UNIQUE playground

Edit the table or type any formula — the HyperFormula engine evaluates it instantly. Load a preset to count distinct values, read the spill height, and use the pre-365 SUMPRODUCT method.

A1Sample data — click any cell to edit, the formula re-evaluates live
Result
—
✓ readyHyperFormula 3.5
// 04 — Error library

The real-world error library

8 failure patterns mined from actual user reports on Reddit, Stack Overflow and Super User — each with the authentic symptom, root cause, a fix, and a runnable formula. Not textbook theory.

// 05 — Cross-platform

Where UNIQUE works

UNIQUE requires dynamic arrays: Excel 365 / 2021, Excel Online and Google Sheets. Excel 2019 and earlier return #NAME?; use SUMPRODUCT(1/COUNTIF) there.

PlatformSupportNotesGotcha
✓ Supported full UNIQUE◐ Partial works with caveatsCompiled from public docs · verify against your build
// Go deeper

Master dynamic arrays

UNIQUE is one piece. The full course walks the toolkit end to end with live, measured examples.

  • UNIQUE / FILTER / SORT — the dynamic-array trio
  • The #SPILL! error and how to clear it
  • exactly_once and by_col arguments
  • The pre-365 SUMPRODUCT(1/COUNTIF) alternative
Free while in beta
Browse the function library

No account needed. Every page has a live playground.

// 07 — FAQ

Frequently asked