UNIQUE Function in Excel: Distinct Lists That Grow
· 7 min read
UNIQUE returns each distinct value of a range once, in the order it first appears. Write it in one cell and the list spills into the cells below:
=UNIQUE(array, [by_col], [exactly_once])- array is the range to take distinct values from. It can be one column or several.
- by_col is FALSE (the default) to compare rows, or TRUE to compare columns.
- exactly_once is FALSE (the default) to return every distinct value, or TRUE to return only the values that appear one time.
UNIQUE needs Excel 2021, Excel for Microsoft 365 or Excel for the web. Excel 2019 and older show #NAME?. There, the usual substitute is Remove Duplicates on a copy of the column, which does not update when the data changes.
This UNIQUE function Excel guide uses one small log of support tickets. It covers a distinct list, counting unique values, the two optional arguments, and a summary table that adds a row by itself when a new name turns up in the data.
The example table
Twelve support tickets: the customer, the agent who handled it, the product and the hours spent.
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Customer | Agent | Product | Hours | Customers | Only once | How many | API customers | |
| 2 | Acme | Rosa | Portal | 1.5 | Acme | Elm Works | 6 | Cobalt | |
| 3 | Birch Co | Jin | Billing | 0.5 | Birch Co | Fjord | Dune Ltd | ||
| 4 | Cobalt | Omar | API | 3.0 | Cobalt | Acme | |||
| 5 | Acme | Jin | Billing | 1.0 | Dune Ltd | Fjord | |||
| 6 | Dune Ltd | Rosa | API | 2.0 | Elm Works | ||||
| 7 | Birch Co | Omar | Portal | 0.5 | Fjord | ||||
| 8 | Acme | Rosa | API | 2.5 | |||||
| 9 | Elm Works | Jin | Portal | 1.0 | |||||
| 10 | Cobalt | Rosa | Billing | 1.5 | |||||
| 11 | Fjord | Omar | API | 4.0 | |||||
| 12 | Dune Ltd | Jin | Portal | 0.5 | |||||
| 13 | Acme | Omar | Billing | 1.0 |
A distinct list with UNIQUE
=UNIQUE(A2:A13)Result: six customers, Acme, Birch Co, Cobalt, Dune Ltd, Elm Works and Fjord. Acme appears four times in the data and once in the list. The order is the order of first appearance, not A to Z. For an alphabetical list, wrap it in SORT: =SORT(UNIQUE(A2:A13)).
UNIQUE ignores case, so "acme" and "Acme" count as one customer, and the list shows whichever spelling comes first. It does not ignore spaces. "Acme " with a trailing space is a separate value, and the two lines look the same on screen. If a name seems to appear twice, check both with =LEN(), or clean the column first with TRIM, as shown in removing spaces in Excel.
Several columns at once
Give UNIQUE more than one column and it returns each distinct combination. =UNIQUE(B2:C13) lists every agent and product pair that occurs, such as Rosa with Portal and Rosa with API. A row counts as a duplicate only if every column matches.
Count unique values
UNIQUE returns a list, so counting its items gives the number of distinct values:
=COUNTA(UNIQUE(A2:A13))Result: 6. This replaces the older SUMPRODUCT(1/COUNTIF(...)) trick, which is harder to read and breaks on blank cells.
One catch: if the range contains blank cells, UNIQUE returns a 0 for them, and COUNTA counts it as one more value. Remove blanks first.
UNIQUE without blanks, or with a condition
To leave blanks out, filter them away before UNIQUE sees them:
=UNIQUE(FILTER(A2:A13, A2:A13<>""))The same pattern gives distinct values that meet a condition. The customers who opened an API ticket, in I2:
=UNIQUE(FILTER(A2:A13, C2:C13="API"))Result: Cobalt, Dune Ltd, Acme and Fjord. FILTER keeps the API rows, then UNIQUE removes the second Acme. To count them, put COUNTA around the whole formula. The FILTER function guide covers conditions in more detail, including AND and OR.
exactly_once: values that appear one time
The third argument changes the question from "which values are there?" to "which values appear only once?":
=UNIQUE(A2:A13, FALSE, TRUE)Result: Elm Works and Fjord, the two customers with a single ticket. Everyone else has two or more. You can leave the middle argument empty, =UNIQUE(A2:A13,,TRUE), but writing FALSE makes it clearer what you meant.
This is a good tool for spotting one-off customers, or for checking an ID column: =UNIQUE(ids, FALSE, TRUE) should return every ID if none are repeated. If it returns fewer, some IDs are duplicates.
by_col: unique values across a row
By default UNIQUE compares rows. When your values run across a row instead, set by_col to TRUE.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Week | 1 | 2 | 3 | 4 | 5 | 6 |
| 2 | On call | Rosa | Jin | Rosa | Omar | Jin | Rosa |
| 3 | |||||||
| 4 | Rows (default) | Rosa | Jin | Rosa | Omar | Jin | Rosa |
| 5 | Columns | Rosa | Jin | Omar |
=UNIQUE(B2:G2) returns all six names. That is not a bug. The range is a single row, and a single row is already unique as a row. =UNIQUE(B2:G2, TRUE) compares the six columns and returns Rosa, Jin, Omar across B5:D5.
A summary table that grows by itself
The most useful thing UNIQUE does is build the label column of a summary. Put the distinct list in one cell, then point SUMIFS and COUNTIFS at the whole spill with #.
| A | B | C | D | E | F | G | H | I | J | K | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Customer | Agent | Product | Hours | Agent | Hours | Tickets | Avg hours | Total hours | ||
| 2 | Acme | Rosa | Portal | 1.5 | Jin | 3.0 | 4 | 0.75 | 19.0 | ||
| 3 | Birch Co | Jin | Billing | 0.5 | Omar | 8.5 | 4 | 2.13 | |||
| 4 | Cobalt | Omar | API | 3.0 | Rosa | 7.5 | 4 | 1.88 | |||
| 5 | Acme | Jin | Billing | 1.0 | |||||||
| 6 | Dune Ltd | Rosa | API | 2.0 | |||||||
| 7 | Birch Co | Omar | Portal | 0.5 | |||||||
| 8 | Acme | Rosa | API | 2.5 | |||||||
| 9 | Elm Works | Jin | Portal | 1.0 | |||||||
| 10 | Cobalt | Rosa | Billing | 1.5 | |||||||
| 11 | Fjord | Omar | API | 4.0 | |||||||
| 12 | Dune Ltd | Jin | Portal | 0.5 | |||||||
| 13 | Acme | Omar | Billing | 1.0 |
The label column, in F2:
=SORT(UNIQUE(B2:B13))The hours, in G2:
=SUMIFS(D2:D13, B2:B13, F2#)F2# means "the whole spill that starts in F2". SUMIFS runs once for each agent in it and spills its own column of results. The result:
| Agent | Hours | Tickets | Avg hours |
|---|---|---|---|
| Jin | 3.0 | 4 | 0.75 |
| Omar | 8.5 | 4 | 2.13 |
| Rosa | 7.5 | 4 | 1.88 |
H2 counts with =COUNTIFS(B2:B13, F2#), and I2 divides one spill by the other with =G2#/H2#. K2 adds up the hours with =SUM(G2#), which gives 19.0, the same as the raw hours column.
Why this beats typed labels
In a classic summary table you type each label once and fill SUMIFS down. That works until a new agent starts. The typed list does not know about them, so their hours are missing from every row, and the total no longer matches the raw data.
With F2#, there is nothing to fill. A new name in column B appears in F, and G, H and I grow a row to match, because they read the spill rather than a fixed range like F2:F4. If you write F2:F4 instead of F2#, the summary stops growing at three rows, and the new agent's hours go missing without any error.
Two rules keep it working:
- Leave room below. A total row under the spill blocks it, and the anchor shows
#SPILL!the day a new label arrives. Put totals above or beside the summary, as K2 does here. - Keep the source range big enough.
B2:B13stops at row 13. Use a table (Ctrl+T) and structured references, so new tickets are included automatically.
For more on SUMIFS with extra conditions, see SUMIFS with multiple criteria.
FAQ
How do I use the UNIQUE function in Excel?
Write =UNIQUE(A2:A13) in an empty cell. Each distinct value of A2:A13 spills into the cells below, in order of first appearance. Wrap it in SORT for A to Z.
How do I count unique values in Excel?
Use =COUNTA(UNIQUE(range)). If the range has blanks, filter them out first: =COUNTA(UNIQUE(FILTER(range, range<>""))).
How do I get values that appear only once?
Set the third argument to TRUE: =UNIQUE(A2:A13, FALSE, TRUE).
Does UNIQUE ignore case?
Yes. "acme" and "Acme" are one value. Extra spaces are not ignored, so clean the data with TRIM first.
Which Excel versions have UNIQUE?
Excel 2021, Excel 2024, Excel for Microsoft 365 and Excel for the web. Older versions show #NAME?.
You can practise UNIQUE on a full year of order and refund data, including an audit where a summary with typed labels missed a new refund reason, on the practice page.
Practise this on a realistic dataset
Solve it yourself on one company's data, with instant feedback that points at what's wrong without giving away the answer.
- Each refunded East customer once, A to Z
- Count orders next to a spilled region list
- A channel summary built from one label spill
- A new refund reason has no totals beside itAudit