Spreadsheet formulas explained, side by side

UNIQUE Function in Excel, Google Sheets, Airtable & Notion

UNIQUE returns the distinct values of a range, in the order they first appear, and spills them into the cells below. Set the third argument to TRUE to get only values that appear exactly once.

Syntax in each app

AppSupportSyntax & notes
Excel✔ Built inUNIQUE(array, [by_col], [exactly_once])
Excel 2021 / Microsoft 365
Not case-sensitive: “North” and “north” count as the same value.
Google Sheets✔ Built inUNIQUE(range, [by_column], [exactly_once])
Case-sensitive: “North” and “north” are both returned.
Airtable≈ WorkaroundARRAYUNIQUE(values)
Only in a rollup field over linked records (or applied to a lookup field). Returns the distinct values; there is no exactly_once option.
Notion✔ Built inlist.unique()
Works on any list, e.g. prop("Orders").map(current.prop("Region")).unique(). Case-sensitive; no exactly_once option.
Try UNIQUE with your own values
One value per line, or separate with commas
Result (as Excel & Google Sheets return it)
North, South, East, West Spills into 4 cells
Your formula, ready to paste
Excel=UNIQUE(A2:A8) open excel.new ↗
Google Sheets=UNIQUE(A2:A8) open sheets.new ↗
Airtable
workaround
ARRAYUNIQUE(values)
Notion["North", "South", "East", "South", "West", "North", "East"].unique()

Excel & Sheets use cell ranges. Paste this sample data into cell A1 of a blank sheet, then paste the formula into any empty cell.

Region
North
South
East
South
West
North
East

The result follows Excel, which ignores case. The Airtable formula is the aggregation formula of a rollup field; Airtable and Notion have no exactly_once option.

Arguments

ArgumentWhat it means
arrayThe range to de-duplicate. With several columns, whole rows are compared.
[by_col] optionalFALSE (default) compares rows; TRUE compares columns.
[exactly_once] optionalTRUE returns only values that occur exactly once. FALSE (default) returns every distinct value.

Examples

FormulaResultWhyCopy
=UNIQUE(A2:A8)North, South, East, WestTester data: each value once, in order of first appearance.
=UNIQUE(A2:A8, FALSE, TRUE)WestOnly values that occur exactly once.
=COUNTA(UNIQUE(A2:A8))4Count distinct values.
=SORT(UNIQUE(A2:A8))East, North, South, WestDistinct values in alphabetical order.

Examples use Excel / Google Sheets syntax unless noted.

How UNIQUE works

UNIQUE builds a live list of distinct values: every customer, region or category that appears in a column, without duplicates. It keeps the order of first appearance, so wrap it in SORT for an alphabetical list, and in COUNTA to count distinct values. Like all dynamic array functions it spills into the cells below and shows #SPILL! if they are not empty.

The third argument changes the meaning: UNIQUE(range, FALSE, TRUE) returns only values that appear exactly once, which is handy for finding singletons. With several columns, whole rows are compared, so UNIQUE(A2:B100) lists distinct combinations. A common surprise is case: Excel treats “North” and “north” as the same value and keeps the first spelling, while Google Sheets returns both. Trailing spaces also make values distinct; clean them with TRIM.

UNIQUE needs Excel 2021 or Microsoft 365. Airtable offers ARRAYUNIQUE(values) as the aggregation formula of a rollup field over linked records. Notion has .unique() on any list, including mapped relation values.

Frequently Asked Questions

How do I count unique values?
=COUNTA(UNIQUE(A2:A100)). If the range has blanks, the blank counts as one value; use =COUNTA(UNIQUE(FILTER(A2:A100, A2:A100<>""))).
Is UNIQUE case-sensitive?
Not in Excel. Google Sheets, Airtable's ARRAYUNIQUE and Notion's unique() treat different capitalisation as different values.

Part of the Lookup & Reference functions. Results in the tester follow Excel and Google Sheets behaviour; always double-check important work in your own file.