Lookup & Reference

UNIQUE Function in Excel

Returns a list of unique values from a range or array.

Syntax

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

Arguments

  • array (required): The range or array to extract unique values from
  • by_col (optional): FALSE=compare rows (default), TRUE=compare columns
  • exactly_once (optional): FALSE=all unique (default), TRUE=only values appearing once

Examples

  • =UNIQUE(A2:A100) - Get unique values from column A - Result: Unique list
  • =UNIQUE(A2:C100) - Get unique rows - Result: Unique rows

UNIQUE best practices

  • Use UNIQUE to build dynamic lists that update as source data changes.
  • Combine UNIQUE with SORT and FILTER for cleaner dashboards and dropdown source ranges.

Excel UNIQUE function documentation

  • UNIQUE is a dynamic array function in Microsoft 365 that returns a list of distinct values from a range or array.
  • Syntax: =UNIQUE(array, [by_col], [exactly_once]). Results spill down or across automatically.
  • Replaces manual Remove Duplicates for live dashboards — updates when source data changes.
  • Related: [SORT](/functions/sort/), [FILTER](/functions/filter/), [COUNTIF](/functions/countif/).

UNIQUE syntax and optional arguments

  • array (required): range or array to deduplicate — one column for a vertical list.
  • by_col FALSE (default): compare rows; TRUE compares columns across a horizontal list.
  • exactly_once FALSE (default): all distinct values; TRUE returns values that appear exactly once only.
  • Requires empty cells below/right for spill — clear blocked cells to fix #SPILL!.

Step-by-step: live distinct customer list

  • Step 1 — Customer names in Table column Orders[Customer] with duplicates.
  • Step 2 — In empty column: =UNIQUE(Orders[Customer]).
  • Step 3 — Sort optional: =SORT(UNIQUE(Orders[Customer])).
  • Step 4 — Count per customer still uses [COUNTIF](/functions/countif/) against full list.
  • Step 5 — For one-time cleanup use Data → Remove Duplicates; UNIQUE for recurring reports.

UNIQUE vs Remove Duplicates vs Pivot

  • UNIQUE — formula-driven; auto-updates; best for dashboards.
  • Remove Duplicates — static one-time destructive change on source.
  • Pivot distinct count — aggregate + count in one step for analytics.
  • Problem: [#SPILL! on dynamic arrays](/problems/filter-spill-error/) when destination blocked.

Worked examples to copy

  • Distinct regions: =SORT(UNIQUE(A2:A1000)).
  • Values appearing once only: =UNIQUE(A:A, FALSE, TRUE).
  • Unique row combinations: =UNIQUE(A2:C500) spills multiple columns.
  • Dropdown source: name =UniqueProducts refers to =UNIQUE(Products[SKU]).

People also ask

  • Does UNIQUE work in Excel 2019? — No on desktop; requires Microsoft 365 / Excel 2021 dynamic arrays.
  • UNIQUE ignore blanks? — Blanks may appear as distinct entry; FILTER them out if needed.
  • UNIQUE vs DISTINCT (SQL)? — Similar purpose; UNIQUE is the Excel function name.
  • UNIQUE slow on big data? — Prefer Excel Table bounded range over full columns.

Common errors

  • Returns #CALC! if result would be empty

Use cases

  • Remove duplicates
  • Create dropdown lists
  • Distinct values

Frequently asked questions

  • How do I get unique values sorted alphabetically? Wrap UNIQUE in SORT: =SORT(UNIQUE(A2:A100)). This first extracts unique values, then sorts them. Add -1 for descending: =SORT(UNIQUE(A2:A100), 1, -1).
  • What does the exactly_once parameter do? When TRUE, UNIQUE returns only values that appear exactly once (no duplicates at all). =UNIQUE(A2:A100, FALSE, TRUE) returns items that have no duplicates. Useful for finding truly unique entries in a dataset.
  • How do I count unique values? Wrap UNIQUE in ROWS or COUNTA: =ROWS(UNIQUE(A2:A100)) counts unique values in a column. For multiple columns: =ROWS(UNIQUE(A2:C100)) counts unique row combinations.
  • Can UNIQUE work with multiple columns? Yes! =UNIQUE(A2:C100) returns unique row combinations across all three columns. A row is only considered duplicate if ALL columns match. This is perfect for finding unique combinations like Name+Date+Product.

Editorial review

  • Reviewed by Excel.Directory Editorial Team. Updated May 2026.

When to use UNIQUE

  • Remove duplicates — common Lookup & Reference scenario for UNIQUE.
  • Create dropdown lists — common Lookup & Reference scenario for UNIQUE.
  • Distinct values — common Lookup & Reference scenario for UNIQUE.

UNIQUE in the Lookup & Reference category

  • Browse all Lookup & Reference functions at /categories/lookup-reference/ for related formulas.
  • UNIQUE syntax: =UNIQUE(array, [by_col], [exactly_once])
  • array (required): The range or array to extract unique values from
  • by_col (optional): FALSE=compare rows (default), TRUE=compare columns
  • exactly_once (optional): FALSE=all unique (default), TRUE=only values appearing once
  • Confirm UNIQUE arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.

Formula checklist before you copy down

  • Confirm UNIQUE arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.
  • If results look wrong, check for text stored as numbers and invisible spaces with TRIM.
  • Spot-check three known input rows manually against expected output.

Related Excel functions

  • FILTER (/functions/filter/): Filters a range of data based on criteria you define. Returns an array of values that meet the specified conditions.
  • SORT (/functions/sort/): Sorts the contents of a range or array in ascending or descending order.
  • COUNTIF (/functions/countif/): Counts the number of cells that meet a criterion.

Common problems and fixes

  • How to Fix #SPILL! in FILTER (/problems/filter-spill-error/): Clear the spill range, unmerge cells, and make sure the FILTER output has enough empty space to expand.

Errors to watch for

  • Returns #CALC! if result would be empty — review causes on linked error pages in the directory.

Copy-paste audit workflow

  • Enter UNIQUE on three test rows with known expected output documented on a QA tab.
  • Fill down only after absolute references are locked on lookup tables and rate tables.
  • Compare against manual calculation or a calculator for financial and statistical functions.
  • Search this directory for comparison guides when choosing between similar functions in the same category.

UNIQUE worked examples to copy

  • =UNIQUE(A2:A100) — Get unique values from column A. Expected result: Unique list.
  • =UNIQUE(A2:C100) — Get unique rows. Expected result: Unique rows.

UNIQUE reference summary for crawlers and offline review

  • UNIQUE belongs to the Lookup & Reference category in Excel. Returns a list of unique values from a range or array.
  • Full syntax: =UNIQUE(array, [by_col], [exactly_once]). Open /functions/unique/ for parameters, FAQs, and related pages.
  • Common mistakes: Returns #CALC! if result would be empty
  • Pair this function with comparison guides when another Excel formula might fit the same task better.
  • Review fix-excel-formula-errors when unexpected errors appear after upgrading Excel or sharing across locales.