Get a clean list of distinct values from any column — one formula, no helper columns, no manual work.
UNIQUE extracts every distinct value from a range and returns them as a spilled list. Add a new item to the source data and the UNIQUE list updates automatically — no refresh needed.
UNIQUE(array, [by_col], [exactly_once])| Argument | What it means |
|---|---|
| array required | The range to deduplicate. Can be a single column, multiple columns, or a row. |
| by_col optional | FALSE (default) = unique rows. TRUE = unique columns. |
| exactly_once optional | FALSE (default) = all distinct values. TRUE = only values that appear exactly once (genuinely unique, not just distinct). |
Column A contains a list of countries with duplicates. Get a clean unique list:
=UNIQUE(A2:A100)Results spill downward automatically. If new countries are added to column A, the list updates instantly.
To find unique Region + Product combinations:
=UNIQUE(A2:B100)UNIQUE treats each row as a unit — it returns rows where the combination of all columns is distinct.
Find items with no duplicates at all (appeared only once in the source):
=UNIQUE(A2:A100, FALSE, TRUE)=SORT(UNIQUE(A2:A100)) gives you a sorted, deduplicated list — perfect as the source for a Data Validation dropdown that updates itself automatically.
=COUNTA(UNIQUE(A2:A100))Type it in a live spreadsheet, get instant feedback. No videos, no downloads.
Start practising free →