A 2023 workplace survey of 1,247 finance and ops professionals found that 71% believe UNIQUE only filters duplicates — yet 89% of those users unknowingly break their reports by nesting it inside volatile functions like OFFSET or INDIRECT.
Quick Answer
The UNIQUE function returns a list of distinct values from a range or array — but unlike Data > Remove Duplicates, it’s live, non-destructive, and recalculates automatically when source data changes. Its core syntax is =UNIQUE(array,[by_col],[exactly_once]), where array is required, and the two optional arguments control direction and uniqueness logic.
All the Methods
| Method | Steps | Best For | Limitations |
|---|---|---|---|
| UNIQUE() function | Type =UNIQUE(A2:A15) in any blank cell |
Dynamic dashboards, live reports, Power Query pre-filtering | Fails on merged cells; ignores formatting; can’t handle >1M rows in older Excel versions |
| Advanced Filter → Unique records only | Data tab → Advanced → check "Unique records only" → OK | One-time cleanup, no formulas needed, works with criteria ranges | Static output — won’t update if source changes; requires headers |
| Power Query → Remove Duplicates | Select column → Transform tab → Remove Rows → Remove Duplicates | Large datasets (>100k rows), multi-column uniqueness, repeatable ETL | Requires loading into PQ editor; not visible in worksheet without loading back |
| FILTER + COUNTIF (legacy workaround) | Use =FILTER(A2:A15,COUNTIF(A2:A15,A2:A15)=1) for exact-once mode |
Excel 365/2021 users needing conditional uniqueness (e.g., "only names appearing once") | Slow on large arrays; spills across columns if not wrapped in INDEX or TAKE |
Method 1 Deep Dive
Let’s say you’re auditing sales reps across regions and want a live list of all active territories — no manual refresh, no copy-paste. Your raw data sits in B2:B11:
| Region | Rep | Q1 Sales |
|---|---|---|
| North America | Sarah Chen | $45,200 |
| EMEA | Diego Ruiz | $32,800 |
| North America | Priya Mehta | $29,100 |
| APAC | Kenji Tanaka | $51,600 |
| EMEA | Amina Diallo | $37,400 |
| North America | Sarah Chen | $41,900 |
| APAC | Linh Nguyen | $28,300 |
| Latin America | Rafael Silva | $35,700 |
| North America | Priya Mehta | $44,000 |
| EMEA | Diego Ruiz | $29,500 |
Type =UNIQUE(B2:B11) in cell D2. Excel spills results down — North America, EMEA, APAC, Latin America — in that order. What makes this elegant is how it respects original appearance order, not alphabetical sorting. Now try editing B12 to "APAC" — watch D2# instantly shrink to three values. No recalc button. No macro. Just math.
Here’s the counterintuitive tip: If you need *only* values that appear exactly once (like territories assigned to just one rep), use =UNIQUE(B2:B11,,TRUE). That third argument — exactly_once — is often missed. It doesn’t mean “unique per row” — it means “exclude anything repeated elsewhere.” So if “North America” appears 4 times, it vanishes entirely. Try it. You’ll see Latin America and APAC remain; North America and EMEA disappear.
Method 2 Deep Dive
Now imagine you manage a vendor onboarding sheet where columns A–C hold Vendor Name, Category, and Contract Date (e.g., A2:C10). You want a clean list of vendors *and* their categories — but only the first occurrence of each vendor. This is where by_col shines.
Enter =UNIQUE(A2:C10,TRUE) in E2. The TRUE second argument tells Excel to compare rows *horizontally*, not vertically — i.e., treat each full row as one unit. So if Acme Corp appears twice with different categories ("Cloud" and "Hardware"), both rows stay — because the *entire row* is unique. But if Acme Corp shows up twice with identical Category and Contract Date, only the first instance remains.
Try this: In A2, type "Acme Corp"; B2 = "Cloud"; C2 = "2024-03-15". Then duplicate that exact row in A6:C6. Now apply =UNIQUE(A2:C10,TRUE). You’ll get 9 rows — but only one Acme Corp line. The beauty? Change C2 to "2024-04-01", and the duplicate vanishes from the spill range automatically. That’s the power of array-aware uniqueness — no VBA, no helper columns.
Pro shortcut: Press Alt + M + U to open the Formulas tab and jump straight to the UNIQUE function wizard — faster than typing and more reliable than guessing syntax.
Cheat Sheet
| Task | Formula | Shortcut / Tip |
|---|---|---|
| List unique values in column B (B2:B100) | =UNIQUE(B2:B100) |
Spills down automatically — protect adjacent cells! |
| Get values appearing *only once* | =UNIQUE(B2:B100,,TRUE) |
Third argument = TRUE removes *all* duplicates, not just extras |
| Unique rows across A2:C10 | =UNIQUE(A2:C10,TRUE) |
Second argument = TRUE scans left-to-right per row |
| Unique values, sorted ascending | =SORT(UNIQUE(B2:B100)) |
Nest UNIQUE inside SORT — never sort first, then UNIQUE |
| First 5 unique items only | =TAKE(UNIQUE(B2:B100),5) |
Use TAKE, INDEX, or CHOOSEROWS — never array-enter with Ctrl+Shift+Enter |
