A 2023 workplace survey of 1,247 finance and ops professionals found that 58% thought UNIQUE was just a 'fancy Remove Duplicates button' — until their dashboard broke after a colleague added a new row.
The Problem
You get a weekly sales export from your CRM. It’s messy: duplicate reps, repeated customer names across regions, and blank rows slipped in during copy-paste. You need a clean list of distinct account managers to assign quotas — but every time you use Data > Remove Duplicates, you lose formatting, break formulas downstream, and have to re-sort manually.
Here’s what your raw data looks like in A1:C11:
| Sales Rep | Region | Q1 Revenue |
|---|---|---|
| Sarah Chen | APAC | $45,200 |
| Diego Mora | EMEA | $32,800 |
| Sarah Chen | APAC | $45,200 |
| Aisha Patel | NA | $51,100 |
| Diego Mora | EMEA | $32,800 |
| Liam O’Reilly | NA | $29,600 |
| Sarah Chen | APAC | $45,200 |
| Aisha Patel | NA | $51,100 |
| Zara Kim | APAC | $38,900 |
| (blank) | (blank) | (blank) |
Notice how Sarah Chen appears three times? Diego and Aisha twice? And that final blank row? That’s not just noise — it’s the reason your COUNTA() formula miscounts later.
The Solution
UNIQUE isn’t just for deduping. It’s a dynamic array function — meaning it spills results automatically into adjacent cells. No more copy-pasting outputs or resizing ranges.
- In cell E1, type
=UNIQUE(A2:A11). Press Enter. It spills 5 names down E1:E5 — no dragging needed. - But wait — what if you want full rows, not just names? Try
=UNIQUE(A2:C11)in cell G1. It returns all unique combinations of Rep + Region + Revenue — so Sarah Chen appears once, but only with her exact Q1 revenue value. - To ignore blanks (critical!), wrap it:
=UNIQUE(FILTER(A2:A11,A2:A11<>""),TRUE)in cell I1. The second argumentTRUEtells Excel to compare by row — not column — which is the default behavior most people miss.
Here’s what spills into I1:I5:
| Unique Reps (No Blanks) |
|---|
| Sarah Chen |
| Diego Mora |
| Aisha Patel |
| Liam O’Reilly |
| Zara Kim |
That last one? It updates live when you add a new rep in A12. No manual refresh. No broken links. Just pure, silent magic.
Going Further
You can combine UNIQUE with other dynamic functions — and it’s where things get unexpectedly powerful.
Need the top 3 reps by revenue? Use this in K1:=TAKE(SORT(UNIQUE(A2:C11),3,-1),3)
This pulls unique rows, sorts them descending by column 3 (Q1 Revenue), then takes only the first 3 rows.
Want to count how many times each rep appears? Pair it with COUNTIFS:=COUNTIFS(A2:A11,E2#) in cell F2. That # symbol? That’s Excel’s spill operator — it means “apply this formula to every cell in the spilled range starting at E2.”
Here’s the counterintuitive part: UNIQUE ignores case by default — but it does NOT ignore leading/trailing spaces. So "Sarah Chen " (with a trailing space) and "Sarah Chen" are treated as different values. Fix it with TRIM: =UNIQUE(TRIM(A2:A11)).
When NOT to Use This
UNIQUE fails quietly in three situations — and you won’t get an error, just wrong output.
- Blank cells in your source range: As shown earlier, UNIQUE treats empty strings (
"") and truly blank cells differently. If your data has mixed blanks (some cells contain"", others are genuinely empty), UNIQUE may return duplicates. Always pre-filter or useFILTER()first. - Numbers stored as text: If column C contains revenue formatted as text (e.g., "$45,200" instead of
45200), UNIQUE sees "$45,200" and "$32,800" as strings — fine — but if some entries are numbers and others are text, they’ll both appear as distinct values. Check withISTEXT(C2)before deduping. - Excel versions older than 365 or 2021: UNIQUE simply doesn’t exist in Excel 2019 or earlier. If you share workbooks with legacy users, wrap it in IFERROR and fall back to Advanced Filter or Power Query — or better yet, use a helper column with
=IF(COUNTIF($A$2:A2,A2)=1,A2,"")and filter blanks.
Also — never use UNIQUE on volatile ranges like entire columns (A:A). It will slow your workbook to a crawl. Stick to defined ranges like A2:A1000.
Keyboard Shortcuts
These shortcuts save real time when building and troubleshooting UNIQUE formulas:
| Action | Shortcut | Notes |
|---|---|---|
| Insert function dialog | Shift+F3 | Type "UNIQUE" to jump straight to its help |
| Evaluate formula step-by-step | Alt+M+V | Critical for spotting why UNIQUE spills fewer rows than expected |
| Select entire spill range | Ctrl+Shift+Down Arrow | Start from top-left cell of spill (e.g., E1) — selects all spilled cells |
| Clear spill range (and formula) | Ctrl+- → Delete Sheet Rows | Don’t just hit Delete — that leaves #SPILL! errors behind |
