UNIQUE Function
Excel Guides / Dynamic Arrays & LAMBDA
Dynamic Arrays & LAMBDA
Turn a column full of repeats into a clean, self-updating list of distinct values.
Updated 12 Oct 2026 Ā· 4 min read Ā· By Sky Dashboards
UNIQUE turns a column full of repeats into a clean list of distinct values. Use it to list every client who booked, every product you sold or every category in a dataset, and the list keeps itself up to date as the data changes. It works hand in hand with SORT, FILTER, COUNTA and drop-down lists, and this guide shows each pairing.
In this guide
UNIQUE syntax
=UNIQUE(array, [by_col], [exactly_once])| Argument | Required? | What it does |
|---|---|---|
array |
Required | The range or array to remove duplicates from, such as A2:A9. With several columns, a row only counts as a duplicate when every column matches. |
by_col |
Optional |
FALSE (the default) compares rows. TRUE compares columns, for data laid out across a row. |
exactly_once |
Optional |
FALSE (the default) returns each distinct value once. TRUE returns only the values that appear exactly once in the source. |
UNIQUE is available in Excel 2021, Excel 2024, Microsoft 365 and Excel for the web. Like FILTER, it spills: you type it in one cell and the result fills as many cells below as it needs. If something is in the way you get #SPILL!, and the FILTER guide covers the fixes.
Distinct values or values that appear once
A photography studio logs each booking on its own row. Column D lists every client; column E lists only the clients who have booked once.
| A | B | C | D | E | ||
|---|---|---|---|---|---|---|
| 1 | Client | Service | All clients | One-off | ||
| 2 | Maya | Portrait | Maya | Priya | ||
| 3 | Leo | Headshots | Leo | Sam | ||
| 4 | Maya | Family | Priya | Ava | ||
| 5 | Priya | Portrait | Sam | |||
| 6 | Leo | Headshots | Ava | |||
| 7 | Sam | Product | ||||
| 8 | Maya | Portrait | ||||
| 9 | Ava | Headshots |
D2 holds =UNIQUE(A2:A9) and E2 holds =UNIQUE(A2:A9, , TRUE). Both spill down.
The two lists answer different questions. Column D is a distinct list: every name, each shown once, in the order it first appears. Column E keeps only names that occur exactly once, so Maya (three bookings) and Leo (two) drop out. That is a quick way to find one-off customers worth following up.
Note the empty second argument in =UNIQUE(A2:A9, , TRUE). The extra comma holds the place of by_col, so Excel knows the TRUE belongs to exactly_once.
Note
UNIQUE is not case-sensitive. maya and Maya count as the same value, and the result shows the spelling that appears first.
Sorted unique lists
UNIQUE keeps the order of first appearance. For an alphabetical list, wrap it in SORT:
=SORT(UNIQUE(A2:A9))Returns Ava, Leo, Maya, Priya, Sam.
=SORT(UNIQUE(A2:A9), , -1)Z to A. The -1 is sort_order; the skipped argument is sort_index.
Leave out blank cells
If the range includes empty cells, for example because you pointed it at A2:A500 to leave room for new bookings, UNIQUE returns a 0 for the blank. Filter the blanks out first:
=SORT(UNIQUE(FILTER(A2:A500, A2:A500<>"")))Distinct, sorted and with no stray 0.
Count distinct values
Wrap UNIQUE in a counting function to find how many different values there are:
=COUNTA(UNIQUE(A2:A9))Returns 5: five different clients.
=ROWS(UNIQUE(A2:B9))Returns 6 unique client and service pairs. ROWS counts rows, not cells, so it is the better choice when the result has more than one column.
To count distinct values that meet a condition, put FILTER inside:
=IFERROR(ROWS(UNIQUE(FILTER(A2:A9, B2:B9="Headshots"))), 0)Leo and Ava booked headshots, so the result is 2. IFERROR returns 0 when nothing matches, because FILTER would otherwise return #CALC!.
In older versions
Without UNIQUE, the classic distinct count is =SUMPRODUCT(1/COUNTIF(A2:A9, A2:A9)). It works in any version of Excel but returns #DIV/0! if the range contains blank cells. The SUMPRODUCT guide explains the function itself.
Feed a drop-down list from UNIQUE
A drop-down that offers every client should not need editing each time someone new books. Build the list with UNIQUE, then point Data Validation at the spill:
- In a spare column, or on a separate sheet for lists, enter
=SORT(UNIQUE(FILTER(A2:A500, A2:A500<>""))). Say it sits in H2. - Select the cell that should hold the drop-down.
- On the Data tab, in the Data Tools group, click Data Validation.
- Under Allow, choose List.
- In Source, type
=$H$2#and click OK.
The # tells Excel to use the whole spill range. When a new client appears in column A, the list in H grows by a row and the drop-down offers the new name straight away. The drop-down list guide covers input messages, error alerts and dependent lists.
Tip
Keep helper lists like this on their own sheet and hide it once the workbook is finished. The selector cells stay tidy while the lists update in the background.
Unique combinations across two columns
Give UNIQUE more than one column and it compares whole rows. This returns each client and service pairing once:
| A | B | C | D | E | ||
|---|---|---|---|---|---|---|
| 1 | Client | Service | Client | Service | ||
| 2 | Maya | Portrait | Maya | Portrait | ||
| 3 | Leo | Headshots | Leo | Headshots | ||
| 4 | Maya | Family | Maya | Family | ||
| 5 | Priya | Portrait | Priya | Portrait | ||
| 6 | Leo | Headshots | Sam | Product | ||
| 7 | Sam | Product | Ava | Headshots | ||
| 8 | Maya | Portrait | ||||
| 9 | Ava | Headshots |
D2 returns six unique rows. The headers in D1:E1 are typed in.
Leo's second headshot booking and Maya's second portrait disappear because both columns match an earlier row. Maya still appears twice, once for Portrait and once for Family, because those rows differ.
Values across a row
When the data runs across a row, such as product names in B1:H1, set by_col to TRUE: =UNIQUE(B1:H1, TRUE). The result spills to the right instead of down.
Frequently asked questions
Why does UNIQUE show a 0 in the list?
The source range includes an empty cell, and UNIQUE returns it as 0. Remove blanks first with =UNIQUE(FILTER(A2:A500, A2:A500<>"")).
Does UNIQUE update automatically?
Yes. It recalculates whenever the source changes. If the source is an Excel table, rows added to the table are included too.
Can I use UNIQUE in Excel 2019?
No. It needs Excel 2021, Excel 2024, Microsoft 365 or Excel for the web. In older versions, Data > Remove Duplicates gives a one-off distinct list.
How is UNIQUE different from Remove Duplicates?
Remove Duplicates deletes repeated rows from the data itself and does not update later. UNIQUE leaves the source untouched and returns a separate list that stays current.
Want the finished dashboard instead?
My Excel templates come with these formulas already wired up. Pick one, type in your own rows and the charts update.
Browse templates ā