SORT and UNIQUE practice exercises

Sort a table by any column and pull out a list without duplicates, with formulas that update the moment the data changes.

Start the first exercise

How SORT and UNIQUE work

SORT and UNIQUE return a whole list or table that updates on its own when the data changes. SORT takes the range, the column number to sort by and the order: 1 for ascending, -1 for descending. UNIQUE takes a range and shows each value once, without the repeats. Both work in Excel 365, Excel 2021 and Excel for the web, but not in Excel 2019 or older.

=SORT(A2:B6,2,-1) returns the table A2:B6 sorted by its second column, so the biggest sale comes first. The result spills into the cells below and to the right, so they must be empty. =UNIQUE(B2:B8) lists each customer once, and =SORT(UNIQUE(B2:B9)) gives each rep once, from A to Z.

The sorted result is a copy, so your original table stays as it was. If you want to reorder the table itself, use the Sort button on the Data tab instead. UNIQUE ignores upper and lower case, so Anna and anna count as the same value. The exercises below cover sorting a table by sales, listing each customer once and putting UNIQUE inside SORT.

  1. 1Sort a table by sales with SORTIn D2, return the table A2:B6 sorted by sales, highest first. SORT takes the range, the column to sort by (2) and the order (-1 for descending).
  2. 2List each customer once with UNIQUEPremiumIn D2, list every customer in B2:B8 once, without the repeats, with UNIQUE.
  3. 3A sorted list without duplicatesPremiumIn D2, list every rep in B2:B9 once, sorted from A to Z. Put UNIQUE inside SORT.

Functions in this topic

Read the guide

Questions

Why does SORT or UNIQUE show a #SPILL! error?

Something is blocking the cells where the result needs to go. Click the cell with the error, open the warning icon and choose Select Obstructing Cells to see what is in the way, then clear it. A spilled result also can't sit inside an Excel table, so use a normal range next to the table instead.

What is the difference between SORT and SORTBY?

SORTBY sorts by a range you point to, so that range doesn't have to be part of the result. =SORTBY(A2:B6,B2:B6,-1) gives the same as =SORT(A2:B6,2,-1). But =SORTBY(A2:A6,B2:B6,-1) returns only the names, ordered by sales. Use SORT when you sort by a column you return.

How do I count unique values in Excel?

Put UNIQUE inside COUNTA: =COUNTA(UNIQUE(B2:B8)) counts each customer once. Keep the range free of blank cells, because a blank is returned as a value and gets counted too. Without Excel 365 or 2021, you can't use UNIQUE, and you need a SUMPRODUCT formula or a PivotTable instead.