Dynamic arrays Updated November 2026

SORT and SORTBY

SORT(array, [sort_index], [sort_order], [by_col]) returns a range sorted by one of its columns, ascending by default. SORTBY(array, by_array1, [order1], ...) sorts a range by one or more separate arrays, which can be columns outside the range you return. Both spill and update when the data changes.

Syntax

SORT(array, [sort_index], [sort_order], [by_col])   SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2], ...)

Part	What it is
`array`	The range to sort and return.
`sort_index (SORT)`	Which column of array to sort by, counted from 1. Default 1.
`sort_order`	1 ascending (default), -1 descending.
`by_array (SORTBY)`	A column, inside or outside array, with the same number of rows, used as the sort key.

Examples

=SORT(A2:C100, 3, -1)

Result: A2:C100 ordered by the third column, largest first

=SORTBY(A2:B100, B2:B100, -1, A2:A100, 1)

Result: Sorted by B descending, then by A ascending for ties

=SORTBY(A2:A100, D2:D100)

Result: The names in A ordered by the dates in D, without showing the dates

=SORT(UNIQUE(A2:A100))

Result: A distinct list in alphabetical order

Common mistakes

#VALUE!: sort_index is larger than the number of columns, or a SORTBY key has a different number of rows than array.
#SPILL!: the cells where the result should appear are not empty.
The sort looks wrong because numbers stored as text sort as text (10 before 9). Convert the column first.
SORT with several keys is awkward; use SORTBY for multi-column sorts, or SORT with an array constant: =SORT(A2:C100, {2,1}, {1,-1}).

Practice it

The drills on Formuladrill check your answer on live data, free, no account needed.

Start practicing

Questions

What is the difference between SORT and SORTBY?

SORT sorts by a column inside the range you return. SORTBY sorts by any array of the same height, which can be a column you do not return, and takes several keys easily.

How do I sort by multiple columns with a formula?

=SORTBY(A2:C100, B2:B100, 1, C2:C100, -1) sorts by B ascending then C descending.

Can I sort a FILTER result?

Yes, nest them: =SORT(FILTER(A2:C100, C2:C100 > 0), 3, -1).

FILTERHow the Excel FILTER function returns all the rows of a range that meet a condition, with examples for one criterion, AND and OR, a not found value and SORT.
UNIQUEHow the Excel UNIQUE function returns a list without duplicates that spills down the sheet, with examples for a distinct list, a distinct count and a sort.
RANKHow to rank values in Excel with RANK.EQ and RANK.AVG, descending or ascending, with examples, how ties work, a tie-breaker and LARGE for the nth value.
INDEXHow the Excel INDEX function returns the cell at a given row and column of a range, with examples for a single column, a grid and the pairing with MATCH.