UNIQUE

Before UNIQUE existed, extracting a distinct list from a column meant either copying and using Remove Duplicates — a manual, static operation — or building a complex array formula involving COUNTIF and IF that very few people could write from memory. UNIQUE replaces all of that with a single function. It spills a deduplicated list automatically, updates whenever the source data changes, and works cleanly with SORT, FILTER, and COUNTA to build dynamic category lists, dropdown sources, and summary headers that never need manual maintenance.

UNIQUE Syntax

=UNIQUE(array, [by_col], [exactly_once])

  array         →  The range or array to deduplicate
  by_col        →  FALSE = compare rows (default)
                   TRUE  = compare columns (deduplicate across columns)
  exactly_once  →  FALSE = return one of each value, even if repeated (default)
                   TRUE  = return ONLY values that appear exactly once
                           (values that appear more than once are excluded entirely)

The Most Common Use — Sorted Unique List

On its own, UNIQUE returns values in the order they first appear in the source. Wrapping it in SORT produces a clean alphabetically ordered list — which is almost always what you want for dropdown sources, category headers, and summary labels.

Regions in A2:A100 (mixed order, duplicates throughout)

Unique regions in first-seen order:
=UNIQUE(A2:A100)                   →  North, East, South, West

Unique regions sorted A to Z:
=SORT(UNIQUE(A2:A100))             →  East, North, South, West

Unique regions sorted Z to A:
=SORT(UNIQUE(A2:A100), 1, -1)      →  West, South, North, East

Count of distinct regions:
=COUNTA(UNIQUE(A2:A100))           →  4

The COUNTA(UNIQUE()) pattern is the clean way to count distinct values — something that previously required a SUMPRODUCT(1/COUNTIF()) formula that broke whenever the range contained blank cells. COUNTA(UNIQUE()) handles blanks cleanly because UNIQUE simply excludes empty cells from the result.

Unique Across Multiple Columns

When you pass a multi-column range to UNIQUE, it treats each entire row as the unit of comparison. Two rows are considered duplicates only if every column matches — it is a row-level deduplication, not a column-level one. This is the formula equivalent of "Remove Duplicates" when all columns are selected.

Data in A2:B10 — Region and Quarter columns

Unique Region + Quarter combinations:
=UNIQUE(A2:B10)

Row is only removed if BOTH Region AND Quarter match a previous row.
"North, Q1" and "North, Q2" are treated as different rows.

Three-column deduplication:
=UNIQUE(A2:C10)   →  Unique combinations of all three columns

Building a Dynamic Summary Table

One of the most powerful applications of UNIQUE is using its output as the row headers of a dynamic summary table — where the categories, totals, and counts all update automatically when the underlying data changes or grows.

Sales data: A=Rep, B=Region, C=Sales (A2:C100)

Step 1 — Dynamic region list as row headers:
E2:  =SORT(UNIQUE(B2:B100))         →  East, North, South, West (spills down)

Step 2 — Total sales per region, referencing the spill result:
F2:  =SUMIF(B2:B100, E2#, C2:C100)  →  totals for each region, aligned with E2#

Step 3 — Count of reps per region:
G2:  =COUNTIF(B2:B100, E2#)         →  counts aligned with E2#

The E2# operator references the entire spill range from E2.
As new regions are added to the data, E2# expands automatically.

UNIQUE as a Data Validation Source

Dynamic dropdowns are one of the most requested features in business spreadsheets — a dropdown that automatically includes new categories without manual updating. UNIQUE makes this straightforward when combined with a named range or a spill reference.

Step 1 — Put the UNIQUE formula in a helper cell, e.g. H2:
H2: =SORT(UNIQUE(B2:B100))

Step 2 — Create a Named Range pointing to the spill:
Name Manager → New → Name: RegionList → Refers to: =H2#

Step 3 — Apply Data Validation using the named range:
Data → Data Validation → Allow: List → Source: =RegionList

Result: the dropdown always contains the current distinct region values,
sorted alphabetically, with no manual updates ever required.

The hash operator (H2#) in the Named Range definition is what makes this dynamic — it references the entire spill range from H2 rather than a fixed number of rows. When a new region appears in column B, UNIQUE adds it to the spill, the Named Range expands, and the dropdown updates automatically.

Combining UNIQUE With FILTER

UNIQUE and FILTER work naturally together. A common pattern is to get the unique values that exist within a filtered subset — for example, finding which departments have employees earning above a threshold.

Employee data: A=Name, B=Department, C=Salary

Unique departments where salary exceeds £60,000:
=SORT(UNIQUE(FILTER(B2:B50, C2:C50>60000)))

How it works:
  FILTER(B2:B50, C2:C50>60000)  →  list of dept names for high earners
  UNIQUE(...)                    →  deduplicate that filtered list
  SORT(...)                      →  sort alphabetically

Unique regions that have North-region representatives:
=UNIQUE(FILTER(B2:B20, A2:A20="North"))

This three-function chain — SORT(UNIQUE(FILTER())) — is one of the most useful patterns in modern Excel. It answers questions like "what distinct categories exist within this filtered subset" entirely dynamically, with no manual steps and no helper columns.

The exactly_once Argument — Finding True Singles

The third argument of UNIQUE — exactly_once — changes its behaviour entirely when set to TRUE. Instead of returning one copy of each value, it returns only values that appear exactly once. Any value that appears two or more times is excluded from the result completely. This is useful for finding anomalies, one-off entries, or non-recurring items in a dataset.

Order IDs in A2:A20 — some appear multiple times (re-orders)

Find order IDs that appear exactly once (single orders only):
=UNIQUE(A2:A20, FALSE, TRUE)

Find reps who appear exactly once (sold in one region only):
=UNIQUE(A2:A20, FALSE, TRUE)

Practical use — finding transaction IDs with no duplicate:
=SORT(UNIQUE(A2:A100, FALSE, TRUE))

Next up — VSTACK and HSTACK, the functions that let you combine multiple ranges and arrays into a single unified table — vertically, horizontally, or both — making multi-source data consolidation a one-formula task.