UNIQUE Function in Excel — The Duplicate Slayer (2026) | Sheets & Cells
⚡ Dynamic Array Starters

UNIQUE Function in Excel

Returns the distinct values from a range — as a live, spilling formula. The function that killed the Remove Duplicates dialog for anything reactive, powers every serious data-validation dropdown, and finally makes "count distinct" a one-line formula. One column in, distinct values out, forever fresh.

Modern Excel Only
UNIQUE requires Microsoft 365, Excel 2021+, Excel Online, Google Sheets, or LibreOffice 7.4+. Excel 2019 and earlier will show #NAME?. Check your version before deploying to shared workbooks.

🗡 The Duplicate Slayer — What UNIQUE Actually Changed

Before UNIQUE, deduplication was a chore: click through a dialog, hope you selected the right columns, get a one-time snapshot. Add new data → repeat the ceremony. Not anymore.

Before 2020 — Legacy Excel

Remove Duplicates dialog or complex COUNTIF/INDEX formulas

{=IFERROR(INDEX($A$2:$A$100,MATCH(0,COUNTIF($B$1:B1,$A$2:$A$100),0)),"")}
The dialog was a one-shot — click, dedupe, done. Source changed? Do it again. The array formula worked but required Ctrl+Shift+Enter, broke silently on empty ranges, and was almost impossible to debug or extend to multi-column pairs.
2020+ — Modern Excel

One formula, spills every distinct value automatically

=UNIQUE(A2:A100)
Type once. Spills every distinct value into the cells below with a blue-dashed spill border. Source grows → distinct list grows. Adds a new value → appears instantly. Multi-column pairs, "exactly once" mode, and "unique across rows" all controlled by two optional arguments.
The paradigm shift: deduplication became a first-class formula. Same source data → different UNIQUE formulas can produce distinct customers, distinct region-category pairs, and one-time buyers, all live-updating from the single source. No copy-paste, no repeated dialog clicks, no stale snapshots.

Quick Answer

UNIQUE returns the distinct values from a range. Live formula — updates when source changes. Optional 2nd argument switches to distinct columns; optional 3rd argument returns only values appearing exactly once.

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

Example: =UNIQUE(A2:A100) spills every distinct value from column A. Multi-column pairs: =UNIQUE(A2:B100). One-time only: =UNIQUE(A2:A100, FALSE, TRUE). First-occurrence order preserved (use SORT wrapper for A-Z).

⚡ Dynamic Arrays Practice Workbook

All 5 UNIQUE examples plus FILTER and SORT patterns on 30 realistic Q1 2026 sales orders.

↓ Download .xlsx (Free)
Filedynamic-arrays-2026.xlsx
Size~30 KB
Sheets6 tabs
CoversFILTER · UNIQUE · SORT
2020
Introduced
1–3
Arguments
365 / 2021+
Required
80+
Templates Use It

Syntax

UNIQUE takes 1 required argument and 2 optional arguments that unlock powerful modes.

ArgumentTypeDescription
array Required The range or array to deduplicate. Can be one column (returns distinct values) or multiple columns (returns distinct ROW combinations).
by_col Optional FALSE (default) treats each ROW as a unit. TRUE treats each COLUMN as a unit — useful when data is laid out horizontally.
exactly_once Optional FALSE (default) returns each distinct value once (standard dedupe). TRUE returns only values that appear EXACTLY once in the source — perfect for one-time buyers, orphans, outliers.

Order preservation: UNIQUE returns values in first-occurrence order, NOT alphabetical. Wrap with SORT for A-Z: =SORT(UNIQUE(A2:A100)).

The Three Arguments — Full Power Unlocked

Most people use only the first argument. Learning arguments 2 and 3 unlocks patterns you didn't know were possible.

1arrayRequired
=UNIQUE(OrderCust)

The range to dedupe. Single column → distinct values. Multi-column → distinct row combinations (Region + Category pairs, First+Last name pairs, etc.).

2by_col — the orientation switchOptional
=UNIQUE(A1:Z1, TRUE) // TRUE = distinct COLUMNS

Default FALSE treats rows as units. Set TRUE when your data is laid out horizontally (dates across columns, months as headers). Rarely used but critical when needed.

3exactly_once — the surgical modeOptional
=UNIQUE(OrderCust, FALSE, TRUE) // only single-occurrence values

The killer feature. TRUE returns only values that appear exactly once in the source. Perfect for finding one-time buyers, orphaned records, data-entry errors, or the "long tail" of any distribution.

5 Worked Examples

Example 01

Distinct regions from the order log

The simplest UNIQUE. One column in, distinct values out. Order matches first occurrence in the source (not alphabetical).

=UNIQUE(OrderRegion)

