Cellwise

Dynamic arrays function

Excel UNIQUE function

UNIQUE returns each value in a range once, with duplicates removed. The results spill down into the cells below. Use it to list your customers, regions, or products from a long order log.

fx=UNIQUE(array)

Arguments

array
The range to pull distinct values from.

UNIQUE examples

Every result below was calculated live from this sample sheet.

ABCD
1OrderCustomerRegionTotal
2SO-901Harbor CafeWest220
3SO-902Pine & CoEast140
4SO-903Harbor CafeWest95
5SO-904NorthgateNorth310
6SO-905Pine & CoEast180
7SO-906Ashby DeliWest60
  1. fx=UNIQUE(B2:B7)

    Result: Harbor Cafe spills 4×1

    Harbor Cafe
    Pine & Co
    Northgate
    Ashby Deli

    Spills each customer once: Harbor Cafe, Pine & Co, Northgate, Ashby Deli.

  2. fx=SORT(UNIQUE(C2:C7))

    Result: East spills 3×1

    East
    North
    West

    Lists the regions once each, in A to Z order.

  3. fx=COUNTA(UNIQUE(B2:B7))

    Result: 4

    Counts the distinct customers: 4.

How to use UNIQUE

  1. Click the top cell of an empty area.
  2. Type =UNIQUE( and select the column.
  3. Press Enter. The list spills down on its own.
  4. Wrap it in SORT if you want it in order.

Common UNIQUE mistakes

Getting #SPILL!

Something is in the way below the formula. Clear those cells so the list has room.

Expecting it to be case-sensitive

UNIQUE treats "east" and "East" as the same value.

Using it in Excel 2019 or older

UNIQUE needs Microsoft 365, Excel 2021 or later. Use Remove Duplicates on a copy in older versions.

UNIQUE FAQ

How do I get a list of unique values in Excel?

Type =UNIQUE(A2:A100) in an empty cell. It spills a list of each value once and updates when the data changes.

How do I count unique values in Excel?

Use =COUNTA(UNIQUE(A2:A100)). UNIQUE builds the list and COUNTA counts it.

How do I get unique values sorted alphabetically?

Wrap it in SORT: =SORT(UNIQUE(A2:A100)).

Practice UNIQUE in these lessons

Guides that use UNIQUE

Related functions

Updated 2026-10-31