How to Use UNIQUE Function in Excel: Syntax and Examples

The UNIQUE function in Excel extracts distinct values from a range or array, removing duplicates automatically. Use it by typing =UNIQUE(range) where range is your data list, and Excel returns only unique entries.

Syntax

The UNIQUE function syntax is:

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

array: The range or array to extract unique values from.

by_col (optional): Set to TRUE to compare columns instead of rows. Default is FALSE.

exactly_once (optional): Set to TRUE to return only values that appear exactly once.

excel

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

Example

This example shows how to get unique names from a list in cells A1:A7.

excel

A1: Apple
A2: Banana
A3: Apple
A4: Orange
A5: Banana
A6: Grape
A7: Pear

In B1, enter:
=UNIQUE(A1:A7)

Output

Apple
Banana
Orange
Grape
Pear

Common Pitfalls

Common mistakes include:

• Not using a dynamic array-enabled Excel version (UNIQUE requires Excel 365 or Excel 2021+).
• Forgetting that UNIQUE spills results into adjacent cells, so avoid placing data directly below the formula.
• Using the wrong range or including blank cells unintentionally.

Example of wrong and right usage:

excel

Wrong: =UNIQUE(A1:A7) placed where cells below are filled (causes #SPILL! error)

Right: Place =UNIQUE(A1:A7) in an empty cell with enough space below for results.

Quick Reference

Parameter	Description	Default
array	Range or array to extract unique values from	Required
by_col	Compare columns instead of rows	FALSE
exactly_once	Return only values that appear once	FALSE

Key Takeaways

Use =UNIQUE(range) to quickly get distinct values from a list or range.

UNIQUE works only in Excel 365 and Excel 2021 or later versions with dynamic arrays.

Make sure the formula output area below is empty to avoid spill errors.

Use optional parameters to customize by column or to get values appearing exactly once.

UNIQUE automatically updates results if the source data changes.