Spill Zone

Region
North
West
East
South

Spills 4 rows in first-occurrence order. Add a new region to source → appears at the bottom. For alphabetical order, wrap: =SORT(UNIQUE(OrderRegion)).

Example 02

Distinct customer list — deduplication in action

Some customers appear in multiple orders. UNIQUE collapses 30 order rows down to 15 distinct customer names. This is the "master customer list" pattern.

=UNIQUE(OrderCust)
Customer (15 distinct)
Acme Corp
TechFlow LLC
Datawise Inc
Blue Ocean Ltd
Metroplex Holdings
Cornerstone Group
Riverbend Co
Summit Partners
Pacific Ventures
Northern Lights Co
Vertex Solutions
…

Deduplicates from 30 → 15 rows. Feed this into a Data Validation dropdown source and your customer list stays fresh as orders come in.

Example 03

Multi-column: distinct Region + Category pairs

Pass a multi-column range and UNIQUE returns distinct ROW combinations, not individual values. This is the "pivot table without a pivot table" pattern.

=UNIQUE(CHOOSE({1,2}, OrderRegion, OrderCat))
RegionCategory
NorthElectronics
WestFurniture
EastFurniture
SouthSoftware
EastElectronics
WestOffice Supplies
NorthOffice Supplies
SouthFurniture
……

Returns 16 distinct pairs from the 30 orders. CHOOSE bundles two separate columns into a virtual 2-column array. Great for building a "which combinations exist" report before building a real pivot.

Example 04

exactly_once mode — customers who ordered exactly once

The 3rd argument's killer power. TRUE returns only values that appear EXACTLY ONCE in the source. Repeat buyers are excluded. Perfect for finding one-time customers, orphaned records, or outlier data.

=UNIQUE(OrderCust, FALSE, TRUE)
One-Time Customers
Pacific Ventures … wait, no — they ordered twice
Vertex Solutions … also twice
Northern Lights Co … twice too
(Result: 0 customers ordered exactly once)

In this specific dataset, every customer has 2+ orders — a good problem to have. In real datasets, the one-time list is often the churn risk segment worth targeting. Use for anti-outreach lists, data-entry error hunting, or "long tail" analysis.

Example 05

UNIQUE + SORT combo: distinct customers, alphabetized (the dropdown pattern)

Deduplicate first, then alphabetize. The single most-used dynamic array pattern in real workbooks — the source of truth behind every Data Validation dropdown that stays fresh.

=SORT(UNIQUE(OrderCust))
Customer (A → Z)
Acme Corp
Blue Ocean Ltd
Cornerstone Group
Datawise Inc
Metroplex Holdings
Northern Lights Co
Pacific Ventures
Riverbend Co
Summit Partners
TechFlow LLC
Vertex Solutions
…

