Quick Answer

UNIQUE Function

Returns unique values (spills the result).

Syntax

UNIQUE

(array, [by_col], [exactly_once])

Parameters

Parameter	Description	Required
array	The range or array from which to extract unique values.	Required
by_col	FALSE to compare across rows (default) or TRUE to compare across columns.	Optional
exactly_once	FALSE to return all distinct values (default) or TRUE to return only values that occur exactly once.	Optional

Basic Example

Extract distinct values from a list

=UNIQUE(A2:A10)

ResultEach distinct value from A2:A10, listed once

UNIQUE removes duplicates and spills the distinct list.

Advanced Examples

Example 1: Values that appear only once

Find items with no duplicates in a column.

Set exactly_once to TRUE

=UNIQUE(A2:A10, , TRUE)

Result: Only the values that occur exactly once in A2:A10

With exactly_once TRUE, repeated values are excluded entirely.

Example 2: Unique across columns

Distinct values in a single row.

Set by_col to TRUE

=UNIQUE(A1:D1, TRUE)

Result: Each distinct value from the row A1:D1

by_col TRUE compares values across columns instead of down rows.

How UNIQUE Works

UNIQUE returns the distinct values from array. By default it compares values down rows (by_col FALSE) and returns all distinct values. Setting by_col TRUE compares across columns; setting exactly_once TRUE returns only values that occur once. Results spill as a dynamic array.

1

Select the range

Pick the array with possible duplicates.

2

Add options

Optionally set by_col and exactly_once.

3

Ensure spill space

Leave empty cells below/right for the result.

Important Notes & Limitations

Microsoft 365 / Excel 2021+ only.
Comparison is case-insensitive by default; 'Apple' and 'apple' are treated as equal.
Returns a spilled array, so it needs empty room below/right.

Common Errors & Fixes

#SPILL!No room for the spilled results.

Fix: Clear the cells in the spill range.

Case variants treated as duplicatesUNIQUE is case-insensitive.

Fix: Use a helper column with EXACT for case-sensitive uniqueness.

Download Practice File

Practice UNIQUE with Real Data

Download a sample CSV file with pre-populated data and practice exercises for the UNIQUE function. Works in both Excel and Google Sheets.

Works in Google SheetsCompatible with ExcelIncludes exercises

File format: CSV (comma-separated values) - opens in Excel, Google Sheets, and all spreadsheet apps

Related Functions

FILTER

FILTER pulls out matching rows from a table based on a condition. Unlike VLOOKUP (which returns one value), FILTER can return entire rows and multiple matches — all in one formula.

RANDARRAY

Generates an array of random numbers.

SEQUENCE

Generates a sequence of numbers in an array.

SORT

Sorts a range or array (spills the result).

SORTBY

Sorts an array using other arrays as keys.

XMATCH

Modern lookup that returns a position (MATCH replacement).

Frequently Asked Questions

Is UNIQUE in Google Sheets?

Yes, UNIQUE is native in Google Sheets.

How do I get only values that appear once?

Set the exactly_once argument to TRUE.

Is the comparison case-sensitive?

No; UNIQUE treats 'Apple' and 'apple' as the same value.