How to Use UNIQUE in Google Sheets
=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])
range: the data to check.by_column(optional, defaultFALSE):TRUEcompares columns instead of rows.exactly_once(optional, defaultFALSE):TRUEreturns only entries that appear a single time, with no duplicates at all.
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 | |
|---|---|
| 1 | Name |
| 2 | Ann |
| 3 | Ben |
| 4 | Ann |
| 5 | Cara |
| 6 | Ben |
| 7 | Ann |
To list each name once:
=UNIQUE(A2:A7)
This returns Ann, Ben and Cara, in three cells going down. Here is why:
- Sheets reads the rows in order and keeps the first Ann, the first Ben and Cara.
- 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
- Duplicates that will not go away. Google's help page says to check for trailing spaces or other hidden differences.
"Ann"and"Ann "count as different. - Inconsistent formats. Numbers, percentages and currency should be formatted the same way.
- Blocked output cells. Anything below the formula stops the list from spreading.
- Confusing the two TRUE options. The second argument compares columns. The third keeps only one-time entries.
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.
Exercise 2: Appears once
Write a formula that returns only the names in A2:A7 that appear exactly once.
What to learn next
- FILTER: pull out the rows that match.
- COUNTIFS: count rows that meet several conditions.
- QUERY: filter, group and sort with one formula.
- Remove duplicates: two ways to clean a list.
- SORT: sort a range with a formula.
Syntax and rules in this guide were checked against Google's UNIQUE help page. See all guides.