15 distinct customers, A–Z. Then point Data Validation at the spill reference (=A2#) and your dropdown updates automatically as new customers enter the source data. Zero maintenance.

Interactive Playground

30-row Orders table with distributed values
Regions in source → 4 distinct
Customers → 15 distinct (some repeat)
Products → 12 distinct
Regions × Categories → 16 pairs
One-time customers → 0 (all repeat)
Formula & Spill Count
=UNIQUE(OrderRegion)
spills 4 rows
=UNIQUE(OrderCust)
spills 15 rows
=UNIQUE(OrderProduct)
spills 12 rows
=SORT(UNIQUE(OrderCust))
15 rows, A-Z
=COUNTA(UNIQUE(OrderCust))
15 (count distinct)

The Dynamic Array Starters

Three modern-Excel functions that spill results. Learn one, understand all three — they compose naturally.

UNIQUE vs Remove Duplicates Dialog — Live Formula vs One-Shot

Excel has had Remove Duplicates on the Data ribbon for years. UNIQUE didn't remove it — it made it obsolete for anything reactive. Here's when each wins.

AspectUNIQUE (formula)Remove Duplicates (dialog)
Reactivity Live — updates when source changes One-shot — re-run manually every time
Effect on source Non-destructive — source stays intact DESTRUCTIVE — deletes duplicate rows in place
Output location Spills to a new location — original preserved Modifies the original range
Setup Type one formula Select range, open dialog, pick columns, click OK
Multi-column pairs Direct — =UNIQUE(A2:B100) Available via checkboxes in the dialog
Exactly-once mode Built-in — =UNIQUE(A, FALSE, TRUE) Not available
Compatibility Excel 365 / 2021+ only Works in Excel 2007 onward

Rule of thumb: reactive workbook or need to preserve the source → UNIQUE. One-time cleanup of a static file → Remove Duplicates dialog is fine. Never use the dialog if you'll need the original data back.

Common Errors

SymptomCauseFix
#NAME? You're on Excel 2019 or earlier — UNIQUE doesn't exist. Or you typo'd the name. Upgrade to Excel 365, 2021, or 2024. On legacy Excel, use INDEX+MATCH+COUNTIF array formulas (Ctrl+Shift+Enter) or the Remove Duplicates dialog.
#SPILL! Something is blocking the spill zone — a value in a cell where the result needs to go. Clear the blocking cells. Hover the spill indicator — Excel highlights the block. Move the formula to a location with room to grow downward.
Returns extra blank cell at bottom The source range includes empty cells past your actual data. UNIQUE treats blank as a valid distinct value. Filter blanks first: =UNIQUE(FILTER(A2:A100, A2:A100<>"")). Or use a properly-sized range instead of whole columns.
Case-insensitive duplicates aren't removed UNIQUE treats "Excel" and "EXCEL" and "excel" as three different values by default. It's case-sensitive-ish (actually, distinguishes case). Normalize before: =UNIQUE(LOWER(A2:A100)). Or =UNIQUE(UPPER(A2:A100)). Case normalization must happen inside the argument.
Duplicates NOT removed even though values look identical Invisible differences — trailing spaces (VLOOKUP-style trap), CHAR(160) non-breaking spaces from web/PDF copy-paste. Wrap with TRIM and SUBSTITUTE: =UNIQUE(TRIM(SUBSTITUTE(A2:A100, CHAR(160), " "))). Fixes both invisible-space traps in one pass.
Order isn't alphabetical UNIQUE preserves first-occurrence order by design — not alphabetical. Wrap with SORT: =SORT(UNIQUE(A2:A100)). Add sort_index/sort_order arguments for descending or multi-column sort.

⚡ Practice on Real Sales Data

Every UNIQUE pattern — distinct values, multi-column pairs, exactly_once, UNIQUE+SORT combo — on 30 Q1 2026 orders with realistic customer distribution.

↓ Get Workbook

Related Functions

Compatibility

PlatformSupportedVersionNotes
Excel 2019 & earlier✗—Not available — #NAME? error
Excel 2021 / 2024✓PerpetualFull support
Microsoft 365✓CurrentFull support with all spill features
Excel Online✓CurrentFull support
Excel Mobile (iOS/Android)✓CurrentFull support
Google Sheets✓AllSame syntax, identical behavior
LibreOffice Calc✓7.4+Earlier versions: not available

How to Use UNIQUE — Step by Step

  1. Click an empty cell with room to spill downward — ideally the top of a blank column.
  2. Type =UNIQUE( — Excel auto-suggests as soon as you type "UNI".
  3. Select the source range (the column you want distinct values from), then close with ) and press Enter.
  4. The result spills into the cells below with a blue-dashed spill border. Only the top-left cell holds the formula; the rest are read-only.
  5. For alphabetical order, wrap with SORT: =SORT(UNIQUE(A2:A100)). Add -1 for descending: =SORT(UNIQUE(A2:A100), 1, -1).
  6. For multi-column pairs, pass a multi-column range: =UNIQUE(A2:B100) returns distinct row combinations. Use CHOOSE or HSTACK to bundle non-adjacent columns.
  7. For "exactly once" mode, add TRUE as the 3rd argument: =UNIQUE(A2:A100, FALSE, TRUE). Returns only values that appear once in the source.
  8. To count distinct values, wrap with COUNTA: =COUNTA(UNIQUE(A2:A100)). The one-line count-distinct formula.

Explore More Functions

⚡ One More Time — Grab the Workbook

Every UNIQUE pattern plus FILTER and SORT combos, on 30 realistic sales orders. Free, one file, six sheets.

↓ Download Free

Frequently Asked Questions

Why does UNIQUE return #NAME?

Your Excel version doesn't support UNIQUE. It was introduced in Microsoft 365 in 2020 and added to Excel 2021 perpetual. Excel 2019 and earlier always show #NAME? because the function doesn't exist.

Options: upgrade to Excel 365/2021+ (recommended), use the Remove Duplicates dialog for one-time cleanup, or use the legacy INDEX+MATCH+COUNTIF array formula. The legacy version works but is hard to read and modify.

What's the difference between UNIQUE and Remove Duplicates?

UNIQUE is a formula that returns distinct values to a new location — the source stays intact and the result updates when source changes. Remove Duplicates is a dialog that DELETES duplicate rows in place — destructive and one-shot.

Use UNIQUE for reactive workbooks (dashboards, dropdowns, live reports) and when you want to preserve the original data. Use Remove Duplicates only for one-time cleanup of static files where you'll never need the original.

How do I count distinct values?

Wrap UNIQUE with COUNTA: =COUNTA(UNIQUE(A2:A100)). Returns the count of distinct values. This is the one-line count-distinct formula that was impossible in legacy Excel without complex SUMPRODUCT+COUNTIF constructions.

For count-distinct with a criteria filter: =COUNTA(UNIQUE(FILTER(A2:A100, B2:B100="West"))). Count distinct customers in West region — one formula.

How do I get unique values from multiple columns?

If the columns are adjacent, pass the multi-column range: =UNIQUE(A2:B100) returns distinct row combinations of the two columns.

If columns aren't adjacent, use CHOOSE or HSTACK to bundle them: =UNIQUE(CHOOSE({1,2}, A2:A100, D2:D100)). HSTACK is cleaner but requires Excel 365: =UNIQUE(HSTACK(A2:A100, D2:D100)).

What does the exactly_once argument do?

The 3rd argument, when set to TRUE, returns only values that appear EXACTLY once in the source. Values appearing twice, three times, or more are excluded entirely.

Common uses: finding one-time customers (churn risk), identifying data-entry errors (misspellings that appear only once), spotting orphaned records, or generating "long tail" analysis lists. Set [by_col] to FALSE and [exactly_once] to TRUE: =UNIQUE(A2:A100, FALSE, TRUE).

How do I get unique across rows instead of columns (by_col TRUE)?

Set the 2nd argument to TRUE: =UNIQUE(A1:Z1, TRUE). This treats each COLUMN as a unit and returns distinct columns instead of distinct rows.

Rarely used because most data is laid out with columns as fields and rows as records. But if your data is transposed — dates across the top row, categories down the left — by_col=TRUE is the right mode. Test it once; you'll know when you need it.

Why is UNIQUE's order different from alphabetical?

UNIQUE preserves first-occurrence order by design. If "West" appears in row 5 and "North" first appears in row 8, UNIQUE returns them in the order they first appeared: West, North (not alphabetically).

For alphabetical order, wrap with SORT: =SORT(UNIQUE(A2:A100)). Add sort_index and sort_order arguments for descending or multi-key sort. The SORT+UNIQUE pattern is the standard for dropdown source lists.

Can UNIQUE handle case-sensitive matching?

UNIQUE distinguishes case by default — "Excel", "EXCEL", and "excel" are treated as three different values. This is often surprising because most other Excel functions (COUNTIF, VLOOKUP, MATCH) are case-insensitive.

To treat them as the same, normalize before UNIQUE: =UNIQUE(LOWER(A2:A100)) or =UNIQUE(UPPER(A2:A100)). The normalization must happen inside the argument so UNIQUE sees the same-case values.

What if my source range has blank cells?

UNIQUE treats blank as a valid distinct value — you'll see an empty row at the end. Filter it out: =UNIQUE(FILTER(A2:A100, A2:A100<>"")). Removes blanks before dedupe.

Better: size your source range correctly instead of using whole-column references (A:A). Overshoot causes both blank rows AND slower recalc on large datasets.

Can I filter and dedupe in one formula?

Yes — wrap FILTER with UNIQUE: =UNIQUE(FILTER(customer_col, region_col="West")). Filters first (only West customers), then dedupes the result.

Add SORT for the full three-function stack: =SORT(UNIQUE(FILTER(customer_col, region_col="West"))). Distinct West customers, alphabetical. This is the "dependent dropdown source" pattern — change the region cell → dropdown auto-updates.

Is UNIQUE the same as a pivot table's distinct values?

Similar but different. Pivot tables offer distinct-values via "Value Field Settings → Summarize by Distinct Count" — but that requires the Data Model (Power Pivot) in older Excel versions, and pivots must be manually refreshed.

UNIQUE is a formula — live, no refresh, no Data Model needed. For simple "distinct customer list" needs, UNIQUE wins. For rich groupings with multiple metrics and drill-down, pivots still win. They complement each other in serious dashboards.

Is UNIQUE available in Google Sheets?

Yes — Google Sheets has had UNIQUE since before Excel added it. Same name, same syntax, same behavior for the basic case. The [by_col] and [exactly_once] arguments work identically.

FILTER, SORT, and SEQUENCE also work identically across both platforms. If you learn Excel's UNIQUE, you already know Google Sheets' UNIQUE.

Every Dropdown Source, Every Distinct-Count Tile, One Formula

Excel Wizard writes UNIQUE patterns — SORT+UNIQUE dropdown sources, COUNTA distinct-counts, multi-column pair extraction, exactly_once churn-risk reports — from a plain-English prompt. Stop clicking Remove Duplicates. Start deduplicating live, right inside Excel.

Get Excel Wizard Add-in →