Before Excel 365, extracting a list of distinct values from a column required complex array formulas combining INDEX, MATCH, and COUNTIF — formulas that only experienced users could write or maintain. The UNIQUE function changed all of that. Type the formula once, press Enter, and Excel automatically populates a clean list of distinct values, removing every duplicate and updating the list live whenever your source data changes. This guide covers the syntax, how the dynamic spill behaviour works, and six practical examples you can use immediately.
UNIQUE Function Syntax
UNIQUE takes one required argument and two optional ones that control the direction of comparison and whether to return values appearing exactly once:
| Argument | Required? | What it means |
|---|---|---|
| array | Required | The range or array to extract unique values from. Can be a single column, multiple columns, or a horizontal row. Works with text, numbers, dates, and any other data type. |
| by_col | Optional | FALSE (default) — compares rows against each other, extracting unique rows vertically. TRUE — compares columns against each other, for horizontal data. Almost all real-world uses keep this at the default FALSE. |
| exactly_once | Optional | FALSE (default) — returns all distinct values, including those that appear multiple times in the source. TRUE — returns only values that appear exactly once. Values that repeat at all are excluded from the output. |
#SPILL! error. Reference the spill range in other formulas using the hash operator: D2#.
Example 1: Basic Distinct List from a Column
The most common use — extract every distinct value from a column, removing all duplicates in a single formula. The result spills automatically and updates whenever the source changes.
exactly_once=TRUE to get only values appearing a single time. See Example 4 for a full comparison.
Example 2: SORT + UNIQUE for an Alphabetical List
UNIQUE returns values in the order they first appear in the source — not alphabetically. Wrap SORT around UNIQUE to get a sorted distinct list in a single formula. This combination is one of the most useful patterns in Excel 365.
=SORT(array, sort_index, sort_order). Use 1 for ascending (default) and -1 for descending as the third argument. When wrapping UNIQUE, sort_index is always 1 since UNIQUE returns a single column. Both functions together still produce a single spill range that updates automatically.
Example 3: UNIQUE with FILTER — Distinct Values from a Subset
Combining UNIQUE with FILTER is where this function becomes genuinely powerful for data analysis. Filter your data to a specific subset first, then extract distinct values from that subset — all in one formula with no helper columns.
Example 4: exactly_once = TRUE — Find Values That Appear Once
Setting the third argument to TRUE changes the behaviour entirely. Instead of returning all distinct values, UNIQUE returns only values that appear exactly once — every repeated value is excluded. Useful for finding first-time customers, one-off transactions, or single-occurrence entries in audit data.
exactly_once you must provide by_col first. Since you almost always want vertical comparison, use FALSE as a placeholder: =UNIQUE(A2:A10, FALSE, TRUE). You cannot skip to the third argument.
Example 5: Unique Rows Across Multiple Columns
Pass a multi-column range to UNIQUE and it compares entire rows — returning only rows where the full combination of values is unique. Ideal for finding unique name pairs, order combinations, or any multi-field key.
Example 6: Count Distinct Values and Build a Dynamic Dropdown
Two of the most practical applications in real workbooks — counting distinct values and feeding the spill result into a data validation dropdown that updates automatically as your data changes.
$D$2# spill range reference expands and contracts automatically — your dropdown always reflects the current distinct values without any manual maintenance.
#SPILL!. Place the UNIQUE formula in a regular cell range outside the table, even if the source data is a table column: =SORT(UNIQUE(Table1[Department])).
Troubleshooting UNIQUE Errors
#SPILL! error
Something is blocking the spill range — a value, a space character, or merged cells in a cell below the formula. Click the formula cell and look for the blue spill border outline. Clear all cells in the expected spill area, including cells that appear blank but may contain a space.
#CALC! error when combined with FILTER
No rows matched the FILTER criteria, so FILTER returned an empty array and UNIQUE had nothing to process. Add a fallback as the third argument of FILTER: =UNIQUE(FILTER(A:A, B:B="North", "No results")).
#NAME? error
UNIQUE is not available in your Excel version. It requires Excel 365, 2024, or 2021. For older versions, use Data > Advanced Filter > Copy to another location and check Unique records only as an alternative.
Result shows zeros where blanks should appear
UNIQUE treats blank cells as a value and includes them in the output as 0. Pre-filter with FILTER to exclude blanks: =UNIQUE(FILTER(A2:A100, A2:A100<>"")).
Frequently Asked Questions
-
What does the UNIQUE function do in Excel?UNIQUE extracts a list of distinct values from a range or array, automatically removing duplicates. The result is a dynamic array that spills into as many cells as needed and updates automatically when the source data changes. It works with text, numbers, dates, and any other data type, and requires no special key presses — just Enter.
-
What is the difference between distinct and exactly once in UNIQUE?Distinct values (default) means every value that appears in the list, included once even if it appears multiple times. Exactly once (third argument = TRUE) means values that appear a single time in the source — if a value appears two or more times it is completely excluded from the result. Use distinct for deduplication, and exactly_once for identifying one-off entries.
-
Which Excel versions support UNIQUE?UNIQUE is available in Microsoft 365, Excel 2024, and Excel 2021. It does not work in Excel 2019, 2016, or earlier because those versions do not support dynamic arrays. For older versions, use the Advanced Filter (Data tab) to extract unique values to another location, or use Remove Duplicates on a copy of your data.
-
How do I sort the UNIQUE results alphabetically?Wrap UNIQUE inside SORT: =SORT(UNIQUE(A2:A100)). SORT arranges results ascending by default. Add -1 as the third argument for descending order: =SORT(UNIQUE(A2:A100), 1, -1). The combined result is still a single dynamic spill range that updates automatically when source data changes.
-
How do I use UNIQUE to create a dynamic dropdown list?Place =SORT(UNIQUE(B2:B100)) in a spare cell outside any Excel Table — for example D2. Then select your dropdown cell, go to Data > Data Validation > List, and enter =$D$2# as the source. The # spill range operator references the full spill automatically, so the dropdown grows or shrinks as your data changes without any manual updates.
-
Why does UNIQUE return a #SPILL! error inside an Excel Table?Dynamic array functions including UNIQUE cannot spill inside a structured Excel Table. Place the UNIQUE formula in a regular cell range outside the table. You can still reference a table column as the source array: =UNIQUE(Table1[Department]) — just ensure the formula cell itself is outside the table boundaries.