Is the UNIQUE function available in Excel 2019?

No on desktop Excel 2019. UNIQUE requires Excel 2021 or Microsoft 365.

What is the difference between UNIQUE and Remove Duplicates?

UNIQUE is a live formula that spills distinct values. Remove Duplicates is a one-time command that changes the source data.

Why does UNIQUE show #SPILL!?

Cells below the formula are not empty. Clear the spill range or move the formula to a column with enough free rows.

Why does my formula show an error?

Open the linked error pages (#N/A, #VALUE!, #REF!) from our directory. Most formula posts fail because of data type mismatches, deleted references, or wrong match modes — not because the function name is wrong.

Should I copy formulas down or use a Table?

Copying down is fine for one-off models. Convert the range to an Excel Table when you add rows often — structured references stay readable and break less.

Formulas - 2026-06-09

UNIQUE Function in Excel: Remove Duplicates & Build Lists

Master the Excel UNIQUE function to extract distinct values, count duplicates, and build dynamic dropdown lists in Microsoft 365. Includes BYCOL, BYROW, and spill tips.

What the UNIQUE Function Does

UNIQUE returns a list of distinct values from a range or array. It spills results into adjacent cells automatically in Microsoft 365 and Excel 2021. Syntax: \\\ =UNIQUE(array, [by_col], [exactly_once]) \\\ array — the range or array to scan by_col — FALSE (default) compares rows; TRUE compares columns exactly_once — FALSE returns every unique value; TRUE returns values that appear only once UNIQUE replaces many manual Remove Duplicates steps when you need a live list that updates with source data.

Example 1: Distinct Customer Names

Source list in \A2:A500\: \\\ =UNIQUE(A2:A500) \\\ The spill starts at the formula cell and lists each customer name once. Add the list to Data Validation for a dynamic dropdown.

Example 2: Values That Appear Only Once

Find SKUs with a single occurrence (possible data entry errors): \\\ =UNIQUE(A2:A500, FALSE, TRUE) \\\ The third argument TRUE means "exactly once only."

Example 3: Unique Rows From Multiple Columns

When uniqueness depends on two columns (Region + Product): \\\ =UNIQUE(A2:B500) \\\ Excel treats each row as a record and returns distinct row combinations.

Example 4: Count How Many Duplicates Exist

After UNIQUE in column D, compare counts: \\\ =COUNTA(A2:A500)-ROWS(UNIQUE(A2:A500)) \\\ Approximate duplicate row count (blank cells affect COUNTA — filter blanks first on critical reports).

UNIQUE vs Remove Duplicates vs COUNTIFS

Tool Behavior UNIQUE Live formula; updates when source changes Data → Remove Duplicates Static; alters source table COUNTIFS Counts per criteria; does not list distinct values Use UNIQUE for dashboards; use Remove Duplicates for one-time cleanup before archiving.

#SPILL! and Blocked Ranges

If something sits below the formula, UNIQUE shows #SPILL!. Fix: Clear cells in the spill area Move the formula to open space Use \=TAKE(UNIQUE(A2:A500), 20)\ temporarily to limit rows while testing See FILTER spill error fix for the same spill rules.

Build a Dynamic Dropdown

\=UNIQUE(A2:A500)\ on a Settings sheet Name the spill range or reference \$D$2#\ (spill reference in modern Excel) Data Validation → List → point to the UNIQUE spill When new customers appear in column A, the dropdown updates after recalc.

Related Functions

SORT — alphabetize the UNIQUE list FILTER — return full rows matching criteria COUNTIFS — count per distinct key Remove duplicates guide Best functions for data analysis

Formula debugging workflow

Step Action 1 Select the cell and read the formula in the formula bar 2 Use Formulas → Evaluate Formula for nested functions 3 Check for text stored as numbers (green triangle) 4 Fix errors using our formula error guide Go deeper: Compare similar functions · Fix common problems

Frequently asked questions

Is the UNIQUE function available in Excel 2019? No on desktop Excel 2019. UNIQUE requires Excel 2021 or Microsoft 365.
What is the difference between UNIQUE and Remove Duplicates? UNIQUE is a live formula that spills distinct values. Remove Duplicates is a one-time command that changes the source data.
Why does UNIQUE show #SPILL!? Cells below the formula are not empty. Clear the spill range or move the formula to a column with enough free rows.
Why does my formula show an error? Open the linked error pages (#N/A, #VALUE!, #REF!) from our directory. Most formula posts fail because of data type mismatches, deleted references, or wrong match modes — not because the function name is wrong.
Should I copy formulas down or use a Table? Copying down is fine for one-off models. Convert the range to an Excel Table when you add rows often — structured references stay readable and break less.