Quick Navigation

UNIQUE Function

Excel 365+

Summary

The UNIQUE function extracts distinct values from a range or array, automatically removing duplicates and returning a dynamic list. Perfect for cleaning datasets, creating dropdown lists, and preparing data for pivot tables without manual filtering.

Syntax

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

Parameters

Parameter	Type	Required	Description
array	`Range/Array`	Yes	The range or array containing the data to analyze for uniqueness
by_col	`Logical`	No	Set to TRUE to compare columns instead of rows (default FALSE)
exactly_once	`Logical`	No	Set to TRUE to show only values that appear exactly once (default FALSE)

Using the UNIQUE Function

UNIQUE revolutionizes data cleaning by instantly generating lists without duplicates. Use it to create unique customer lists, product inventories, or category summaries. Combine with SORT, FILTER, and other dynamic array functions for powerful data transformations.

Common UNIQUE Examples

Basic Unique List

=UNIQUE(A2:A20)

Returns all unique values from sales list, automatically spills results

Unique by Columns

=UNIQUE(B2:D10,TRUE)

Extracts unique column combinations from multi-column dataset

First-Time Customers Only

=UNIQUE(sales[Customer],,TRUE)

Shows only customers who purchased exactly once

Combined Names

=UNIQUE(B2:B100&" "&C2:C100)

Creates unique full names by concatenating first/last name columns

Sorted Unique List

=SORT(UNIQUE(A2:A100))

Returns unique values in alphabetical/numerical order

Frequently Asked Questions

The spill range is blocked by data. Clear cells below/right of formula or move to empty area.

Yes, when source data changes or if using Excel Table structured references.

Yes, use by_col=TRUE for column-wise uniqueness or entire range for row-wise.

Blanks treated as values - multiple blanks return single blank.

Common Errors and Solutions

#SPILL!

Cause: Cells in spill range contain data

Solution: Clear adjacent cells or choose different location

#REF!

Cause: Source workbook closed (cross-workbook)

Solution: Keep both workbooks open

#VALUE!

Cause: Invalid by_col or exactly_once arguments

Solution: Use TRUE/FALSE or 1/0 only

Single value returned

Cause: All source values identical

Solution: Verify source data contains duplicates

Notes

Requires Excel 365 or Excel 2021 with dynamic arrays
Spill behavior auto-creates result area
Excel Tables make formulas fully dynamic
Case-sensitive for text values
Numbers stored as text treated differently

Compatibility

Available in: Excel 365, Excel 2021

Not available in: Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007

Content last reviewed: December 9, 2025
Update frequency: As needed
Excel versions tested: Excel 365+