SheetsLessons
Lesson 34 · Arrays & Filtering

SORT & UNIQUE

Practice SORT and UNIQUE in Google Sheets: remove duplicates, sort by one or two columns, and build a sorted list of distinct values that keeps itself up to date.

Task 1 of 5

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.

Sorted and de-duplicated lists that stay live

UNIQUE returns the rows of a range with repeats removed, in the order they first appear. SORT returns a range sorted by the columns you choose. Both spill, and both leave the original data alone.

=UNIQUE(range)
=SORT(range, sort_column, is_ascending, [sort_column2, is_ascending2, ...])
  • sort_column counts from the first column of the range, not from column A of the sheet. In SORT(C2:E9, 2, FALSE), 2 means column D.
  • is_ascending is TRUE for A→Z and smallest first, FALSE for Z→A and largest first.
  • Add more column/direction pairs to break ties: sort by department, then by salary within each department.
  • Nest them: =SORT(UNIQUE(B2:B11)) gives an alphabetical list of distinct values. With only a range, SORT sorts by the first column, A→Z.

UNIQUE compares text exactly, so "acme" and "Acme" count as two different values. Clean the data with TRIM, PROPER or LOWER first if the duplicates differ only in spacing or capitals.