SheetsLessons
Filter & Sort function

UNIQUE function in Google Sheets

UNIQUE returns the rows of a range with duplicates removed, in the order each one first appears. Use it to list the distinct customers, regions or products in a column, count how many there are, or feed a dropdown that grows as new values arrive.

Syntax

=UNIQUE(range, [by_column], [exactly_once])
range
The data to de-duplicate. With several columns, a row only counts as a duplicate if every column matches.
by_column
Optional, FALSE by default. TRUE compares columns instead of rows.
exactly_once
Optional, FALSE by default. TRUE returns only the values that appear exactly once, leaving out anything that repeats.

Practice UNIQUE

Task 1 of 2

Several clients ordered more than once. List each client once, starting in E2.

E2
fx

Tip: while typing a formula, click a cell or drag across cells to insert references. Press Enter to check, and Tab to accept a function suggestion.

UNIQUE examples

Each example below runs against this sample sheet. The results are calculated by the same engine as the practice grid.

ABCDE
1RepRegionProductUnitsRevenue
2AvaWestMonitor12$2,400.00
3LiamEastKeyboard30$1,500.00
4MayaWestMouse45$900.00
5NoahNorthMonitor8$1,600.00
6ZaraEastMonitor15$3,000.00
7OmarWestKeyboard22$1,100.00
Sales by rep
FormulaResultWhat it shows
=UNIQUE(B2:B7)
West
East
North
Each region once, in order of first appearance.
=SORT(UNIQUE(C2:C7))
Keyboard
Monitor
Mouse
Distinct products, alphabetical.
=COUNTA(UNIQUE(B2:B7))3How many different regions there are.
=UNIQUE(B2:C7)
WestMonitor
EastKeyboard
WestMouse
NorthMonitor
EastMonitor
WestKeyboard
Unique region and product pairs (here every pair is different).
=UNIQUE(B2:B7, FALSE, TRUE)NorthOnly the regions that appear exactly once.

Tips and common mistakes

  • UNIQUE is case-sensitive: "Acme" and "acme" are kept as two values. Normalise first with ARRAYFORMULA(PROPER(...)) or LOWER if that's a problem.
  • Stray spaces also create "duplicates" that look identical. Clean the column with TRIM first.
  • Wrap it in SORT for an alphabetical list, and in COUNTA to count distinct values.
  • With an open-ended range like B2:B, the empty cells below your data can add a blank entry to the list. Use UNIQUE(FILTER(B2:B, B2:B<>"")) to leave it out.

Related functions

SORTSorts the rows of a range by one or more columns.FILTERReturns only the rows of a range that meet every condition.COUNTACounts cells that are not empty.COUNTIFCounts cells in a range that meet one condition.

Learning step by step? UNIQUE is covered in Lesson 34: SORT & UNIQUE of the free course.