UNIQUE Function

Returns a list with duplicates removed.

Category

Lookup & Reference

Available in

Microsoft 365, Excel 2021+

Returns

A spilled list of unique values

Taught hands-on in Level 4: Advanced Dynamic Arrays & Logic · Dynamic Arrays module

Overview

UNIQUE gives you each distinct value once. Unlike Remove Duplicates, it doesn't change your data — and it updates as the list changes.

It can also return values that appear exactly once, which is handy for spotting one-offs.

Syntax

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

Arguments

ArgumentRequired?What it does
arrayYesThe range or array to de-duplicate.
by_colOptionalFALSE = compare rows (default), TRUE = compare columns.
exactly_onceOptionalTRUE = return only values that occur once. FALSE (default) = every distinct value.

Examples

Example 1 — Distinct regions

List each region once.

C2fx=UNIQUE(A2:A6)
ABC
1RegionUnique
2EastEast
3WestWest
4EastNorth
5North
6West
=UNIQUE(A2:A6) Explain

Example 2 — Sorted, counted list

Combine with SORT and COUNTA for a clean, sorted list and its size.

=SORT(UNIQUE(A2:A100)) Explain

And =COUNTA(UNIQUE(A2:A100)) tells you how many distinct regions there are.

Pass two columns to get unique combinations: =UNIQUE(A2:B100).

Common mistakes

  • A 0 in the result. Blank cells count as a value. Use UNIQUE(FILTER(A2:A100, A2:A100<>"")).
  • Case-insensitive. "east" and "East" are treated as the same value.

Notes

Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.