UNIQUE
Builds a list without duplicates: all the cities from an orders table, all customers, all product categories.
UNIQUE(array, [by_col], [exactly_once])- [ ]optional part
- array
- The range to remove duplicates from, for example A2:A9.
- by_col
- FALSE or left out: compare rows. TRUE: compare columns. A beginner almost always needs the first option.Optional
- exactly_once
- TRUE: keep only the values that appear exactly once. If you leave it out, you get all values without duplicates.Optional
Return value
A list of values without duplicates. It spills down into the neighboring cells; if they are taken, you get #SPILL!.
Examples
Cities without duplicates
fx
A
B
C
D
E
1
2
3
4
5
6
7
8
9
10
11
Order
City
Cities
5101
New York
5102
Chicago
5103
New York
5104
Denver
5105
Chicago
5106
Miami
5107
New York
5108
Denver
Sheet1
Details
UNIQUE takes a range and returns each value once, in the order it first appears. You write the formula in one cell, and the result takes up as many cells down as there are values. This is called spilling.
The cells below the formula must be empty. If something is in the way, you get #SPILL! instead of the list. When the data in the source range changes, the list updates by itself.
The function is available in Excel 2021 and Microsoft 365. Excel 2019 and earlier don't have it, and the formula shows #NAME? there.
Common mistakes
- There is data below the formula. The result has nowhere to spill, and #SPILL! appears.
- Values differ by an extra space: "New York" and "New York " both end up in the list. Remove the spaces with TRIM first.
- Trying to edit one cell inside a spilled list. You can only change the formula in the first cell.