SORT and SORTBY in Excel

The Excel SORT function returns a sorted copy of a range, and SORTBY sorts a range by the values in another one. Unlike Data > Sort, both are formulas: the original data stays as it is, and the sorted copy updates when the data changes. This guide is for anyone building leaderboards, top-seller lists or tidy reports. SORT and SORTBY need Excel 2021, Excel 2024 or Microsoft 365.

How the Excel SORT function works#

SORT takes a range and returns it in order. With only the range, it sorts by the first column, A to Z.

=SORT(array, [sort_index], [sort_order], [by_col])

• array is the data to sort, without its header row.
• sort_index is which column of the array to sort by, counted from the array's left edge. The default is 1.
• sort_order is 1 for smallest first (A to Z, the default) or -1 for largest first (Z to A).
• by_col is optional and rarely needed: TRUE sorts columns left to right instead of rows.

Like FILTER and UNIQUE, SORT is a dynamic array function: you type it in one cell and the results spill into the cells around it. If those cells aren't empty, you see #SPILL!; see our guide to the #SPILL! error.

Here is a product list:

In E2, =SORT(A2:C9) returns all eight rows in alphabetical order by product, from Cold Brew to Trail Mix, with each product's category and units beside it.

How to sort by another column, largest first#

To put the best sellers at the top, sort by Units, the 3rd column of the range, with -1:

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

The result starts with Sparkling Water (610), Cold Brew (512) and Rice Crackers (421), and ends with Dark Chocolate (198).

The same -1 works for text. =SORT(A2:A9,1,-1) lists the product names from Z to A, starting with Trail Mix and ending with Cold Brew.

sort_index counts columns inside the range, not on the sheet. If you sort only B2:C9, Units is column C on the sheet but the 2nd column of the range, so the formula is =SORT(B2:C9,2,-1).

How SORTBY works#

SORTBY sorts one range by the values in another range of the same height.

=SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2], ...)

• array is what you want to see.
• by_array1 is what decides the order.
• sort_order1 is 1 or -1, as in SORT.

The big difference is that the column you sort by doesn't have to be in the result. To list only the product names, best sellers first:

=SORTBY(A2:A9,C2:C9,-1)

This returns Sparkling Water, Cold Brew, Rice Crackers, Oat Milk, Trail Mix, Granola Bar, Green Tea, Dark Chocolate, without showing the units. With SORT, the units column would have to be part of the range.

How to sort by two columns#

SORTBY takes as many by_array and sort_order pairs as you need. This sorts by category A to Z, then by units from highest to lowest inside each category:

=SORTBY(A2:C9,B2:B9,1,C2:C9,-1)

The Drinks come first (Sparkling Water 610, Cold Brew 512, Oat Milk 389, Green Tea 233), then the Snacks (Rice Crackers 421, Trail Mix 340, Granola Bar 275, Dark Chocolate 198). This is the same as a two-level sort in Data > Sort, but it stays up to date. If a product's units change, it moves to its new place by itself, which is exactly what you want for a leaderboard or a weekly top-sellers report. Add a third pair to break any remaining ties, such as by product name.

SORT vs SORTBY: which should you use?#

Use SORT for a quick sort by one column of the data you're returning. Use SORTBY when the sort column isn't in the result, or when you need more than one sort level.

A range is also safer than a number: if someone inserts a column, SORTBY's range moves with it, while SORT's 3 stays a 3. The same idea is behind lookups that use MATCH or XMATCH instead of typed column numbers; see MATCH vs XMATCH.

How to combine SORT with FILTER and UNIQUE#

SORT and SORTBY are often the outer layer of a longer formula. Excel works out the inside function first, then sorts its result.

• Sorted filter results: =SORT(FILTER(A2:C9,B2:B9="Snacks"),3,-1) shows only the Snacks, best sellers first: Rice Crackers, Trail Mix, Granola Bar, Dark Chocolate. See the FILTER function for the conditions, or the FILTER function guide for a quick reference.
• A sorted list of categories: =SORT(UNIQUE(B2:B9)) returns Drinks, Snacks, each once. See the UNIQUE function.
• The top item only: =INDEX(SORT(A2:C9,3,-1),1,1) returns Sparkling Water, row 1 and column 1 of the sorted table.
• Count the sorted list: if your SORTBY spills from E2, =ROWS(E2#) counts its rows. The # is a spill reference: it means "everything E2 spilled", and it grows with the list.

Common problems with SORT and SORTBY#

Most problems come from the spill, the arguments or the data:

1. #SPILL!: something is in the way of the result. Clear the cells below and to the right, or move the formula.
2. #VALUE! from sort_order. It must be exactly 1 or -1. A 0 or 2 returns #VALUE!.
3. Counting sort_index from column A of the sheet. Count from the first column of your range.
4. Ranges of different heights in SORTBY. =SORTBY(A2:A9,C2:C10,-1) returns #VALUE!. Make them match.
5. Numbers stored as text. Text numbers sort as text, so "1000" can land before "250". Convert them to real numbers first.
6. Including the header row. =SORT(A1:C9) sorts "Product" in with the data. Start on row 2 and type the headers above the result.
7. #NAME? in older Excel. Excel 2019 and earlier don't have these functions; use Data > Sort there.

Practise it#

Try the SORTBY exercise on this page: the names come out best seller first, without the units column. Then practise SORT in a live spreadsheet and continue with modern Excel functions such as SORTBY, XMATCH and spill references.