Dynamic arrays
How to Use UNIQUE in Excel (Distinct Lists, with Examples)
UNIQUE returns the distinct values from a range, removing duplicates and spilling the clean list into new cells. It is the live alternative to the Remove Duplicates button: the result updates automatically as your data grows, and it never touches the original.
UNIQUE is a dynamic-array function, available in Excel 365, Excel 2021 and later, and Google Sheets. This guide covers the syntax, building a distinct list, counting distinct values with COUNTA, sorting the result, and pulling out items that appear exactly once.
UNIQUE syntax
=UNIQUE(array, [by_col], [exactly_once]) | Argument | Required | What it does |
|---|---|---|
array | Required | The range to get distinct values from, such as A2:A100. |
by_col | Optional | FALSE (default) compares rows; TRUE compares columns. Leave it out for a normal vertical list. |
exactly_once | Optional | FALSE (default) returns each distinct value once; TRUE returns only values that appear exactly one time. |
Note: UNIQUE spills, so leave room below. Wrap it in SORT to get a clean, ordered distinct list.
Examples
Real, copy-paste-ready formulas.
1. Build a distinct list
=UNIQUE(A2:A100) Returns each value from A2:A100 once, dropping repeats, and spills the distinct list into the cells below. The source list is left unchanged.
2. Count how many distinct values
=COUNTA(UNIQUE(A2:A100)) UNIQUE produces the distinct list and COUNTA counts how many items it contains, giving a live distinct count in a single cell.
3. Distinct list, sorted A to Z
=SORT(UNIQUE(A2:A100)) Nesting UNIQUE inside SORT first removes duplicates, then orders the result, producing a tidy sorted distinct list in one formula.
4. Values that appear only once
=UNIQUE(A2:A100, FALSE, TRUE) The third argument TRUE switches to "exactly once" mode, returning values with no duplicates at all, rather than one of each value.
How to write UNIQUE step by step
- 1
Click the cell where you want the distinct list to start and type =UNIQUE(
- 2
Select the range you want distinct values from (the array).
- 3
Close the bracket ) and press Enter; the unique values spill down automatically.
- 4
To count them, wrap the formula as =COUNTA(UNIQUE(range)).
- 5
To sort the result, wrap it as =SORT(UNIQUE(range)).
Common errors and fixes
| Error | Why it happens | How to fix it |
|---|---|---|
#SPILL! | A cell in the area where the unique list needs to spill is not empty. | Clear the cells below the formula, or move it to an open part of the sheet. |
#CALC! | The array is empty, or with exactly_once set to TRUE no value appears exactly once. | Point UNIQUE at a range with data, and confirm some values are truly non-repeating if using exactly-once mode. |
Blank counted as a value | Empty cells in the range come through as a 0 or blank entry in the distinct list. | Filter blanks first, for example =UNIQUE(FILTER(A2:A100, A2:A100<>"")). |
#NAME? | UNIQUE is not available in this version of Excel. | UNIQUE needs Excel 365 or Excel 2021. In older versions, use Remove Duplicates on the Data tab instead. |
Frequently asked questions
How do I count distinct values in Excel?
Wrap UNIQUE in COUNTA: =COUNTA(UNIQUE(A2:A100)). UNIQUE produces the distinct list and COUNTA counts its entries, giving a live count that updates as data changes.
What is the difference between UNIQUE and Remove Duplicates?
UNIQUE is a formula that returns a distinct list in new cells and recalculates automatically, leaving the source intact. Remove Duplicates is a one-time command that deletes duplicate rows from your data in place.
How do I get values that appear only once?
Set the third argument to TRUE: =UNIQUE(A2:A100, FALSE, TRUE). This returns only values with no duplicates, which is different from returning one copy of each value.
Does UNIQUE work in Google Sheets?
Yes. Google Sheets has UNIQUE and it spills the same way. It also supports the optional by-column and exactly-once arguments.