The SORT function sorts a range and returns the result in a new place, so your original data stays untouched. When someone adds a row, the sorted list updates by itself. This is the big difference from the Sort button on the ribbon.
Syntax
=SORT(array, [sort_index], [sort_order], [by_col])
- sort_index: which column to sort by, as a number (1 is the first column of the array).
- sort_order: 1 for ascending, -1 for descending.
Sort a table by sales, highest first
With a table in A2:C100 and sales in column 3:
=SORT(A2:C100, 3, -1)
The whole table spills out with the biggest sale at the top.
Top 5 only
Combine SORT with TAKE (Microsoft 365) to show only the top rows:
=TAKE(SORT(A2:C100,3,-1),5)
In older versions use INDEX with SEQUENCE, or the LARGE function.
Sort by more than one column
Use SORTBY. This example sorts by region A to Z, then by sales high to low:
=SORTBY(A2:C100, A2:A100, 1, C2:C100, -1)
Sort and remove duplicates together
=SORT(UNIQUE(A2:A500))
This gives an alphabetical drop-down source list that updates itself.
Common mistakes
- If the cells below the formula are not empty, you get #SPILL!.
- sort_index is a position inside the range, not the column letter. Sorting A2:C100 by column C means index 3.
- Text numbers, such as "100" stored as text, sort before real numbers. Convert them first.