Knowledge base / Excel
UNIQUE in Excel: distinct values and exactly-once items
I separate distinct representatives from single occurrences and show which argument selects columns and which selects exactly-once items.

A list of distinct products and a list of products occurring exactly once answer different questions. UNIQUE can produce both, provided you use the appropriate argument. I explain that distinction with small synthetic datasets. I begin by defining the required output rather than assuming that everyone means the same thing by unique.
Availability and argument order
Microsoft lists Microsoft 365, Excel 2021 and Excel 2024. UNIQUE is unavailable in Excel 2019, contrary to the previous version of this entry. Before sharing a workbook, establish whether its recipients have a version supporting the function.
=UNIQUE(array,[by_col],[exactly_once])
The first argument supplies the data. The second selects comparison of whole columns rather than rows; FALSE, or omission, means rows. Setting the third argument to TRUE retains only items occurring exactly once. The Polish reference documents the same function as UNIKATOWE. Square brackets indicate optional arguments and are not part of the formula you type.
Distinct values and single occurrences
Put the header Product in A1 and these synthetic item names in A2:A6:
| Product |
|---|
| Folder |
| Notebook |
| Folder |
| Binder |
| Notebook |
Outside the source, for example in C2, enter:
=UNIQUE(A2:A6)
The output contains one representative of each name: Folder, Notebook and Binder. Repeated entries are not deleted from A2:A6. The formula produces a separate result list. This is useful when you want to know which products occur at all, regardless of how many item records each product has.
If you want names occurring exactly once, enter this in another free area:
=UNIQUE(A2:A6,FALSE,TRUE)
Only Binder remains. Folder and Notebook each appear twice, so neither is included. The third argument does not mean removing extra copies. It excludes every value with multiple occurrences. This matters when creating a list of one-off events or checking which catalogue entries appear only once.
Writing =UNIQUE(A2:A6,TRUE) does not enable that mode. It sets the second argument and compares columns. With a single input column it does not answer the question about names occurring once. I therefore write both logical arguments explicitly in introductory examples, making it easier to see which decision is being changed.

A whole row is the comparison unit
In a separate area, put this second synthetic example in A1:B4:
| Product | Region |
|---|---|
| Notebook | North |
| Notebook | South |
| Notebook | North |
The formula =UNIQUE(A2:B4) returns two combinations: Notebook with North and Notebook with South. It compares entire rows in the supplied range. It does not produce independent product and region lists. Extending an input from A2:A4 to A2:B4 therefore changes the definition of a repeated item.
For a column-comparison example, use another layout in A1:C2: the first row contains A, A, B and the second contains 1, 1, 2. These are synthetic labels and numbers. =UNIQUE(A1:C2,TRUE) compares three whole columns. The first two match, leaving one column containing A and 1 and another containing B and 2. This is different from collecting every distinct individual cell in a rectangle.
Sorting is a separate operation
Do not assume the list is alphabetical. Express an ordering requirement explicitly:
=SORT(UNIQUE(A2:A6))
For the first product list, the output is Binder, Folder, Notebook. Microsoft documents SORT as a separate array-ordering function. Sorting source records and sorting a result are different operations. Choose the one the reader needs, without rearranging input data unnecessarily.
Check the definition of your catalogue before treating the output as a cleaned product list. Identical descriptions can belong to different products when a code is the key. Nearly identical names may instead require a naming correction. UNIQUE cannot decide those business rules for you or establish that removing repeated combinations is the appropriate analytical choice.
Spilling and empty results
The output occupies the neighbouring cells required by the returned array. Leave space and place the formula outside an Excel table, while keeping the source in a table if useful. Microsoft’s dynamic array guide explains that spilling formulas are not supported inside Excel tables themselves.
An occupied output area can produce #SPILL!. Inspect the obstruction before changing the function arguments. If exactly_once finds no single occurrences, the issue is an empty result rather than a failed lookup. Excel does not support empty arrays; Microsoft documents this as #CALC!. The Polish name is #OBL!. The previous suggestion of a missing-match error was misleading.
Practice before using the list in a report
Add another Binder to the first example and extend the range through A7. The distinct list still has three entries, but the exactly-once version has none. Then add a new product and predict both outputs. Ensure the formula includes the new row rather than assuming that a fixed reference expands by itself.
For a real report, record what one input row means and which columns define a repeated record. Then choose the range and arguments. If the problem concerns output space or function availability, the Excel error guide gives a broader investigation process. The resulting list should reflect a deliberate definition of distinctness rather than accidental removal of information.
Key takeaways
Which argument selects exactly-once items?
The third. Use UNIQUE(A2:A6,FALSE,TRUE) to retain values occurring only once.
Does UNIQUE work in Excel 2019?
No. Microsoft lists Microsoft 365, Excel 2021 and Excel 2024.
Is the result automatically sorted?
Do not assume sorting. Combine UNIQUE with SORT when an ordered result is required.
AI was used in an automated way while working on this article. If you want to learn how to do it too, check out this project.
