The SORT function
A sorted copy of a table, which sorts itself again when the source changes. Data sorting sorts once; SORT sorts always.
Syntax
=SORT(array, [sort_index], [sort_order], [by_col])
In a French Excel: =TRIER(tableau; [index_tri]; [ordre_tri]; [par_col]).
Arguments
- array: the range to sort.
- sort_index: the number of the sort column; 1 by default.
- sort_order: 1 ascending, -1 descending.
- by_col: TRUE to sort columns.
An example
=SORT(A2:D100,4,-1)
The table sorted on its 4th column, largest first.
Pitfalls
- Occupied cells where the result must spill: #SPILL!.
- Missing from Excel 2019 and earlier.
Going further
- UNIQUE, FILTER.
- SQL queries: ORDER BY on a whole file.
Frequently asked questions
- How do I sort on two columns?
=SORT(A2:D100,{2,4},{1,-1}): the 2nd column ascending, then the 4th descending.