SORT vs SORTBY in Excel: Which Dynamic Sort

The short answer to SORT vs SORTBY in Excel: SORT orders a range by one of its own columns, which you choose by number, while SORTBY orders a range by any other range you point to, even one you don't return. Both create a live, sorted copy that updates when the data changes. This comparison is for anyone using Excel 2021 or Microsoft 365 who wants sorted lists, leaderboards or top-N reports without clicking the Sort button every time.

SORT vs SORTBY at a glance#

SORT is shorter for simple sorts; SORTBY is more flexible and easier to read for anything more.

Spilling means a formula in one cell fills the cells next to it with results. Both functions spill, so leave empty space below and to the right, or you get a #SPILL! error.

How SORT works#

SORT takes a range and returns it sorted by one of its columns.

=SORT(A2:C6)
=SORT(A2:C6,2,-1)

The first sorts by the first column, A to Z. The second sorts by column 2 of the range, with -1 meaning descending (largest first). Use 1 or leave the order out for ascending.

The sort column is a position inside the range, not a column letter. In A2:C6, 2 means column B. If the range were B2:D6, 2 would mean column C. The SORT function guide explains every argument.

How SORTBY works#

SORTBY takes the range to return, then one or more pairs of "sort by this range, in this order".

=SORTBY(A2:C6,B2:B6,-1)
=SORTBY(A2:A6,B2:B6,-1)

The first returns the whole table sorted by score, highest first, just like the SORT example. The second returns only the names, still ordered by score. The score column is used for sorting but isn't shown. SORT can't do that, because it can only sort by a column inside the range it returns.

The sort-by range must have the same number of rows as the range you return, or SORTBY returns #VALUE!.

The same sorts done both ways#

Here is a small team scoreboard:

Task 1: whole table by score, highest first#

=SORT(A2:C6,2,-1)
=SORTBY(A2:C6,B2:B6,-1)

Both return Tom 95, Mei 91, Kofi 88, Priya 82, Lena 77, with their teams. For one sort level, SORT is a little shorter.

Task 2: names only, by score#

=SORTBY(A2:A6,B2:B6,-1)

This spills Tom, Mei, Kofi, Priya, Lena into one column. With SORT, you would have to sort the whole table and then pick out the names with another function.

Task 3: by team, then by score#

=SORT(A2:C6,{3,2},{1,-1})
=SORTBY(A2:C6,C2:C6,1,B2:B6,-1)

Both return Blue first (Tom 95, Kofi 88), then Red (Mei 91, Priya 82, Lena 77). The SORT version packs the column numbers and orders into curly-bracket lists, which is easy to mistype. The SORTBY version reads like a sentence: by team ascending, then by score descending. The guide on sorting by multiple columns has more examples.

Task 4: the top scorer only#

=INDEX(SORTBY(A2:A6,B2:B6,-1),1)

This returns Tom. INDEX takes the first item from the sorted list. Change 1 to 2 for the runner-up.

Task 5: one team, sorted, and a top three#

Both functions combine well with FILTER. To list only the Red team, highest score first:

=SORT(FILTER(A2:C6,C2:C6="Red"),2,-1)

This returns Mei 91, Priya 82 and Lena 77. FILTER keeps the Red rows, then SORT orders them by the second column. The SORTBY version needs the filter twice, once for the data and once for the sort key, so SORT is the neater choice here.

For a top three in Microsoft 365 or Excel 2024, TAKE keeps the first rows of a sorted result:

=TAKE(SORTBY(A2:B6,B2:B6,-1),3)

This spills Tom 95, Mei 91 and Kofi 88. In Excel 2021, which has no TAKE, use =INDEX(SORTBY(A2:B6,B2:B6,-1),SEQUENCE(3),{1,2}) for the same result.

When to use SORT#

Use SORT when you want a simple, one-level sort of a whole table. It fits:

• Alphabetical lists: =SORT(A2:A50) for a sorted list of names.
• A whole table by one column, such as products by price.
• Combining with other dynamic functions, such as =SORT(UNIQUE(A2:A50)) for a sorted list of unique values.
• Sorting across columns with the by_col argument, for data laid out in rows.
• Sorting the result of another formula, such as a FILTER, where the columns are already known and a position number is quicker than repeating a range.
• Drop-down source lists, where a sorted, spilled list of options keeps a data validation list in order as new items are added.

When to use SORTBY#

Use SORTBY whenever the sort needs more than one level or a column you don't want to show. It fits:

• Multi-level sorts, such as region, then sales.
• Leaderboards that show names only, sorted by a score you leave out.
• Sorting by a calculation, such as name length: =SORTBY(A2:A6,LEN(A2:A6)).
• Files where columns may be inserted later, because references adjust and position numbers don't.
• Custom orders, such as High, Medium, Low, using MATCH against a list. See custom sort orders.

The comparison of SORT and SORTBY covers more patterns, and the Sort button vs the SORT function explains when a one-off manual sort is better.

Common mistakes with SORT and SORTBY#

1. Using a column letter as sort_index. =SORT(A2:C6,"B") doesn't work. Use the position number, 2.
2. Mismatched sizes in SORTBY. The sort-by range must have the same number of rows as the returned range, or you get #VALUE!.
3. Including the header row. =SORT(A1:C6) sorts the header in with the data. Start the range on the first data row.
4. Blocking the spill range. Anything typed below or beside the formula causes #SPILL!. Clear the cells.
5. Forgetting -1 for descending. Both functions sort ascending unless you ask for -1.
6. Using them in Excel 2019 or earlier. The formula shows #NAME?. Use the Sort button there, as explained in how to sort data in Excel.

Our recommendation#

Use SORT for quick, one-level sorts of a whole table, where its short syntax is an advantage. Use SORTBY for everything else: multi-level sorts, results that leave out the sort column, sorting by a calculation, and any file where columns might move. If you only want to learn one, learn SORTBY, because it can do everything SORT does and its references are easier to read and check.

In short: SORT for simple sorts, SORTBY for anything with more than one level or a hidden key.

Practise it#

Try the exercise on this page: find the top scorer with SORTBY and INDEX. Then practise the SORT function in a live spreadsheet: you type each formula, and it checks your answer instantly.