UNIQUE Function in Excel: Distinct List Without Remove Duplicates

📎 This article includes 1 downloadable practice file ↓

Dynamic ArrayLevel: ExpertAvailable in: Excel 365 / 2021+

In this article

1. Syntax
2. Examples
3. Example 1
4. Example 2
5. Example 3
6. Power combo
7. UNIQUE in practice
8. Where you will use it
9. Worked example
10. Mistakes people make
11. Related functions
12. Continue learning

UNIQUE returns the distinct values of a range, updating automatically as data changes.

Syntax

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

Argument	What it means
array	Range to de-duplicate.
by_col	TRUE to compare columns instead of rows.
exactly_once	TRUE to return only values that appear once.

Examples

Example 1

=SORT(UNIQUE(B2:B500))

Sorted region list.

Example 2

=COUNTA(UNIQUE(C2:C500))

Number of distinct customers.

Example 3

=UNIQUE(A2:A500, , TRUE)

IDs that appear only once.

Power combo

=LET(r, UNIQUE(B2:B500), HSTACK(r, SUMIFS(E2:E500, B2:B500, r)))

A mini pivot: each region with its total, in one formula.

UNIQUE in practice

Where you will use it

• Distinct customer list
• Values appearing only once
• Dropdown sources

Worked example

Exactly once	Formula	Result
FALSE	=TEXTJOIN(", ",,UNIQUE({"Pune";"Delhi";"Pune";"Goa"},,A2))	Pune, Delhi, Goa
TRUE	=TEXTJOIN(", ",,UNIQUE({"Pune";"Delhi";"Pune";"Goa"},,A3))	Delhi, Goa
FALSE	=TEXTJOIN(", ",,UNIQUE({"Pune";"Delhi";"Pune";"Goa"},,A4))	Pune, Delhi, Goa

Results calculated in Excel.

Mistakes people make

Mistake	What to do instead
Case	UNIQUE ignores case
Trailing spaces	TRIM first

Related functions

FILTER · SORT · COUNTA

📚 Part of the free Excel course: Beginner → Expert · Try it in the Formula Lab or ask the AI Helper.

Continue learning

All Lookup & Reference guides →

Excel #N/A Error in VLOOKUP and XLOOKUP: 5 Fixes That Actually WorkThe #N/A ('Value Not Available') error in Microsoft Excel signals that a lookup function—such…TOROW Function in Excel: Flatten Into One RowTOROW flattens a block into a single row, optionally skipping blanks and errors.WRAPCOLS Function in Excel: Fold a List Into ColumnsWRAPCOLS takes a long list and wraps it into columns of a fixed height…WRAPROWS Function in Excel: Reshape a List Into a GridWRAPROWS reshapes a single column into rows of a fixed width — e.g. a…

📎 Practice files for this article

• 📗
  Practice workbook: Dynamic arrays18 tasks on realistic sales data — type your formula, the Check column turns green when it is right. Answers sheet included.
  ⬇ XLSX · 35 KB

Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.