Excel practice › Excel functions › SORT
SORT in Excel: how it works, with examples you can edit
SORT returns a sorted copy of a range and leaves the original where it is. To sort a table in A2:C8 by its third column, largest first: =SORT(A2:C8, 3, -1).
You type the formula in one cell and the sorted table spills into the cells next to it. When the data changes, the copy re-sorts by itself. Microsoft lists SORT for Excel 2021, Excel 2024 and Microsoft 365. Excel 2019 and 2016 are not on that list.
Syntax
=SORT(array, [sort_index], [sort_order], [by_col])
array- The range to sort. Give it the whole table if the rows have to stay together.
sort_index- Optional. The column to sort by, as a number counted from the first column of
array. Left out, it is 1. sort_order- Optional.
1= ascending, the default: smallest first, A to Z.-1= descending. by_col- Optional.
FALSE, or nothing, sorts the rows, which is what a normal table needs.TRUEsorts the columns instead.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Item | Type | Sold | Item | Type | Sold | |
| 2 | Brisket Taco | Taco | 312 | Brisket Taco | Taco | 312 | |
| 3 | Veggie Bowl | Bowl | 97 | Carnitas Taco | Taco | 268 | |
| 4 | Carnitas Taco | Taco | 268 | Chicken Bowl | Bowl | 184 | |
| 5 | Chicken Bowl | Bowl | 184 | Churros | Dessert | 141 | |
| 6 | Churros | Dessert | 141 | Elote | Side | 76 | |
| 7 | Horchata | Drink | 229 | Horchata | Drink | 229 | |
| 8 | Elote | Side | 76 | Veggie Bowl | Bowl | 97 |
How Excel reads it
- It takes the rows of A2:C8.
- It orders them by the column that
sort_indexnames. No number means column 1, the item names. sort_ordersets the direction. No number means ascending.- The sorted copy spills from the cell that holds the formula. A2:C8 itself doesn't change.
sort_index is a position inside the range, like the column number of a VLOOKUP. In A2:C8, Sold is column 3. If you sorted B2:C8, the same Sold column would be column 2.
Two mistakes
1. Forgetting the sort order. You want the best sellers on top, you tell SORT to use the Sold column, and the list comes out upside down. Ascending is the default, so the smallest number is first:
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Item | Type | Sold | Item | Type | Sold | |
| 2 | Brisket Taco | Taco | 312 | Elote | Side | 76 | |
| 3 | Veggie Bowl | Bowl | 97 | Veggie Bowl | Bowl | 97 | |
| 4 | Carnitas Taco | Taco | 268 | Churros | Dessert | 141 | |
| 5 | Chicken Bowl | Bowl | 184 | Chicken Bowl | Bowl | 184 | |
| 6 | Churros | Dessert | 141 | Horchata | Drink | 229 | |
| 7 | Horchata | Drink | 229 | Carnitas Taco | Taco | 268 | |
| 8 | Elote | Side | 76 | Brisket Taco | Taco | 312 |
=SORT(A2:C8,3,-1) puts Brisket Taco (312) first.2. Sorting one column on its own. SORT returns what you give it. Hand it the Sold column alone and you get the numbers in order, with nothing to say which item each one belongs to:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Item | Type | Sold | Sold | |
| 2 | Brisket Taco | Taco | 312 | 312 | |
| 3 | Veggie Bowl | Bowl | 97 | 268 | |
| 4 | Carnitas Taco | Taco | 268 | 229 | |
| 5 | Chicken Bowl | Bowl | 184 | 184 | |
| 6 | Churros | Dessert | 141 | 141 | |
| 7 | Horchata | Drink | 229 | 97 | |
| 8 | Elote | Side | 76 | 76 |
sort_index, and -1 sorts largest first. To keep the names with the numbers, sort the whole table and say which column to sort by: =SORT(A2:C8,3,-1).SORT around FILTER
SORT accepts the result of another formula as its range. Filter first, then sort what is left. Here: the tacos only, best seller first.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Item | Type | Sold | Item | Type | Sold | |
| 2 | Brisket Taco | Taco | 312 | Brisket Taco | Taco | 312 | |
| 3 | Veggie Bowl | Bowl | 97 | Carnitas Taco | Taco | 268 | |
| 4 | Carnitas Taco | Taco | 268 | ||||
| 5 | Chicken Bowl | Bowl | 184 | ||||
| 6 | Churros | Dessert | 141 | ||||
| 7 | Horchata | Drink | 229 | ||||
| 8 | Elote | Side | 76 |
Taco to Bowl for the bowls.Practice: 3 exercises
Type the formula in the box, press Enter. Clicking a cell inserts its address. Each answer is checked on the data you see and again on changed data, so a typed-in value won't pass.
In E2, list the item names in column A in alphabetical order, A to Z.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Item | Type | Sold | Items A-Z | |
| 2 | Brisket Taco | Taco | 312 | ||
| 3 | Veggie Bowl | Bowl | 97 | ||
| 4 | Carnitas Taco | Taco | 268 | ||
| 5 | Chicken Bowl | Bowl | 184 | ||
| 6 | Churros | Dessert | 141 | ||
| 7 | Horchata | Drink | 229 | ||
| 8 | Elote | Side | 76 |
Show the answer
=SORT(A2:A8) gives Brisket Taco (7 rows, starting with Brisket Taco).
With only a range, SORT uses the first column in ascending order, which for text is A to Z.
In E2, return the whole table (columns A to C) sorted by Sold, with the largest number on top.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Item | Type | Sold | Item | Type | Sold | |
| 2 | Brisket Taco | Taco | 312 | ||||
| 3 | Veggie Bowl | Bowl | 97 | ||||
| 4 | Carnitas Taco | Taco | 268 | ||||
| 5 | Chicken Bowl | Bowl | 184 | ||||
| 6 | Churros | Dessert | 141 | ||||
| 7 | Horchata | Drink | 229 | ||||
| 8 | Elote | Side | 76 |
Show the answer
=SORT(A2:C8,3,-1) gives Brisket Taco (7 rows, 3 columns, starting with Brisket Taco).
Sold is the 3rd column of A2:C8, and -1 asks for descending order. The rows move as a whole, so each item keeps its type and its number.
In E2, return the full rows of the items that sold more than 150, sorted by Sold with the largest on top. Filter first, then sort the result.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Item | Type | Sold | Item | Type | Sold | |
| 2 | Brisket Taco | Taco | 312 | ||||
| 3 | Veggie Bowl | Bowl | 97 | ||||
| 4 | Carnitas Taco | Taco | 268 | ||||
| 5 | Chicken Bowl | Bowl | 184 | ||||
| 6 | Churros | Dessert | 141 | ||||
| 7 | Horchata | Drink | 229 | ||||
| 8 | Elote | Side | 76 |
Show the answer
=SORT(FILTER(A2:C8,C2:C8>150),3,-1) gives Brisket Taco (4 rows, 3 columns, starting with Brisket Taco).
FILTER returns the four rows over 150. SORT receives that result as its range and orders it by its 3rd column, largest first.
Questions people ask
How do I sort in descending order with the SORT function?
Set the third argument, sort_order, to -1: =SORT(A2:C8,3,-1) sorts by the third column, largest first. To sort a single column descending, skip the second argument with two commas: =SORT(C2:C8,,-1).
Does the SORT function change the original data?
No. SORT returns a sorted copy that spills from the cell with the formula, and the source range stays in its own order.
What is the difference between SORT and SORTBY?
SORT takes the column to sort by as a number counted inside the range. SORTBY takes it as a range. Microsoft's page recommends SORTBY for data on the sheet, because a range reference follows the column when columns are added or deleted, and a column number doesn't. The sheets on this page run SORT only.
Does SORT work in Excel 2019 or 2016?
Microsoft lists SORT for Excel 2021, Excel 2024 and Microsoft 365. Excel 2019 and 2016 are not on that list.