SORT
Shows a copy of a table sorted by the column you need: a ranking of sales reps by sales, products by price.
SORT(array, [sort_index], [sort_order], [by_col])- [ ]optional part
- array
- The table without headers to sort.
- sort_index
- The number of the column inside the table to sort by. If left out, the table is sorted by the first column.Optional
- sort_order
- 1 or left out: ascending. -1: descending.Optional
- by_col
- FALSE or left out: reorder rows. TRUE: reorder columns. A beginner almost always needs the first option.Optional
Return value
The same values in sorted order. They spill into the neighboring cells; if those are taken, you get #SPILL!.
Examples
Sales rep ranking by sales
Details
SORT takes a table and returns a sorted copy of it; the source data stays as it was. You can set which column to sort by and in which order: 1 for ascending, -1 for descending.
You write the formula in one cell, and the result spills down and to the right to the size of the table. The cells below the formula must be empty, or you get #SPILL!. If the source numbers change, the order recalculates by itself.
The function is available in Excel 2021 and Microsoft 365. Excel 2019 and earlier don't have it, and the formula shows #NAME? there.
Common mistakes
- There is data below or to the right of the formula. The result has nowhere to spill, and #SPILL! appears.
- The header is included in the range. It gets sorted along with the data and ends up in the middle of the list.
- The column number is counted from the start of the sheet. Count from the first column of the table: in A2:B6, column B is the second one.
In Google Sheets
In Google Sheets, SORT sets the order with TRUE (ascending) or FALSE (descending) instead of 1 and -1.