UNIQUE Function in Excel and Google Sheets
Purpose
Return a list of distinct values from a range, removing duplicates, as a dynamic spill result
Return value
A dynamic array of unique values that spills into adjacent empty cells and updates when the source data changes
UNIQUE is the formula-based Remove Duplicates that every spreadsheet analyst has wanted for years. Before it existed, getting a list of unique values meant either the Data > Remove Duplicates tool (which modifies the data in place) or a SUMPRODUCT(1/COUNTIF) workaround that broke on blank cells. UNIQUE returns a live deduplicated list that sits alongside the source data and updates automatically whenever the source changes. I use it constantly to build dynamic dropdown lists from columns that grow over time, to create unique product or customer lists for reporting, and as the first step in a SORT(UNIQUE(...)) chain that gives a sorted distinct list in one formula. The third argument exactly_once adds another layer of utility: pass TRUE and UNIQUE returns only the values that appear a single time, making it useful for finding one-off entries or detecting items with no duplicates. UNIQUE is available in Excel 365, Excel 2021, and all modern Google Sheets.
Syntax
✓ Excel 365 / 2021
✓ Google Sheets
= Same syntax
=UNIQUE(array, [by_col], [exactly_once])
Arguments
| Argument | Required | Description |
|---|---|---|
| array | Required | The range or array to deduplicate. Can be a single column, a single row, or a multi-column range where entire row uniqueness is evaluated. |
| by_col | Optional | FALSE (default) to return unique rows, TRUE to return unique columns. Sorting rows (the default) is the standard use case. |
| exactly_once | Optional | FALSE (default) returns one instance of every distinct value. TRUE returns only values that appear exactly once -- values appearing more than once are excluded entirely. |
How it works
UNIQUE scans the array argument and returns one instance of each distinct value in the order they first appear, spilling results into adjacent empty cells. The spill range expands or contracts as the number of unique values changes. Two entries are considered duplicates only when all compared columns match exactly -- for a single-column range this is straightforward; for multi-column ranges entire rows must be identical to be considered duplicates. Text matching is case-insensitive. Wrap UNIQUE in SORT to get a sorted unique list: =SORT(UNIQUE(A2:A100)). Wrap in COUNTA to count distinct values: =COUNTA(UNIQUE(A2:A100)). Use UNIQUE as the source range for a data validation dropdown to give users a live deduplicated list that updates automatically. For Excel 2019 and earlier where UNIQUE does not exist, the count unique values formula using SUMPRODUCT is the standard workaround.
Examples
1
Extract a unique list of regions from a sales column that contains many repeated region names.
| A | B | |
|---|---|---|
| 1 | Rep | Region |
| 2 | Alice | North |
| 3 | Bob | South |
| 4 | Carol | East |
| 5 | Dan | North |
| 6 | Ella | South |
| 7 | Finn | North |
| 8 | Gia | East |
| 9 | Hugo | North |
| 10 | Iris | South |
| 11 | Jo | East |
Row 2: North
— The three distinct regions are North, South, and East -- in the order they first appear in the column. UNIQUE spills all three into the formula cell and the two cells below it.
North appears 4 times, South 3 times, East 3 times in the source column. UNIQUE returns each once. The result is in first-appearance order, not alphabetical -- wrap in SORT to get alphabetical: =SORT(UNIQUE(B2:B11)).
2
Use the exactly_once argument to find products that appear only once in an order log -- products with no repeat orders.
| A | |
|---|---|
| 1 | Product |
| 2 | Laptop |
| 3 | Mouse |
| 4 | Monitor |
| 5 | Laptop |
| 6 | Keyboard |
| 7 | Mouse |
| 8 | Webcam |
| 9 | Monitor |
| 10 | Laptop |
| 11 | Cable |
Row 2: Keyboard
— Three products appear exactly once: Keyboard, Webcam, and Cable. Laptop appears 3 times, Mouse 2 times, Monitor 2 times -- all are excluded because they appear more than once.
The double comma ,, skips the by_col argument (leaving it as the default FALSE) to reach the exactly_once argument. This pattern -- supplying a later optional argument while skipping an earlier one -- is standard in Excel and Google Sheets.
3
Count the number of distinct customers in an order column by wrapping UNIQUE in COUNTA.
| A | |
|---|---|
| 1 | Customer |
| 2 | Alice |
| 3 | Bob |
| 4 | Carol |
| 5 | Alice |
| 6 | Dan |
| 7 | Bob |
| 8 | Eve |
| 9 | Carol |
| 10 | Alice |
| 11 | Dan |
| 12 | Distinct count: =COUNTA(UNIQUE(A2:A11)) |
Row 2: 5
— There are 5 distinct customers: Alice, Bob, Carol, Dan, and Eve. UNIQUE extracts the 5 unique names and COUNTA counts them.
=COUNTA(UNIQUE(range)) is the cleanest way to count distinct values in modern Excel and Google Sheets. In older Excel without UNIQUE, use the count unique values formula: =SUMPRODUCT(1/COUNTIF(A2:A11,A2:A11)) as the equivalent.
Common use cases
1. Build a dynamic dropdown list from a column that grows over time, always showing only distinct values
=UNIQUE(A2:A1000)
2. Create a sorted alphabetical list of unique category names for a report header
=SORT(UNIQUE(B2:B500))
3. Find entries that appear only once in a dataset using the exactly_once argument
=UNIQUE(A2:A100,,TRUE)
4. Count how many distinct values exist in a column using COUNTA wrapped around UNIQUE
=COUNTA(UNIQUE(A2:A100))
5. Deduplicate a multi-column range to find unique row combinations
=UNIQUE(A2:C100)
Common errors
#SPILL! error
The cells below the UNIQUE formula are not empty. UNIQUE cannot spill its results into occupied cells.
Fix: Clear the cells in the expected spill range. UNIQUE needs as many blank cells below the formula as there are unique values in the source range.
#CALC! error
The array argument is empty or contains only blank cells, giving UNIQUE nothing to return.
Fix: Check that the range contains data. If the source column may sometimes be entirely empty, wrap in IFERROR: =IFERROR(UNIQUE(A2:A100),"No data").
#NAME? error on the UNIQUE function
UNIQUE is not available in Excel 2019 or earlier.
Fix: Use Excel 365 or Excel 2021. For older Excel, use Data > Remove Duplicates to produce a static unique list, or use the count unique values SUMPRODUCT formula.
Tips and variations
Wrap in SORT for a sorted unique list in one formula
=SORT(UNIQUE(A2:A100)) is the cleanest way to produce an alphabetically sorted deduplicated list. The two functions compose directly because UNIQUE returns an array that SORT accepts as its input.
=SORT(UNIQUE(A2:A100))
Use as a dynamic data validation source
Point a data validation dropdown list at a UNIQUE formula spill range -- as new values are added to the source column, the dropdown updates automatically. Use the spill reference operator # to reference the entire spill: =UNIQUE(A2:A100) in cell E1, then reference E1# in data validation.
=UNIQUE(A2:A100)
Count distinct values with COUNTA(UNIQUE(...))
The combination =COUNTA(UNIQUE(A2:A100)) is the modern replacement for the complex SUMPRODUCT(1/COUNTIF(...)) formula for counting unique values. It is shorter, easier to read, and works with both text and numbers.
=COUNTA(UNIQUE(A2:A100))
Excel vs Google Sheets
Excel vs Google Sheets
UNIQUE is available in Excel 365, Excel 2021, and all versions of Google Sheets. It is not available in Excel 2019 or earlier. The syntax and spill behaviour are identical in Excel 365 and Google Sheets. Formulas copy between the two without changes.
Related reading
Frequently asked questions
UNIQUE returns a list of distinct values from a range, removing all duplicates. The result spills dynamically into adjacent cells and updates whenever the source data changes. =UNIQUE(A2:A100) returns each value that appears in the range exactly once, in the order they first appear.
Use the third argument exactly_once set to TRUE: =UNIQUE(A2:A100,,TRUE) returns only values that appear exactly one time in the range, excluding any value that appears more than once. The default FALSE returns one instance of every distinct value including those that appear multiple times.
Yes. =UNIQUE(A2:C100) returns distinct rows -- combinations of values across all three columns. Two rows are considered duplicates only if every column matches. To find unique values from a single column within a multi-column range, pass just that column: =UNIQUE(B2:B100).
No. UNIQUE was introduced in Excel 365 and Excel 2021 as part of the dynamic array functions. It is not available in Excel 2019, 2016, or earlier. For older Excel versions, use Remove Duplicates from the Data tab, pivot tables, or COUNTIF-based helper columns. UNIQUE is available in all modern versions of Google Sheets.
Wrap UNIQUE in COUNTA: =COUNTA(UNIQUE(A2:A100)) counts how many distinct values exist in the range. This is cleaner than the traditional SUMPRODUCT(1/COUNTIF(range,range)) formula and works for text as well as numbers. In older Excel without UNIQUE, the SUMPRODUCT approach is still the standard method.