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.

Skip to the 3 exercises ↓

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. TRUE sorts the columns instead.
Live exampleThe whole table, A to Z by item
E2fx
ABCDEFG
1ItemTypeSoldItemTypeSold
2Brisket TacoTaco312Brisket TacoTaco312
3Veggie BowlBowl97Carnitas TacoTaco268
4Carnitas TacoTaco268Chicken BowlBowl184
5Chicken BowlBowl184ChurrosDessert141
6ChurrosDessert141EloteSide76
7HorchataDrink229HorchataDrink229
8EloteSide76Veggie BowlBowl97

How Excel reads it

  1. It takes the rows of A2:C8.
  2. It orders them by the column that sort_index names. No number means column 1, the item names.
  3. sort_order sets the direction. No number means ascending.
  4. 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:

Live exampleSorted by Sold, but smallest first
E2fx
ABCDEFG
1ItemTypeSoldItemTypeSold
2Brisket TacoTaco312EloteSide76
3Veggie BowlBowl97Veggie BowlBowl97
4Carnitas TacoTaco268ChurrosDessert141
5Chicken BowlBowl184Chicken BowlBowl184
6ChurrosDessert141HorchataDrink229
7HorchataDrink229Carnitas TacoTaco268
8EloteSide76Brisket TacoTaco312

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:

Live exampleNumbers without their names
E2fx
ABCDE
1ItemTypeSoldSold
2Brisket TacoTaco312312
3Veggie BowlBowl97268
4Carnitas TacoTaco268229
5Chicken BowlBowl184184
6ChurrosDessert141141
7HorchataDrink22997
8EloteSide7676

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.

Live exampleFilter, then sort
E2fx
ABCDEFG
1ItemTypeSoldItemTypeSold
2Brisket TacoTaco312Brisket TacoTaco312
3Veggie BowlBowl97Carnitas TacoTaco268
4Carnitas TacoTaco268
5Chicken BowlBowl184
6ChurrosDessert141
7HorchataDrink229
8EloteSide76

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.

Exercise 1A list in alphabetical order

In E2, list the item names in column A in alphabetical order, A to Z.

E2fx
ABCDE
1ItemTypeSoldItems A-Z
2Brisket TacoTaco312
3Veggie BowlBowl97
4Carnitas TacoTaco268
5Chicken BowlBowl184
6ChurrosDessert141
7HorchataDrink229
8EloteSide76
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.

Exercise 2Best sellers first

In E2, return the whole table (columns A to C) sorted by Sold, with the largest number on top.

E2fx
ABCDEFG
1ItemTypeSoldItemTypeSold
2Brisket TacoTaco312
3Veggie BowlBowl97
4Carnitas TacoTaco268
5Chicken BowlBowl184
6ChurrosDessert141
7HorchataDrink229
8EloteSide76
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.

Exercise 3Filter, then sort

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.

E2fx
ABCDEFG
1ItemTypeSoldItemTypeSold
2Brisket TacoTaco312
3Veggie BowlBowl97
4Carnitas TacoTaco268
5Chicken BowlBowl184
6ChurrosDessert141
7HorchataDrink229
8EloteSide76
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.

Keep going

Lesson 10 is nine drills on a support desk's ticket log: UNIQUE, a blocked spill to clear, SORT, FILTER with one and with several conditions, the empty result, and a summary table built on a spilled list with #. It's part of Pro ($29 once for everything, or $19 for the Excel track). Lessons 1 and 2 and the level test are free.

Related

FILTER function, with live examplesUNIQUE function, with live examplesVLOOKUP function, with live examples#SPILL! error in Excel: meaning and fixesExcel FILTER, SORT, UNIQUE practice (Pro)Excel functions with live examplesExcel practice online

Written and tested by Nico, who builds SheetDrill (about). Every formula on this page ran through the engine that checks the exercises before the page was published. Checked against: Microsoft Support: SORT function. Last checked November 2026.