It's 4:47 PM on Friday. Your manager just asked for a consolidated vendor list by 5. You have three sheets: Vendors_Q1, Vendors_Q2, and Archived_Vendors. All contain 'Acme Corp', 'Zephyr Logistics', and 'NexaTech Inc' — but spelled slightly differently, with extra spaces, mixed case, and one entry has 'NexaTech Inc.' with a period. You type =UNIQUE(A2:A100). It returns 97 rows instead of 3.
The Problem
UNIQUE doesn’t clean data. It only filters exact matches — byte-for-byte identical values. Case sensitivity? Ignored. Trailing spaces? Preserved. Numbers stored as text? Treated as different from real numbers. That’s why your 'cleaned' list still has 'acme corp', 'ACME CORP ', and 'acme corp' (with a non-breaking space) — all counted as distinct.
Here’s what your raw vendor list actually looks like in Sheet1!A2:A12:
| A2:A12 | Raw Entry |
|---|---|
| A2 | Acme Corp |
| A3 | acme corp |
| A4 | Acme Corp |
| A5 | Zephyr Logistics |
| A6 | zephyr logistics |
| A7 | NexaTech Inc. |
| A8 | NexaTech Inc |
| A9 | 12345 |
| A10 | "12345" |
| A11 | 2024-03-15 |
| A12 | "2024-03-15" |
Run =UNIQUE(A2:A12) in B2. You’ll get all 12 entries — not 5.
This isn’t broken behavior. It’s by design. UNIQUE sees "12345" (text) and 12345 (number) as fundamentally different. Same for "2024-03-15" vs. the real date serial 45366.
The Solution
Do this — in order. No skipping steps.
- Pre-clean your source range. In C2, paste this formula and drag down to C12:
=TRIM(UPPER(SUBSTITUTE(A2,CHAR(160)," ")))
This removes non-breaking spaces (common in web-pasted data), trims whitespace, and standardizes case. - Convert numbers-as-text to real numbers where needed. In D2, use:
=IF(ISNUMBER(A2),A2,IF(ISNUMBER(VALUE(A2)),VALUE(A2),C2))
Then copy D2:D12 and Paste Values over A2:A12 (Alt+E+S+V). - Now apply UNIQUE — on the cleaned column. In E2, enter:
=UNIQUE(C2:C12)
Press Enter. You’ll get exactly 5 rows:ACME CORP,ZEPHYR LOGISTICS,NEXATECH INC,12345,2024-03-15.
That’s it. The cleaned result in E2:E6:
| E2:E6 | Cleaned Unique Value |
|---|---|
| E2 | ACME CORP |
| E3 | ZEPHYR LOGISTICS |
| E4 | NEXATECH INC |
| E5 | 12345 |
| E6 | 2024-03-15 |
Surprising tip: UNIQUE ignores empty cells *only if they’re truly blank*. A cell with ="" (formula returning empty string) is NOT ignored. Test with =ISBLANK(A2). If it returns FALSE, UNIQUE counts it. Delete those cells — don’t just hide them.
Going Further
You can nest UNIQUE inside other functions — but avoid doing it blindly.
Extract unique names from two columns at once:
In F2, try:=UNIQUE(CHOOSE({1,2},B2:B10,C2:C10))
This stacks columns B and C vertically, then deduplicates across both. Works only if both columns have same data type (e.g., both text).
Get unique values that appear *more than once*:
Use COUNTIF + FILTER:=UNIQUE(FILTER(A2:A12,COUNTIF(A2:A12,A2:A12)>1))
This returns only values that are duplicated elsewhere — useful for spotting errors.
Sort unique results alphabetically (ascending):=SORT(UNIQUE(C2:C12))
Count how many unique entries exist:=ROWS(UNIQUE(C2:C12))
Not COUNTA. ROWS gives you the actual count of returned array rows.
And yes — UNIQUE works with dynamic arrays. If your source range is a spilled array (e.g., =FILTER(...) in G2), just reference G2# and UNIQUE will auto-expand.
When NOT to Use This
UNIQUE fails silently in these cases. Walk away — use Power Query or manual dedupe instead.
- Your data contains merged cells. UNIQUE throws
#VALUE!— no warning, no explanation. Unmerge first. - You need to preserve original formatting (bold, colors, fonts). UNIQUE outputs plain values only. Formatting is gone. Always keep a backup sheet.
- You’re comparing dates from different time zones or imported from CSV with inconsistent separators.
"03/15/2024"vs."15-03-2024"won’t unify. Convert to proper Excel dates (=DATEVALUE()) before UNIQUE. - You have >1 million rows. UNIQUE slows hard past 250k entries. For large datasets, use Data > Remove Duplicates (Alt+A+M) — it’s faster and handles mixed data types more reliably.
Also: UNIQUE does not support wildcards or partial matches. If you want 'all vendors starting with “Acme”', use FILTER + LEFT, not UNIQUE.
Keyboard Shortcuts
These save real time when building UNIQUE workflows:
| Action | Shortcut | Notes |
|---|---|---|
| Paste Values only | Alt+E+S+V |
Critical after cleaning formulas — avoids dragging live references |
| Select entire column | Ctrl+Space |
Then Ctrl+C, go to new sheet, Alt+E+S+V |
| Open Name Manager | Ctrl+F3 |
Name your cleaned range (e.g., clean_vendors) — makes UNIQUE formulas readable |
| Toggle formula view | Ctrl+` (grave accent) |
See all formulas at once — essential for debugging UNIQUE chains |
