Dynamic arrays function
Excel SORTBY function
SORTBY returns a sorted copy of a range, ordered by one or more other lists. Unlike SORT, the sort column does not have to be part of the result, and each key gets its own order, so a team list sorted by team and then by score is one formula.
=SORTBY(array, by_array1, [sort_order1], ...)Arguments
array- The range or array to sort and return.
by_array1- The list to sort by: one column as tall as array (or one row as wide, to sort columns).
sort_order1- Optional. 1 ascending (default) or -1 descending.
SORTBY examples
Every result below was calculated from this sample sheet by the same spreadsheet engine the Cellwise app uses.
| A | B | C | |
|---|---|---|---|
| 1 | Name | Team | Score |
| 2 | Ava | Blue | 88 |
| 3 | Ben | Red | 92 |
| 4 | Cara | Blue | 92 |
| 5 | Dev | Red | 75 |
| 6 | Eli | Blue | 81 |
| 7 | Fay | Red | 88 |
Download this sheet as an Excel practice file (.xlsx)
Result: spills 6 rows × 1 column
Ben Cara Ava Fay Eli Dev Only the names spill, highest score first, even though the scores are not in the result.
Result: spills 6 rows × 3 columns
Cara Blue 92 Ava Blue 88 Eli Blue 81 Ben Red 92 Fay Red 88 Dev Red 75 Two keys: team A to Z, then score high to low inside each team.
Result: Ben
The first name of the sorted list is the top scorer: Ben.
Result: #VALUE!
The order must be 1 or -1. Anything else is #VALUE!.
How to use SORTBY
- Click the top-left cell of an empty area.
- Type =SORTBY( and select the data to return, without the header row.
- Add a comma, the column to sort by, a comma and 1 or -1.
- Add more column, order pairs to break ties, close the bracket and press Enter.
Common SORTBY mistakes
A sort column of a different height
Every by_array must have exactly as many rows as array. SORTBY(A2:A7,C2:C6) is #VALUE!. Select both from the same first and last row.
Including the header row
The header gets sorted in with the data. Select A2:C7, not A1:C7, and type the headers above the formula.
Cells in the way
The result needs empty cells to spill into. Anything typed there gives #SPILL!.
SORTBY FAQ
What is the difference between SORT and SORTBY?
SORT sorts by a column number inside the range you return. SORTBY sorts by separate ranges, so you can sort names by a score column you do not show, and give every key its own order.
How do I sort by two columns with a formula?
Give SORTBY two keys: =SORTBY(A2:C7,B2:B7,1,C2:C7,-1) sorts by team, then by score within each team.
How do I sort a list by a column that is not in the result?
Use SORTBY with that column as the key: =SORTBY(A2:A7,C2:C7,-1) returns only names, sorted by score.
Which Excel versions have SORTBY?
Microsoft 365, Excel 2021 and later, and Excel for the web. Older versions show #NAME?.
Guides that use SORTBY
Related functions
SORTSorts a range, spillsFILTERKeeps matching rows, spillsUNIQUERemoves duplicates, spillsCHOOSECOLSPicks columns, spills
Updated