Excel practice › Excel functions › UNIQUE

UNIQUE in Excel: how it works, with examples you can edit

UNIQUE returns each different value of a list once. To list the sales reps named in B2:B8, however many orders each one has: =UNIQUE(B2:B8).

You type the formula in one cell and the list spills into the cells below it. Add an order for a new rep and the list grows by itself. Microsoft lists UNIQUE for Excel 2021, Excel 2024 and Microsoft 365. Excel 2019 and 2016 are not on that list.

Skip to the 3 exercises ↓

Syntax

=UNIQUE(array, [by_col], [exactly_once])

array
The range to take the values from. With several columns, UNIQUE compares whole rows.
by_col
Optional. FALSE, or nothing, compares rows with each other, which is what a list going down the sheet needs. TRUE compares columns.
exactly_once
Optional. FALSE, or nothing, returns every different value once. TRUE returns only the values that occur exactly once in the data.
Live exampleEach rep once
E2fx
ABCDE
1OrderRepStateReps
25001AveryTXAvery
35002JordanCOJordan
45003AveryTXRiley
55004RileyAZCasey
65005JordanNM
75006AveryOK
85007CaseyCO

How Excel reads it

UNIQUE goes down the range and keeps a value the first time it sees it. Avery appears three times in the orders and once in the result. The values come out in the order they first appear in the data, not in alphabetical order. For that, wrap the formula in SORT: =SORT(UNIQUE(B2:B8)).

Only the first cell holds the formula. The rest of the list belongs to it, and the cells it needs have to be empty, or Excel shows #SPILL! (see the #SPILL! error).

Several columns: unique rows

Give UNIQUE two columns and it compares whole rows. A row is dropped only when both of its values repeat an earlier row. Avery sold to Texas twice, and that pair appears once. Avery and Jordan still appear twice each, because their second rows have a different state:

Live exampleEach rep and state combination once
E2fx
ABCDEF
1OrderRepStateRepState
25001AveryTXAveryTX
35002JordanCOJordanCO
45003AveryTXRileyAZ
55004RileyAZJordanNM
65005JordanNMAveryOK
75006AveryOKCaseyCO
85007CaseyCO

Two mistakes that give the wrong list

1. Setting the third argument to TRUE. exactly_once sounds like "show each value once". It means something else: return only the values that occur a single time in the data. Avery and Jordan have several orders, so they disappear:

Live exampleexactly_once: not the list without duplicates
E2fx
ABCDE
1OrderRepStateReps
25001AveryTXRiley
35002JordanCOCasey
45003AveryTX
55004RileyAZ
65005JordanNM
75006AveryOK
85007CaseyCO

2. Values that only look the same. UNIQUE compares the cells as they are. In this copy of the log, someone typed a space after Jordan in B6. You can't see it, but "Jordan " and "Jordan" are two different values, so the name shows up twice:

Live exampleThe same name twice
E2fx
ABCDE
1OrderRepStateReps
25001AveryTXAvery
35002JordanCOJordan
45003AveryTXRiley
55004RileyAZJordan
65005Jordan NMCasey
75006AveryOK
85007CaseyCO

Practice: 3 exercises

Type the formula in the box, press Enter. Clicking a cell inserts its address. Each answer is checked on the data you see and again on changed data, so a typed-in value won't pass.

Exercise 1A list without duplicates

Which states did the team ship to this week? In E2, write one formula that lists every state in column C once.

E2fx
ABCDE
1OrderRepStateStates
25001AveryTX
35002JordanCO
45003AveryTX
55004RileyAZ
65005JordanNM
75006AveryOK
85007CaseyCO
Show the answer

=UNIQUE(C2:C8) gives TX (5 rows, starting with TX).

UNIQUE keeps each state the first time it appears: TX and CO occur twice in the orders and once in the list.

Exercise 2Unique and sorted

In E2, list the reps in column B once each, in alphabetical order.

E2fx
ABCDE
1OrderRepStateReps A-Z
25001AveryTX
35002JordanCO
45003AveryTX
55004RileyAZ
65005JordanNM
75006AveryOK
85007CaseyCO
Show the answer

=SORT(UNIQUE(B2:B8)) gives Avery (4 rows, starting with Avery).

UNIQUE runs first and returns the four names. SORT then puts that list in ascending order, which for text is A to Z.

Exercise 3Count the different values

How many different states are in column C? In E2, return the number with one formula: remove the duplicates, then count what is left.

E2fx
ABCDE
1OrderRepStateStates
25001AveryTX
35002JordanCO
45003AveryTX
55004RileyAZ
65005JordanNM
75006AveryOK
85007CaseyCO
Show the answer

=COUNTA(UNIQUE(C2:C8)) gives 5.

UNIQUE returns the five different states, and COUNTA counts the items of that list. The list never has to appear on the sheet.

Questions people ask

How do I count unique values in Excel?

Wrap UNIQUE in COUNTA: =COUNTA(UNIQUE(C2:C8)) removes the duplicates and counts what is left. On the orders above it returns 5. Use it on a range with no empty cells.

What is the difference between unique and distinct values in Excel?

Distinct values are every different value, each listed once: that is what =UNIQUE(B2:B8) returns. Values that are unique in the strict sense occur a single time in the data: that is =UNIQUE(B2:B8,FALSE,TRUE), with the third argument, exactly_once, set to TRUE.

How do I get a unique list sorted alphabetically?

Put SORT around UNIQUE: =SORT(UNIQUE(B2:B8)). UNIQUE alone returns the values in the order they first appear. See SORT for sorting largest first or by another column.

Does UNIQUE work in Excel 2019 or 2016?

Microsoft lists UNIQUE for Excel 2021, Excel 2024 and Microsoft 365. Excel 2019 and 2016 are not on that list.

Keep going

Lesson 10 is nine drills on a support desk's ticket log: UNIQUE, a blocked spill to clear, SORT, FILTER with one and with several conditions, the empty result, and a summary table built on a spilled list with #. It's part of Pro ($29 once for everything, or $19 for the Excel track). Lessons 1 and 2 and the level test are free.

Related

SORT function, with live examplesFILTER function, with live examplesCOUNTIF function, with live examplesTRIM function, with live examples#SPILL! error in Excel: meaning and fixesExcel FILTER, SORT, UNIQUE practice (Pro)Excel functions with live examplesExcel practice online

Written and tested by Nico, who builds SheetDrill (about). Every formula on this page ran through the engine that checks the exercises before the page was published. Checked against: Microsoft Support: UNIQUE function. Last checked November 2026.