Excel SORT and SORTBY: Rank and Reorder Data
By Szabó Gergő · Updated
SORT reorders a range by one or more columns and spills the result. SORTBY can sort one range using the order of another range, which is useful for ranking.
Syntax and arguments
=SORT(array, [sort_index], [sort_order], [by_col])- array
- The range to return in sorted order.
- sort_index
- Column number to sort by, counting from the left of array.
- sort_order
- 1 ascending (default) or -1 descending.
- by_col
- FALSE sorts rows; TRUE sorts columns.
SORT examples
Table is A2:D100 and amount is column 4.
=SORT(A2:D100,4,-1)The largest amount rises to the first spilled row.
Paid rows should appear alphabetically by name.
=SORT(FILTER(A2:C100,C2:C100="Paid"),1,1)FILTER extracts, then SORT orders the remaining names.
Names in A2:A20 and scores in B2:B20.
=SORTBY(A2:A20,B2:B20,-1)Names spill in score order without returning the scores.
Common mistakes
sort_index counts from the worksheet
Count from the left edge of the array you passed to SORT.
Sorting mixed numbers stored as text
Convert with VALUE so 10 does not sort before 2.
Overwriting the source table
Spill SORT onto a report sheet; do not place it inside the same table.
SORT FAQ
Can I sort by two columns?
Yes. =SORT(A2:C100,{1,3},{1,-1}) sorts by column 1 ascending, then column 3 descending.
Is SORT the same as the ribbon Sort button?
Ribbon Sort changes the source. SORT leaves the source alone and spills a live copy.
What is SORTBY for?
When the sort key is not inside the returned array, or when you want to sort by a calculated helper.