How to Use UNIQUE in Google Sheets

By Tomosuke Hasegawa · Updated Oct 29, 2026 · Beginner · 5 min read · includes 2 free exercises

In short: UNIQUE returns the rows of a range with duplicates removed, keeping the first occurrence of each. The formula is =UNIQUE(range, [by_column], [exactly_once]).

What UNIQUE does

UNIQUE builds a clean list from data that repeats: the distinct customers in an order log, the different tags in a column. The result is a live list that updates when the source data changes, and the original is left untouched.

UNIQUE syntax

=UNIQUE(range, [by_column], [exactly_once])

Per Google's help page, rows come back in the order in which they first appear in the source range.

A step-by-step example

Here is a column of names in cells A1:A7, with repeats.

A
1Name
2Ann
3Ben
4Ann
5Cara
6Ben
7Ann

To list each name once:

=UNIQUE(A2:A7)

This returns Ann, Ben and Cara, in three cells going down. Here is why:

  1. Sheets reads the rows in order and keeps the first Ann, the first Ben and Cara.
  2. It drops the later repeats of Ann and Ben.

The cells below the formula must be empty, or Sheets cannot show the full result.

Show only entries that appear once

Set the third argument to TRUE to remove every value that has any duplicate, including the first copy:

=UNIQUE(A2:A7, FALSE, TRUE)

This returns only Cara, because Ann and Ben each appear more than once.

Common UNIQUE mistakes

Try it yourself

Use the name list above (A1:A7). Type your formula into the box, starting with =, then press Check.

Exercise 1: Distinct names

Write a formula that lists each name in A2:A7 only once.

Hint: one argument is enough.

Exercise 2: Appears once

Write a formula that returns only the names in A2:A7 that appear exactly once.

Hint: the second argument is FALSE and the third is TRUE.

What to learn next

Syntax and rules in this guide were checked against Google's UNIQUE help page. See all guides.