Removing Duplicates With UNIQUE

The UNIQUE function returns a list with duplicates removed, keeping only the distinct values from a range. It spills the result, so a single formula produces a clean, always-current list.

This is far easier than the old Remove Duplicates menu command, which made a static copy you had to redo whenever the data changed.

UNIQUE takes one required argument and two optional ones:

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

• array is the range to deduplicate.
• by_col set to TRUE compares columns instead of rows (rarely needed).
• exactly_once set to TRUE returns only values that appear a single time.

Most of the time you just supply the array.

Suppose A2:A12 contains region names with repeats: East, West, East, North, West, East. To get each region once:

=UNIQUE(A2:A12)

It spills three values: East, West, North, in the order they first appear. Add a new region to the source and the list grows automatically.

UNIQUE can deduplicate whole rows. If A2:B12 holds Region and Product, a row is duplicate only when both columns match.

=UNIQUE(A2:B12)

This returns every distinct Region-and-Product combination as a small two-column spilled table. It is ideal for building the row labels of a summary report.

Set the third argument to TRUE to keep only values that occur a single time and drop any that repeat at all.

=UNIQUE(A2:A12,,TRUE)

Note the two commas: we skip the by_col argument and supply only exactly_once. If East appears three times it is removed entirely, while a region that appears once is kept.

Wrap UNIQUE in COUNTA or ROWS to count distinct values. To see how many different regions exist:

=ROWS(UNIQUE(A2:A12))

ROWS counts the rows in the spilled list. As new regions appear, the count updates by itself. This is a clean way to answer how many distinct items are in your data.

UNIQUE keeps first-appearance order. To present an alphabetical list, wrap it in SORT:

=SORT(UNIQUE(A2:A12))

Now the distinct regions come out in order: East, North, West. This combination is one of the most common patterns for building tidy report labels.

UNIQUE pairs beautifully with FILTER. To get the distinct products sold in the East region only:

=UNIQUE(FILTER(B2:B12, A2:A12="East"))

FILTER first narrows to East rows, then UNIQUE removes duplicate products. Nesting spill functions like this lets you answer rich questions with one formula.

A classic mini-report combines UNIQUE for labels with SUMIF for totals. Put the distinct regions in column E with =UNIQUE(A2:A12), then beside the spilled list use:

=SUMIF(A2:A12, E2#, C2:C12)

The E2# spill reference feeds every distinct region into SUMIF, returning one total per region. The whole summary updates as data grows.

UNIQUE works in both Excel 365 and Google Sheets, though there are small differences. In Google Sheets, the older UNIQUE compared full rows and did not have the exactly_once argument, so check behavior if results differ.

Sheets also offers UNIQUE inside QUERY and a SORTN function for advanced cases. For everyday distinct lists, the basic =UNIQUE(range) behaves the same on both platforms.

A distinct list is perfect for a data-validation dropdown. Put =UNIQUE(A2:A12) in a helper area, then point a dropdown at its spill range using the # operator.

If the list anchors in H1, set the validation source to =H1#. As new regions appear in your data, the dropdown choices grow automatically with no manual updates required.

Test your understanding of UNIQUE.

You learned to extract distinct values with UNIQUE:

• Syntax is =UNIQUE(array, [by_col], [exactly_once]).
• It spills distinct values in first-appearance order.
• It can deduplicate whole multi-column rows.
• Set exactly_once to TRUE for values that appear only once.
• Combine with SORT, FILTER, ROWS, and SUMIF for tidy live reports.

Next, you will learn to diagnose and fix the SPILL error.