=UNIQUE(B2:B8) returns each value of B2:B8 once, in the order it first appears. North appears three times and South twice below, and the result lists each region a single time. It spills down from E2 and updates when you change the data: type West into B5 and East disappears from the result, because Cara was its only row.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Rep | Region | Sales | Regions | |
| 2 | Ann | North | 120 | North | |
| 3 | Ben | South | 80 | South | |
| 4 | Ann | North | 90 | East | |
| 5 | Cara | East | 150 | West | |
| 6 | Ben | South | 80 | ||
| 7 | Dan | North | 60 | ||
| 8 | Eve | West | 95 |
UNIQUE does not change the original list. To delete the repeated rows from the table itself, use the Remove Duplicates button on the Data tab (remove duplicates shows both ways).
UNIQUE syntax
=UNIQUE(array, [by_col], [exactly_once])
arrayis the range or array to take values from.by_colis FALSE (the default) to compare rows, TRUE to compare columns.exactly_onceis FALSE (the default) to return every distinct value, TRUE to return only values that appear once.
UNIQUE needs Excel 2021, Excel 2024 or Microsoft 365. In older versions it shows #NAME?. Google Sheets has UNIQUE with the same three arguments.
For a list that runs across a row instead of down a column, set by_col to TRUE: =UNIQUE(B1:H1,TRUE) returns the distinct values of the row as a row.
Unique rows across several columns
Give UNIQUE more than one column and it compares whole rows: a row is a duplicate only when every column matches another row. Here the pairs of rep and region:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Rep | Region | Sales | Rep | Region | |
| 2 | Ann | North | 120 | Ann | North | |
| 3 | Ben | South | 80 | Ben | South | |
| 4 | Ann | North | 90 | Cara | East | |
| 5 | Cara | East | 150 | Dan | North | |
| 6 | Ben | South | 80 | Eve | West | |
| 7 | Dan | North | 60 | |||
| 8 | Eve | West | 95 |
Ann and North appear twice, and so do Ben and South, so 7 rows become 5. Dan is North too, but Dan and North is a different pair, so it stays. With all three columns, =UNIQUE(A2:C8) keeps both Ann rows (120 and 90 differ) and drops only the second Ben, South, 80 row.
Values that appear exactly once
The third argument changes the question from "which values are there" to "which values are not repeated":
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Rep | Region | Sales | Distinct | Exactly once | |
| 2 | Ann | North | 120 | Ann | Cara | |
| 3 | Ben | South | 80 | Ben | Dan | |
| 4 | Ann | North | 90 | Cara | Eve | |
| 5 | Cara | East | 150 | Dan | ||
| 6 | Ben | South | 80 | Eve | ||
| 7 | Dan | North | 60 | |||
| 8 | Eve | West | 95 |
E2 lists all five reps. F2 lists only Cara, Dan and Eve, because Ann and Ben each appear twice. The two commas skip by_col, which keeps its default.
Sorted unique list
UNIQUE keeps the order of first appearance. For an alphabetical list, wrap it in SORT. Next to it, COUNTA counts how many distinct values there are:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Rep | Region | Sales | Regions A to Z | Count | |
| 2 | Ann | North | 120 | East | 4 | |
| 3 | Ben | South | 80 | North | ||
| 4 | Ann | North | 90 | South | ||
| 5 | Cara | East | 150 | West | ||
| 6 | Ben | South | 80 | |||
| 7 | Dan | North | 60 | |||
| 8 | Eve | West | 95 |
E2 returns East, North, South, West, and F2 shows 4. Use =SORT(UNIQUE(B2:B8),,-1) for Z to A. To count without listing, =ROWS(UNIQUE(B2:B8)) gives the same 4; count unique values covers the formulas for Excel versions without UNIQUE.
UNIQUE with a condition
To take distinct values only from rows that meet a condition, filter first and pass the result to UNIQUE. Reps with at least one sale of 90 or more:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Rep | Region | Sales | Reps | |
| 2 | Ann | North | 120 | Ann | |
| 3 | Ben | South | 80 | Cara | |
| 4 | Ann | North | 90 | Eve | |
| 5 | Cara | East | 150 | ||
| 6 | Ben | South | 80 | ||
| 7 | Dan | North | 60 | ||
| 8 | Eve | West | 95 |
Ann has two such sales (120 and 90) and appears once. The result is Ann, Cara and Eve. Change C3 to 100 and Ben joins the list. Any condition from FILTER works here, including several conditions multiplied together.
Use UNIQUE as the source of a drop-down list
A drop-down list built from UNIQUE never shows the same item twice and picks up new values without editing the list. Click F1 and open the list: its items come from the UNIQUE result in column E. Type Central into B6 and a fifth item appears in the list.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Rep | Region | Sales | Regions | North | |
| 2 | Ann | North | 120 | North | 270 | |
| 3 | Ben | South | 80 | South | ||
| 4 | Ann | North | 90 | East | ||
| 5 | Cara | East | 150 | West | ||
| 6 | Ben | South | 80 | |||
| 7 | Dan | North | 60 | |||
| 8 | Eve | West | 95 |
F2 totals the sales of the picked region: 270 for North. In Excel, set it up with Data > Data Validation > List and, as the Source, type the first cell of the result followed by #: =$E$2#. The # means "the whole spilled result", so the list grows and shrinks with UNIQUE. A fixed range such as =$E$2:$E$5 would miss a fifth region.
Practice: a sorted unique list
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Rep | Region | Sales | Regions | |
| 2 | Ann | North | 120 | ||
| 3 | Ben | South | 80 | ||
| 4 | Ann | North | 90 | ||
| 5 | Cara | East | 150 | ||
| 6 | Ben | South | 80 | ||
| 7 | Dan | North | 60 | ||
| 8 | Eve | West | 95 |
Your turn: In E2, list each region once, sorted A to Z.
Why UNIQUE keeps values that look the same
UNIQUE compares values exactly, apart from upper and lower case. When a value you expected once shows up twice, the cells differ in a way you cannot see:
- A trailing space. "North " and "North" are different values. Clean the source with TRIM, or remove the spaces inside the formula:
=UNIQUE(TRIM(B2:B8)). - A number stored as text.
'101and101look alike but one is text, so both stay. Convert the column with VALUE or Data > Text to Columns. - Case is not the problem. UNIQUE ignores case, so apple and Apple count as the same value and the result keeps the spelling it met first.
=UNIQUE({"apple";"Apple";"North ";"North"})
In Excel this returns three values: apple, "North " with its trailing space, and North.
Frequently Asked Questions
What does the UNIQUE function do in Excel?
It returns each distinct value of a range once: =UNIQUE(B2:B8) lists every region in B2:B8 a single time, in the order each first appears. The result spills down from the formula cell and updates when the data changes.
How do I get a sorted list of unique values in Excel?
Wrap UNIQUE in SORT: =SORT(UNIQUE(B2:B8)) returns the distinct values A to Z. Add ,,-1 inside SORT for Z to A: =SORT(UNIQUE(B2:B8),,-1).
What is the difference between distinct and unique in Excel?
=UNIQUE(A2:A8) returns the distinct values: every value once, repeated ones included. =UNIQUE(A2:A8,,TRUE) returns the values that appear exactly once and drops every value that repeats.
Why does UNIQUE show #NAME? in my Excel?
Your version does not have it. UNIQUE needs Excel 2021, Excel 2024 or Microsoft 365. In Excel 2019 and older use Data > Remove Duplicates on a copy of the list, or Data > Advanced with Unique records only.
