UNIQUE
Return distinct values from a range in one formula. What used to require Remove Duplicates (destructive), array formulas, or Power Query is now a single function — and it updates dynamically as your data changes.
Returns distinct values from an array. Preserves original data. Updates automatically when source changes. Foundation of dropdown lists, deduplicated reports, and dashboard filters.
What UNIQUE does
UNIQUE returns each distinct value from a range exactly once. Given a column with 500 sales records across 8 regions, UNIQUE returns those 8 region names — no duplicates, no manual work, no destructive editing. When a new region appears in the source data, UNIQUE picks it up automatically. When a region drops out entirely, UNIQUE removes it from the output.
This transforms how you build dropdown menus, category filters, and summary dashboards. Instead of maintaining a separate list of "all regions", you point UNIQUE at your actual data and let it derive the list. The dropdown always reflects reality. The dashboard filter always shows current options. Fewer moving parts, fewer bugs, fewer maintenance headaches.
💡 The third argument — the underrated feature
Setting exactly_once to TRUE changes UNIQUE's behavior completely. Instead of returning distinct values, it returns only values that appear exactly once — everything with duplicates disappears. Perfect for finding items with no repeats: one-time customers, unique errors, non-repeating IDs.
Syntax breakdown
The range or array to extract distinct values from. Can be one column, one row, or multiple columns (in which case UNIQUE returns distinct combinations of column values as full rows).
FALSE (default) treats each row as a unit — returns distinct rows. TRUE treats each column as a unit — returns distinct columns. Rarely used; most data is analyzed by rows.
FALSE (default) returns each distinct value once (standard dedupe). TRUE returns only values that appear exactly once in the source — items with duplicates are excluded entirely.
5 real-world examples
Get all unique customer names
Customer names in A2:A1000 with lots of repeats. Return the distinct list.
Perfect for building dropdown source lists, dashboard filter options, or summary reports.
Distinct region + product combinations
Data in A2:C1000. Return distinct combinations of region and product (columns A and B together).
Powerful for building lookup tables from raw transaction data.
Distinct customers, alphabetized
Combine UNIQUE with SORT for a clean deduplicated + sorted output.
The gold-standard pattern for dropdown source lists — dedupe first, then sort.
One-time customers only
Return only customers who appear exactly once — no repeat buyers.
Great for anomaly detection, one-off analysis, or identifying customers you should follow up with.
Distinct customers in West region only
Filter first, then dedupe. Get distinct customers only for West-region orders.
The trio pattern — FILTER narrows, UNIQUE dedupes, and SORT can wrap the whole thing to also sort.
Common errors and how to fix them
Cells needed for spill are blocked
UNIQUE wants to spill into 50 cells below, but one is occupied. Excel refuses to overwrite.
Trailing spaces or case differences
UNIQUE sees "Widget" and "Widget " (with trailing space) as different values. Same for "APPLE" and "apple" — though case usually doesn't matter, invisible characters do.
Blank cells appearing in the unique list
If your source range contains blanks, UNIQUE treats blank as one of the distinct values and includes it in the output.
Empty source combined with FILTER
Your FILTER returned nothing, so UNIQUE has nothing to dedupe.
See our Dynamic Array Errors guide for more.
UNIQUE vs alternatives
Version compatibility
Related functions
📥 Download the practice workbook
All 5 examples plus a dashboard template with UNIQUE-powered dropdowns and dynamic filters.
Frequently asked questions
How do I count how many unique values there are?
Wrap UNIQUE in ROWS: =ROWS(UNIQUE(A2:A1000)). Returns the count of distinct values. If your data can be blank, wrap with FILTER first: =ROWS(UNIQUE(FILTER(A2:A1000, A2:A1000<>""))).
Does UNIQUE care about case?
No — "Apple" and "APPLE" are treated as the same value. This is usually helpful. For case-sensitive uniqueness, you'd need a workaround using EXACT() and array formulas.
What's the difference between UNIQUE and Remove Duplicates?
UNIQUE is a formula that returns distinct values in a new location while preserving source data. Remove Duplicates is a menu action that deletes duplicate rows from your data (destructive). UNIQUE is safer, dynamic, and non-destructive.
Can I get unique values across multiple columns?
Yes. Pass a multi-column range: =UNIQUE(A2:C1000) returns distinct row combinations across all three columns. To combine values from separate ranges first, use VSTACK: =UNIQUE(VSTACK(A2:A100, B2:B100)).
How do I get unique values sorted alphabetically?
Nest inside SORT: =SORT(UNIQUE(A2:A1000)). This is the canonical pattern for dropdown source lists — sorted, deduplicated, live-updating.
Can UNIQUE be used as the source for Data Validation dropdowns?
Yes — and this is one of its best use cases. Put =SORT(UNIQUE(A2:A1000)) in a cell like Z2. In Data Validation, set the source to =Z2# (the spill reference). The dropdown auto-updates as source data changes.
Is UNIQUE slow on big datasets?
UNIQUE handles tens of thousands of rows fine. Past 100K rows, you may notice recalculation delays. For very large datasets, Power Query is faster and more scalable.
Deduped, sorted, live-updating lists.
Describe what you want — "unique customers who bought from West region sorted alphabetically" — and get the exact UNIQUE/SORT/FILTER combo.
Get the Add-in →