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.
#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.
Remove Duplicates dialog or complex COUNTIF/INDEX formulas
One formula, spills every distinct value automatically
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.
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.
Syntax
UNIQUE takes 1 required argument and 2 optional arguments that unlock powerful modes.
| Argument | Type | Description |
|---|---|---|
| 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.
The range to dedupe. Single column → distinct values. Multi-column → distinct row combinations (Region + Category pairs, First+Last name pairs, etc.).
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.
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
Distinct regions from the order log
The simplest UNIQUE. One column in, distinct values out. Order matches first occurrence in the source (not alphabetical).
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)).
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.
| 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.
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.
| Region | Category |
|---|---|
| North | Electronics |
| West | Furniture |
| East | Furniture |
| South | Software |
| East | Electronics |
| West | Office Supplies |
| North | Office Supplies |
| South | Furniture |
| … | … |
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.
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.
| 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.
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.
| 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
Customers → 15 distinct (some repeat)
Products → 12 distinct
Regions × Categories → 16 pairs
One-time customers → 0 (all repeat)
Formula & Spill Count
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.
| Aspect | UNIQUE (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
| Symptom | Cause | Fix |
|---|---|---|
| #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.
Related Functions
=UNIQUE(FILTER(...)) for deduplicated matches.=COUNTA(UNIQUE(range)). The one-line count-distinct formula.=COUNTIF(source, UNIQUE(source)#).=UNIQUE(CHOOSE({1,2}, ColA, ColB)).=UNIQUE(HSTACK(ColA, ColB)). Excel 365 only.=UNIQUE(TRIM(A2:A100)). Fixes the invisible-space trap.=UNIQUE(LOWER(A2:A100)). Merges "Excel" and "EXCEL" as one.Compatibility
| Platform | Supported | Version | Notes |
|---|---|---|---|
| Excel 2019 & earlier | ✗ | — | Not available — #NAME? error |
| Excel 2021 / 2024 | ✓ | Perpetual | Full support |
| Microsoft 365 | ✓ | Current | Full support with all spill features |
| Excel Online | ✓ | Current | Full support |
| Excel Mobile (iOS/Android) | ✓ | Current | Full support |
| Google Sheets | ✓ | All | Same syntax, identical behavior |
| LibreOffice Calc | ✓ | 7.4+ | Earlier versions: not available |
How to Use UNIQUE — Step by Step
- Click an empty cell with room to spill downward — ideally the top of a blank column.
- Type
=UNIQUE(— Excel auto-suggests as soon as you type "UNI". - Select the source range (the column you want distinct values from), then close with
)and press Enter. - 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.
- For alphabetical order, wrap with SORT:
=SORT(UNIQUE(A2:A100)). Add-1for descending:=SORT(UNIQUE(A2:A100), 1, -1). - 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. - For "exactly once" mode, add TRUE as the 3rd argument:
=UNIQUE(A2:A100, FALSE, TRUE). Returns only values that appear once in the source. - 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.
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.
Templates That Use UNIQUE
Customer Master List
Distinct customers from every order/invoice/ticket — one UNIQUE formula, no manual maintenance as new customers appear across systems.
USES UNIQUEData Validation Dropdown Source
SORT+UNIQUE pattern feeding a spill-referenced Data Validation range. Add a new value to source → dropdown updates instantly.
USES UNIQUEDuplicate Detection Tool
Uses UNIQUE with exactly_once=TRUE inverted with FILTER to find records appearing MORE than once — the "here are your dupes" report.
USES UNIQUEProduct Catalog Deduplicator
Merges CSV imports from multiple systems by SKU. LOWER+TRIM normalization inside UNIQUE removes case-and-space variants automatically.
USES UNIQUEDistinct-Count Dashboard
COUNTA(UNIQUE(FILTER(...))) tiles for distinct customers per region, distinct products per category, distinct dates per month — all live.
USES UNIQUEOne-Time Buyer Report
exactly_once=TRUE surfaces customers who ordered exactly once — the churn-risk list ready for re-engagement outreach campaigns.
USES UNIQUECategory Reference Sheet
Multi-column UNIQUE builds a distinct Region×Category matrix as source data grows. Auto-updates without pivot table refresh.
USES UNIQUEClient Contact Consolidator
Merges contact records from CRM + email + call log into a single deduplicated master list via UNIQUE with normalized inputs.
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 →