UNIQUE function

Returns all distinct values from an array, removing duplicates by row or column; use it to extract clean, unique item lists from repetitive data.

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

Generate a UNIQUE formula

Describe what you need. The generator will reach for UNIQUE where UNIQUE is the right tool, and tell you when it is not.

How to get a better answer
  • Name your columns by letter and by header: "column F (Net Value)" beats "the amount column".
  • State every condition, including the negatives — "not cancelled" changes the formula's shape.
  • Say where the data starts if it is not row 1, and whether it will grow.
  • Check the settings above match your spreadsheet: the wrong argument separator is a syntax error on your machine.

Arguments

How UNIQUE reads its arguments
arrayrequiredby_coloptionalexactly_onceoptionalUNIQUE →
ArgumentRequiredDescription
arrayRequiredThe input array or range to deduplicate; can be a single row, column, or 2D range. If empty, returns an empty array.
by_colOptionalOptional logical value (TRUE/FALSE or 1/0); FALSE (default) removes duplicate rows, TRUE removes duplicate columns.
exactly_onceOptionalOptional logical value (TRUE/FALSE or 1/0); FALSE (default) returns all unique values, TRUE returns only values appearing exactly once.

Returns

An array of unique values in the same shape as the input, with duplicates removed.

Availability

Excel: 365 / 2021+ · Google Sheets: Supported

Worked examples

1. Extract unique vehicle IDs from a maintenance log

VehicleOdometerService DateCostGarage
Tesla-1250002026-01-15450Downtown
Tesla-1350002026-03-20320Downtown
Truck-2120002026-02-10650Uptown
Tesla-1450002026-05-05480Downtown
Truck-2220002026-04-15720Uptown
Van-380002026-01-25380Midtown
=UNIQUE(A2:A7)

Result: Tesla-1Truck-2Van-3

UNIQUE scans the Vehicle column (A2:A7) and returns each distinct vehicle once, in the order it first appears. Tesla-1 appears three times but is returned only once; Truck-2 twice; Van-3 once.

2. Find vehicles serviced at exactly one location

VehicleOdometerService DateCostGarage
Tesla-1250002026-01-15450Downtown
Tesla-1350002026-03-20320Downtown
Truck-2120002026-02-10650Uptown
Tesla-1450002026-05-05480Downtown
Truck-2220002026-04-15720Uptown
Van-380002026-01-25380Midtown
=UNIQUE(A2:A7,,TRUE)

Result: Van-3

With exactly_once=TRUE, UNIQUE returns only vehicles appearing exactly once in the log. Tesla-1 appears 3 times and Truck-2 twice, so they're excluded. Only Van-3 appears exactly once.

3. Get a list of all unique service locations

VehicleOdometerService DateCostGarage
Tesla-1250002026-01-15450Downtown
Tesla-1350002026-03-20320Downtown
Truck-2120002026-02-10650Uptown
Tesla-1450002026-05-05480Downtown
Truck-2220002026-04-15720Uptown
Van-380002026-01-25380Midtown
=UNIQUE(E2:E7)

Result: DowntownUptownMidtown

UNIQUE extracts the Garage column (E2:E7) and returns each distinct location once. Downtown appears three times but is listed once; Uptown twice; Midtown once.

Common errors

Which UNIQUE error are you seeing?
UNIQUE returned an error#SPILL!
Move the UNIQUE formula to an empty area with enough space, or delete the content blocking the spill range.
#VALUE!
Use only TRUE, FALSE, 1, or 0 for by_col and exactly_once parameters.
#REF!
Update the formula to point to a valid, existing range, or use a named range to make references more robust.
ErrorWhy it happensHow to fix it
#SPILL!The result array wants to spill into cells that already contain data or formulas, blocking the output.Move the UNIQUE formula to an empty area with enough space, or delete the content blocking the spill range.
#VALUE!The by_col or exactly_once argument is not a valid logical value (e.g., you passed text like "yes" instead of TRUE).Use only TRUE, FALSE, 1, or 0 for by_col and exactly_once parameters.
#REF!The array argument references a range that has been deleted or moved, breaking the reference.Update the formula to point to a valid, existing range, or use a named range to make references more robust.

Tips and when to use something else

  • UNIQUE preserves the order of first appearance, not alphabetical order. Sort the result with SORT() if you need alphabetical ordering.
  • Use exactly_once=TRUE to find singleton items—values that appear only once in your dataset, useful for finding outliers or one-off occurrences.
  • When your array has headers, exclude the header row from the UNIQUE range or the header will be treated as a data value and may be included in duplicates.
  • For deduplication with multiple criteria across columns, combine UNIQUE with FILTER or consider XLOOKUP with a helper column instead.

Related functions

FILTERReturns filtered rows from an array based on a boolean criteria array, retaining the original order and all columns.XLOOKUPXLOOKUP returns the value from a specified return array that corresponds to a given lookup value, with optional handling for missing matches and search direction.COUNTIFCounts cells in a range that meet a specific criterion, returning a number — use it to tally matching rows like employee counts by department.TOCOLFlattens an array into a single column, optionally ignoring blanks or errors and reading row-by-row or column-by-column.SORTSORT arranges array data in ascending or descending order by a specified column, returning a sorted copy as a dynamic array.

Frequently asked questions

Does UNIQUE preserve the original order of items?
Yes. UNIQUE returns items in the order they first appear in the input array. If you need them sorted alphabetically or by another criterion, wrap the result with SORT().
How does UNIQUE handle blank cells or empty strings?
UNIQUE treats blank cells and empty strings as distinct values. If your data has blank rows, they will appear once in the result. Filter or clean your data before using UNIQUE if blanks should be excluded.
Can UNIQUE work on a 2D range and return unique rows?
Yes. If you pass a range with multiple columns and by_col=FALSE (the default), UNIQUE removes duplicate rows as complete records. Each row must match entirely for it to be considered a duplicate.
What's the difference between UNIQUE and manually removing duplicates?
UNIQUE is dynamic—if your source data changes, the unique list updates automatically. Manual removal is a one-time edit that won't reflect new data. UNIQUE is also simpler for large datasets.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17