SORT and SORTBY in Excel: Sorted Lists That Update
Use SORT and SORTBY in Excel 365 and Google Sheets to create sorted copies of data that update automatically: by one column, descending, or several columns.
By Ayoub Farid · · 1 min read
The Sort button rearranges your data once. The SORT function creates a sorted copy that re-sorts itself whenever the data changes. Great for leaderboards, top-10 lists and dashboards.
Available in Microsoft 365, Excel 2021+, Excel for the web and Google Sheets (where SORT has slightly different arguments).
Syntax (Excel)
=SORT(array, [sort_index], [sort_order], [by_col])- sort_index: which column to sort by (1 = first)
- sort_order: 1 ascending (default), -1 descending
Examples
Sort A2:C50 by the 3rd column, largest first:
=SORT(A2:C50,3,-1)Alphabetical list of names:
=SORT(A2:A50)Sort by several columns
By category (column 2) A–Z, then sales (column 3) high to low:
=SORT(A2:C50,{2,3},{1,-1})SORTBY: sort by a column you don't show
=SORTBY(A2:A50,C2:C50,-1)Names ordered by sales, without showing the sales column.
Top 5
=TAKE(SORT(A2:C50,3,-1),5)TAKE is in Microsoft 365. In Google Sheets use =ARRAY_CONSTRAIN(SORT(A2:C50,3,FALSE),5,3).
Google Sheets syntax
=SORT(A2:C50,3,FALSE)In Sheets, the column number is absolute within the range and the order is TRUE (ascending) or FALSE (descending).
Combine with FILTER and UNIQUE
=SORT(UNIQUE(C2:C200))
=SORT(FILTER(A2:C200,C2:C200="Coffee"),3,-1)Frequently asked questions
How do I sort data with a formula in Excel?
=SORT(range,column,order), e.g. =SORT(A2:C50,3,-1).
What's the difference between SORT and SORTBY?
SORT sorts by a column inside the range; SORTBY can sort by any separate range.
Does SORT change my original data?
No. It creates a sorted copy elsewhere.
Why does SORT give #SPILL!?
The cells where the results should go aren't empty.