Function reference

UNIQUE in Excel & Google Sheets

UNIQUE is used in 1 formula pattern in this library. Browse worked examples below, or use the generator to build a custom variation for your specific data.

1 beginnerBuild a custom UNIQUE formula →

When to use UNIQUE

Create a de-duplicated list of product names, customers, or categories from a raw data column to use as a dropdown source or summary label.

Worked examples (1)

Array Formulasbeginner

Extract a unique list with UNIQUE

=UNIQUE(A2:A100)

Open formula UNIQUE reference

Common errors with UNIQUE

UNIQUE is not available

UNIQUE requires Excel 365 or Excel 2021. It is not available in Excel 2019 or earlier. For older versions, use Remove Duplicates from the Data menu or an array formula workaround.

SPILL error

A #SPILL error means cells below the formula are not empty. Clear the range where results need to spill and the formula will resolve automatically.

Frequently asked questions

How do I sort the unique list alphabetically?

Wrap in SORT — =SORT(UNIQUE(A2:A100)) — to return the distinct values in ascending order.

Need a custom variation?

Describe your specific UNIQUE problem and get a formula written for your exact data.

Open generator with UNIQUE