Removing duplicates with UNIQUE

1 / 4

Most customers ordered several times. Find the ones who ordered only once, who might need a follow-up.

keyboard Typing formulas is easiest on a computer with a keyboard.

only once

Type to enter a value or a formula starting with =. F2 edits, F4 adds $ to a reference.

Study notes

How UNIQUE works

What does UNIQUE do?

It returns a list with every repeat removed, in the order the values first appear. The result spills down, and it updates when the data changes:
=UNIQUE(B2:B13)
What about capitals?

UNIQUE ignores case: "acme" and "ACME" count as the same, and you get whichever appears first. Extra spaces do count, so clean with TRIM first if needed: =UNIQUE(TRIM(B2:B13)).
What are the other parts?

=UNIQUE(array, [by_col], [exactly_once])
- by_col: TRUE compares columns instead of rows. Leave it FALSE for a normal list.
- exactly_once: TRUE returns only the values that appear once, such as customers who never came back.
Can it work on several columns?

Yes. =UNIQUE(A2:B13) removes rows where both columns repeat: each distinct pair once.
What goes well with it?

Wrap it in SORT for a sorted list, =SORT(UNIQUE(B2:B13)), or put COUNTIF next to it to count how often each value appears. The Table Workspace also has a Remove Duplicates step for whole files.

Know someone fighting with a spreadsheet? Send them this page.