UNIQUE

Return a list of distinct values from a range or array, removing duplicates automatically.

Lookup & Reference
|
Excel 2021+
|
Google Sheets Supported

Spreadsheet editor

Syntax

=UNIQUE(array, [by_col], [exactly_once]) Returns: Array

Arguments

Argument Required Description
array Yes The range or array from which to return unique rows or columns.
by_col No A logical value that sets the comparison direction. TRUE compares columns. FALSE or omitted compares rows.
exactly_once No A logical value that controls filtering. TRUE returns only values that appear exactly once. FALSE or omitted returns all distinct values.

About

UNIQUE takes a range or array and returns only the distinct values, removing any duplicates. The results spill into neighboring cells as a dynamic array, so they update automatically when your source data changes. Use it whenever you need a clean list, like pulling distinct customer names from an order log or listing the departments that appear in a staff directory.

By default UNIQUE compares rows, but you can set by_col to TRUE to compare columns instead. There is also an exactly_once option: set it to TRUE to return only values that appear a single time in your data, which is handy for spotting one-off entries.

UNIQUE pairs well with other dynamic array functions. Wrap it in SORT to get an alphabetized list, pass it to FILTER to narrow results further, or feed it into COUNTIF to count how often each distinct value appears.

Examples

Find products ordered only once

Set the third argument to TRUE to return only values that appear exactly once. Try adding a duplicate product and watch it disappear from the results.

Spreadsheet editor

Unique name-city pairs across columns

Pass a multi-column range and UNIQUE compares entire rows. Notice that Sara Kim appears twice in the results because she visited two different cities.

Spreadsheet editor

Sorted unique list of expense categories

Wrap UNIQUE inside SORT to get an alphabetized list with no duplicates. Try adding a new category and see it land in the right spot automatically.

Spreadsheet editor

Watch out for

⚠

#SPILL! error from blocked cells

The cells where UNIQUE needs to spill its results already contain data, so Excel returns a #SPILL! error.

→ Clear the cells below (or beside) the formula so the dynamic array has room to expand.

⚠

Forgetting the exactly_once argument

You expect only values that appear once, but UNIQUE returns all distinct values by default.

→ Set the third argument to TRUE: =UNIQUE(A2:A50, FALSE, TRUE). This filters the list to entries that occur exactly one time.

⚠

Case sensitivity surprises

UNIQUE treats "Apple" and "apple" as the same value because it is not case-sensitive by default.

→ If you need case-sensitive comparison, combine UNIQUE with EXACT or clean your data first so casing is consistent.

Tips & notes

UNIQUE is a dynamic array function available in Excel 2021, Excel for Microsoft 365, and Excel for the web. The results spill automatically, so make sure neighboring cells are empty. In Google Sheets the same function is available, though it only accepts the array argument (no by_col or exactly_once options).

Common questions

Can UNIQUE work across multiple columns at once?

Yes. When you pass a multi-column range, UNIQUE compares entire rows and removes rows where every column value matches. This is useful for finding distinct combinations, like unique Name + Region pairs.

How do I get a sorted list of unique values?

Wrap UNIQUE inside SORT: =SORT(UNIQUE(A2:A50)). Both are dynamic array functions, so the result spills and updates automatically.

Does UNIQUE preserve the original order of the data?

Yes. UNIQUE returns values in the order they first appear in the source range. It does not sort them. Use SORT around the result if you need alphabetical or numerical order.