Excel UNIQUE Function

UNIQUE extracts a list of distinct values from a range or array, spilling the results into as many cells as needed. It returns an array, not a single value, so make sure there's empty space below and to the right of your formula cell before you use it.

Available in: Excel 365, Excel 2021. Not available in Excel 2019 or earlier.

Syntax

=UNIQUE(array, [by_col], [exactly_once])

Parameter	Required	Description
`array`	Yes	The range or array to pull distinct values from.
`by_col`	No	TRUE compares data across columns instead of rows. FALSE or omitted (default) compares rows, which is what you want for most standard lists.
`exactly_once`	No	TRUE returns only values that appear exactly one time, excluding anything duplicated. FALSE or omitted (default) returns every distinct value, including ones that repeat.

Basic Example

You're managing a call log with a Department column that repeats for every call handled. You want a clean list of departments without duplicates.

=UNIQUE(B2:B50)

// B2:B50 = the Department column, full of repeated entries

Excel scans the full range top to bottom and returns each distinct value once, in the order it first appears. If "Sales" shows up in row 3, row 9, and row 41, it only appears once in the output, at the position matching row 3.

How UNIQUE Works

Results spill automatically

UNIQUE doesn't need to know how many unique values exist ahead of time. Type the formula in one cell, press Enter, and the results spill downward (or across) into as many cells as the output requires. You never copy the formula down.

by_col switches the comparison direction

By default, UNIQUE compares entire rows against each other. Set by_col to TRUE and it compares columns instead, useful when your "records" run horizontally, like a weekly schedule laid out with dates across the top.

=UNIQUE(A1:F1, TRUE)

// TRUE = compare columns, not rows
// Returns the distinct values across a single horizontal range

exactly_once isolates one-time occurrences

This is the argument most tutorials skip past. Set it to TRUE and UNIQUE returns only values that appear exactly once, dropping anything that shows up two or more times entirely, not just collapsing the duplicates down to one instance.

=UNIQUE(C2:C200, FALSE, TRUE)

// FALSE = compare rows
// TRUE = only values with a single occurrence survive

That distinction matters. UNIQUE(range) with no third argument gives you every distinct value, duplicates included once each. UNIQUE(range, FALSE, TRUE) gives you only the values that never repeated at all.

Multi-column uniqueness

Feed UNIQUE a multi-column range and it treats each full row as the unit of comparison. Two rows only count as duplicates if every column matches.

=UNIQUE(A2:C500)

// Returns distinct combinations of columns A, B, and C together
// A row with the same name but a different region is NOT a duplicate

Nesting inside SORT

UNIQUE preserves first-appearance order, not alphabetical order. Wrap it in SORT when you need the output alphabetized or ranked.

=SORT(UNIQUE(B2:B200))

// UNIQUE builds the distinct list first
// SORT arranges it ascending before it spills

Common Use Cases

Building a dynamic dropdown list

A data validation dropdown that updates itself as new products get added, without editing the validation rule every time.

=UNIQUE(products[Category])

// products[Category] = a Table column, so the range grows automatically

Point Data Validation's source at the spill range (using the # reference, like A2#) and the dropdown list stays current as long as the source Table keeps growing.

Counting distinct values

You need a single number, not a list, like how many different sales reps closed deals this month.

=COUNTA(UNIQUE(D2:D300))

// returns the count of distinct rep names

Extracting unique values with a condition

Combine UNIQUE with FILTER when you only want distinct values that meet a criterion, like unique customer names from orders placed in Q2 only.

=UNIQUE(FILTER(customers, quarter="Q2"))

// FILTER narrows the range to Q2 rows first
// UNIQUE then removes duplicates from that narrowed set

Finding one-time buyers

A retail dataset where you want to flag customers who purchased exactly once, useful for a win-back email campaign.

=UNIQUE(orders[CustomerID], FALSE, TRUE)

// TRUE (third argument) keeps only customers who appear one single time
// Repeat buyers are excluded entirely, not just deduplicated

✓

To pull a unique list from two separate ranges at once, stack them first with VSTACK, then wrap the result in UNIQUE: =UNIQUE(VSTACK(range1, range2)). This is cleaner than running UNIQUE twice and merging the outputs manually.

Handling Errors

UNIQUE throws a small set of specific errors, and each one points to a different fix.

Common causes:

#SPILL! when the cells below or beside the formula aren't empty, blocking the results from spilling
#REF! when array points to a closed workbook
#CALC! when the source range is genuinely empty, so there's nothing to return

=IFERROR(UNIQUE(B2:B50), "Check source range")

⚠

#SPILL! is the error you'll hit most. Clear every cell in the spill path before entering the formula, or move the formula to a cell with more open space below it. Excel outlines the blocking cells with a dashed border when you click the formula, which makes them easy to spot.

Notes & Gotchas

Why does UNIQUE return #SPILL!?

#SPILL! means the results have nowhere to go. Something in the range below or to the right of your formula, a stray value, a merged cell, part of another formula's spill range, is blocking the output. Delete whatever's occupying that space and the formula recalculates immediately.

Does UNIQUE distinguish between blank cells and empty strings?

Yes, and this trips people up constantly. A truly empty cell and a cell containing "" (an empty string returned by another formula) are treated as different values by UNIQUE, so both can show up separately in your results. If you need to exclude both, filter them out first with FILTER(range, range<>"") before passing the result to UNIQUE.

What happens if I add new rows below my source range?

Nothing, until you edit the formula. =UNIQUE(B2:B50) will never expand past row 50 on its own, even if you add data in row 51. Reference an Excel Table column instead, like products[Category], and the range grows automatically as rows get added.

Can UNIQUE reference a closed workbook?

Not reliably. Cross-workbook references only work while both files are open. Close the source workbook and the formula returns #REF! instead of recalculating. If you need this to work unattended, bring the data into the same workbook first, or use Power Query instead.

Does UNIQUE treat text and numbers as the same value?

No. The text string "10" and the number 10 are counted as two distinct values, even though they look identical in the cell. This usually shows up when data gets imported from a system that stores IDs as text, so a numeric-looking column secretly mixes formats. Convert the whole column to one data type with VALUE() or TEXT() before running UNIQUE if you're seeing unexpected duplicates.

How do I get UNIQUE to ignore case differences?

You don't need to; it already does. "Sales", "SALES", and "sales" are treated as the same value automatically, no extra setup required. If you actually need case-sensitive comparison (rare, but it happens with product codes), you'll need an array formula built on EXACT instead, since UNIQUE has no argument for it.

Related Functions

Function	Use this when...
`FILTER`	You need to narrow a range by a condition before or after removing duplicates.
`COUNTIF`	You're on a version before Excel 365 and need to flag or count duplicates without UNIQUE.
`VLOOKUP`	You need one matching value from a table, not a full list of distinct entries.