What it does
SORT and SORTBY return sorted dynamic arrays.
Syntax or pattern
=SORT(array, [sort_index], [sort_order]) / =SORTBY(array, by_array, [sort_order])✅
5 practical examples
1
Sort names alphabetically
Sort a list from A to Z.
=SORT(A2:A100)Simple dynamic sorting.
2
Sort table by sales
Sort a table by the third column descending.
=SORT(Sales,3,-1)-1 sorts descending.
3
Sort by another column
Sort products by sales amount.
=SORTBY(Products,Products[Sales],-1)SORTBY can sort by a separate array.
4
Sort filtered rows
Filter and sort East sales.
=SORT(FILTER(Sales,Sales[Region]="East"),3,-1)Dynamic array functions combine well.
5
Sort by two columns
Sort by region then sales.
=SORTBY(Sales,Sales[Region],1,Sales[Amount],-1)Use multiple sort arrays.
Common mistakes to avoid
- Make sure spill ranges have empty space below or beside them.
- Use Excel Tables where possible for expanding source data.
- Older Excel versions may not support all dynamic array functions.
Related Excel examples
FAQ
What is a spill formula?
A spill formula returns multiple cells from one formula entered in a single cell.