UNIQUE function in Excel: distinct lists, distinct counts and sorting

The UNIQUE syntax

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

• array: the range to de-duplicate, one column or several.
• by_col: FALSE (the default) compares rows, TRUE compares columns. You rarely need TRUE.
• exactly_once: FALSE (the default) returns every distinct value, TRUE only the values that appear exactly once.

UNIQUE is a dynamic array function, available in Microsoft 365, Excel 2021 and later and Excel for the web. The result spills, so keep the cells below the formula empty. The comparison is not case-sensitive: "Harbor Cafe" and "harbor cafe" count as one.

A distinct list, sorted A to Z

=UNIQUE(A2:A13) returns Harbor Cafe, Pine Deli, Oak Bistro, Mill Bakery, Fern Grill and Sage Kitchen: first-appearance order, which is the order of the first order each customer placed. For an alphabetical list wrap it in SORT: =SORT(UNIQUE(A2:A13)) starts with Fern Grill and ends with Sage Kitchen. =SORT(UNIQUE(A2:A13),,-1) sorts Z to A.

Worked example: twelve order lines, distinct customers and a summary

Rows 2 to 13 are the order lines. Rows 16 to 21 are the distinct customers with their orders and revenue, and column D is the same list sorted A to Z.

Worked example: twelve order lines, distinct customers and a summary

	A	B	C	D
1	Customer	Tea	Region	Amount
2	Harbor Cafe	Green tea	North	240
3	Pine Deli	Black tea	South	180
4	Oak Bistro	Green tea	North	320
5	Harbor Cafe	Oolong	North	150
6	Mill Bakery	Black tea	East	210
7	Pine Deli	Green tea	South	260
8	Fern Grill	Oolong	East	90
9	Harbor Cafe	Black tea	North	300
10	Sage Kitchen	Green tea	West	130
11	Oak Bistro	Oolong	North	170
12	Pine Deli	Black tea	South	220
13	Mill Bakery	Green tea	East	190
14
15	UNIQUE(A2:A13)	Orders	Revenue	SORT(UNIQUE())
16	Harbor Cafe	3	690	Fern Grill
17	Pine Deli	3	660	Harbor Cafe
18	Oak Bistro	2	490	Mill Bakery
19	Mill Bakery	2	400	Oak Bistro
20	Fern Grill	1	90	Pine Deli
21	Sage Kitchen	1	130	Sage Kitchen
22
23	Distinct customers: ROWS(UNIQUE())			6
24	Same count, pre-365 way: SUMPRODUCT(1/COUNTIF())			6
25	Customers who ordered exactly once			2
26	First of them (exactly_once = TRUE)			Fern Grill
27	Distinct teas			3
28	Distinct customer and tea pairs: UNIQUE(A2:B13)			11
29	Distinct customers in the North region			2
30	Check: revenue of the summary = all orders			0

Fictional order data. In Excel each UNIQUE formula spills its whole list from one cell; the grid uses INDEX(UNIQUE(…),k) so each value can be shown and checked in its own cell.

Count distinct values

A distinct list has as many rows as there are distinct values, so =ROWS(UNIQUE(A2:A13)) returns 6. If the list has already spilled into A16, =ROWS(A16#) or =COUNTA(A16#) gives the same answer. Before dynamic arrays, the usual trick was =SUMPRODUCT(1/COUNTIF(A2:A13,A2:A13)): every customer that appears three times contributes 3 × 1/3 = 1. It also returns 6 and still works in old versions, but it is slow on large ranges and fails with blank cells (division by zero).

Values that appear exactly once

Set the third argument to TRUE: =UNIQUE(A2:A13,FALSE,TRUE) returns only Fern Grill and Sage Kitchen, the 2 customers with a single order. That is a different question from "distinct": distinct means "each value once", exactly once means "values without duplicates". It is useful for one-off customers who need a follow-up call, or for codes that should repeat but do not.

UNIQUE on several columns, and with FILTER

With more than one column, UNIQUE compares whole rows. =UNIQUE(A2:B13) returns each customer and tea combination once: 11 pairs, because only Pine Deli and Black tea appears twice. To get the distinct values of a subset, filter first: =UNIQUE(FILTER(A2:A13,C2:C13="North")) lists the 2 customers from the North region (Harbor Cafe and Oak Bistro), even though North has five order lines. The FILTER guide shows how to combine several criteria in that step.

A summary table that grows by itself

Put =UNIQUE(A2:A13) in A16 and refer to the spilled list with A16#: =COUNTIF(A2:A13,A16#) counts the orders and =SUMIFS(D2:D13,A2:A13,A16#) totals the revenue per customer, each as one formula that spills next to the list. Harbor Cafe has 3 orders worth 690 and Pine Deli 3 orders worth 660. When a new customer appears in the data, the list and both columns get one row longer without any formula being copied. The check in the last row proves that the summary adds up to the total of all orders. The same pattern with SUMIFS is the basis of most dynamic reports.

How the example shows the spilled lists

In Excel, A16 would hold the single formula =UNIQUE(A2:A13) and the six names would spill into A16:A21, with B16 and C16 spilling next to it. The example grid shows every value in its own checked cell, so A16 to A21 use =INDEX(UNIQUE($A$2:$A$13),k) for the k-th distinct value and B and C use a normal COUNTIF and SUMIFS per row. The values are the same as the spilled result.

Common mistakes

• Cells in the way of the spill. A value below the formula blocks the list and shows #SPILL!. Leave enough empty rows for new values.
• Blank cells in the range. UNIQUE returns an empty cell as a 0 in the list. Filter the blanks out first: =UNIQUE(FILTER(A2:A100,A2:A100<>"")).
• Expecting a sorted result. UNIQUE keeps the first-appearance order. Wrap it in SORT for an alphabetical list.
• Mixing up distinct and exactly once. UNIQUE(A2:A13,FALSE,TRUE) drops every value that appears more than once. It is not the distinct list.
• Hidden spaces and spelling variants. "Pine Deli" and "Pine Deli " count as two customers. Clean the column with TRIM before counting.
• Referring to A16:A21 instead of A16#. A fixed range does not grow with the list. The spill reference does.