Excel UNIQUE Function Examples: Formulas for Common Tasks

Excel’s UNIQUE function extracts distinct values from a range, saving you from manually removing duplicates or maintaining a separate list.

These examples show how to use it with names, numbers, multiple columns, and other functions, plus what to check when the formula returns an unexpected result.

How does the Excel UNIQUE function work?

UNIQUE returns a dynamic array: one formula can produce results in multiple cells, which Excel calls a spill range.

If the source list changes, the results can update automatically.

Enter the formula in one cell with enough blank space below or beside it for the results to spill.

The syntax is:

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

• array is the range or array to examine.
• by_col is optional.
  
  Omit it or use FALSE to compare rows; use TRUE to compare columns.
• exactly_once is optional.
  
  Omit it or use FALSE to return each distinct value once; use TRUE to return only values that occur once.

For example, =UNIQUE(A2:A8) returns one copy of each distinct entry in A2:A8.

It keeps the order of first appearance rather than sorting the results.

Basic Excel UNIQUE function examples

Return a list of distinct names

Suppose A2:A7 contains Ana, Ben, Ana, Chris, Ben, and Dana.

Enter:

=UNIQUE(A2:A7)

The spill range contains Ana, Ben, Chris, and Dana.

Each name appears once, in the same order as its first occurrence in the source.

Return numbers or categories without duplicates

The function also works with numbers, dates, and text categories.

If B2:B8 contains order statuses with repeated values, use:

=UNIQUE(B2:B8)

The resulting list can serve as the source for a summary, a chart, or a data validation list.

Format returned dates as dates if Excel displays their serial numbers.

Show values that occur exactly once

Use the third argument to exclude any value that appears more than once.

If A2:A7 contains 10, 10, 20, 30, 30, and 40, enter:

=UNIQUE(A2:A7,,TRUE)

The result is 20 and 40.

The empty second argument leaves by_col at its default row-based behavior.

This differs from ordinary UNIQUE: the value 10 is not returned once, because it does not occur exactly once.

Work with rows and multiple columns

Find distinct records across several columns

When the input contains multiple columns, UNIQUE compares whole rows by default.

For a table of department and employee pairs in A2:B8, use:

=UNIQUE(A2:B8)

Excel returns each distinct department-and-employee combination.

A repeated employee in the same department is removed, but the same employee paired with a different department remains because that row is distinct.

Find duplicate columns instead of rows

To compare columns in a horizontal range, set by_col to TRUE.

For values in A1:F2:

=UNIQUE(A1:F2,TRUE)

Excel returns distinct columns, preserving the order in which each column first appears.

This option is less common for lists arranged vertically, so check the layout before changing it.

Combine UNIQUE with other Excel functions

Return a sorted distinct list

UNIQUE preserves the source order.

Wrap it in SORT when the result should be alphabetical or numerical:

=SORT(UNIQUE(A2:A100))

For a descending sort, add the sort order argument:

=SORT(UNIQUE(A2:A100),1,-1)

The 1 identifies the first column of the returned array; -1 requests descending order.

Get distinct values that meet a condition

Combine FILTER and UNIQUE to first select matching rows, then remove duplicates.

If product names are in A2:A100 and regions are in B2:B100, this returns distinct products for the West region:

=UNIQUE(FILTER(A2:A100,B2:B100="West"))

To avoid an error when there are no matching rows, provide FILTER’s optional empty-result argument:

=IFERROR(UNIQUE(FILTER(A2:A100,B2:B100="West")),"No matches")

Count distinct values

To count the number of distinct entries in a nonempty range, combine UNIQUE with COUNTA:

=COUNTA(UNIQUE(A2:A100))

If the source may contain blank cells, consider filtering them out first so a blank does not count as an item:

=COUNTA(UNIQUE(FILTER(A2:A100,A2:A100<>"")))

In this formula, <> is Excel’s “not equal to” comparison operator.

It appears as escaped text in the example so it is displayed as formula code.

Compatibility and common problems

UNIQUE is available in Excel for Microsoft 365, Excel for the web, and Excel 2021 or later perpetual versions.

It is not available as a native dynamic array function in Excel 2019 or earlier.

In an older version, use Advanced Filter, Remove Duplicates, or a helper-column formula instead.

• #SPILL!: One or more cells in the expected output area are occupied or merged.
  
  Clear the obstructing cells, or move the formula to an open area.
• Unexpected near-duplicates: Leading or trailing spaces, inconsistent spelling, and text-versus-number differences can make entries appear distinct.
  
  Clean source data with functions such as TRIM or standardize the values first.
• Results include a blank: The source range contains blank cells.
  
  Filter out empty entries before applying UNIQUE if you do not want a blank result.
• Formula does not update: A fixed reference such as A2:A100 will not include new data below row 100.
  
  Use an Excel Table column reference, such as =UNIQUE(Table1[Product]), so the input range expands with the table.
• Formula is shown as text: Check that the cell is not formatted as Text and that the formula begins with =.
  
  Re-enter the formula after changing the cell format to General.

Practical tips for reliable results

Place a dynamic formula outside an Excel Table: spilled array formulas cannot spill within table cells.

You can reference a table’s columns as the input, then put the UNIQUE formula in a regular worksheet cell.

For a dropdown, set Data Validation’s list source to the spill reference, such as =D2#, where D2 contains the UNIQUE formula.

When copying a formula, use the spill reference operator # to refer to its full output.

For example, =COUNTA(D2#) counts the values currently spilling from D2.

This keeps dependent formulas aligned as the unique list grows or shrinks.