Excel SORT and SORTBY: dynamic sorting for real data

Sort a task list from the ribbon menu, add a row an hour later, and the sort is already stale. Every new entry lands at the bottom, and you re-sort. Then a teammate re-sorts, then the direction flips, then someone hides a column and the whole snapshot tears. SORT and SORTBY, added in Microsoft 365 and Excel 2021, replace that ritual with a formula. The sorted list is a spill; when the source range changes, the spill catches up on the next recalc. Same shape, no click. The only real question is which of the two you should reach for, and when.

SORT vs SORTBY: a two-line rule

The two functions are close cousins, but they answer different questions. Reach for SORT when the column you want to sort by is already part of the array you are returning — a price column inside a product table, a date column inside a log. Reach for SORTBY when the sort key lives somewhere else, or when you want to sort by a value you do not want to display.

The one-line test

Ask this: “Is the thing I am sorting by one of the columns I am about to show?” Yes means SORT. No — or “I want to hide it” — means SORTBY. The comparison below is the whole decision surface, and it holds for every sort you will write.

Both spill their result as a dynamic array, and both re-run on every recalc — so once you write the formula, the sort takes care of itself.

SORT for one clean pass

SORT takes an array and returns it in order. The signature is short:

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

array is the range you want sorted. sort_index is which column (or row) to sort by, counting from 1. sort_order is 1 for ascending (the default) or -1 for descending. by_col is TRUE only when your data runs left-to-right instead of top-to-bottom; leave it off almost always. Microsoft’s SORT function reference covers the same shape.

Sort by a different column

Say the sheet holds products, prices, and stock. You want the list ordered by price, most expensive first. The source lives in A1:C6:

Drop this in E2 and let it spill:

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

Column 2 is Price. -1 means descending. Monitor pops to the top, Keyboard drops to fourth. Add a row for a new product, and the spill grows and re-sorts on the next recalc. No menu, no click.

SORTBY when the sort key lives elsewhere

SORTBY keeps the sort logic and the returned data as two separate arguments. That is the whole point: the thing you display is not tied to the thing you sort by.

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

Suppose column A holds candidate names and column B holds a private interview score you do not want to publish. To return names ranked by score, high to low, without exposing the scores:

=SORTBY(A2:A11, B2:B11, -1)

The spill is one column of names. The score column drives the order and never appears in the output — impossible with plain SORT, which can only sort a column that is part of what it returns.

Multiple keys with different directions

Real sorts are almost never one-key. You want region ascending, then age descending, and you want them driven by the same live source. SORTBY stacks pairs left to right and applies them in order:

=SORTBY(A2:C11, C2:C11, 1, B2:B11, -1)

Region (column C) sorts first, ascending. Inside each region, age (column B) sorts descending. You can keep adding pairs, but three keys is usually the ceiling before the intent gets murky.

Custom order sorting with SORTBY and MATCH

Alphabetic order is useless for values like High, Medium, Low — sorted as text they come out High, Low, Medium, which is neither the order you meant nor the order anyone reads. The fix is to sort by the position of each value inside a custom order list, and MATCH supplies that position.

=SORTBY(A2:B11, MATCH(B2:B11, {"High";"Medium";"Low"}, 0))

MATCH looks up each priority label inside the array constant and returns 1, 2, or 3. SORTBY then sorts on that numeric result, ascending, and the visible column stays in words. Curly braces with semicolons build a vertical array literal; commas would build a horizontal one, which MATCH would reject here.

The same trick for weekdays

Weekday text sorts even worse than priority. You want Mon before Tue before Wed, not Fri before Mon before Sat. Same pattern:

=SORTBY(A2:C50, MATCH(B2:B50, {"Mon";"Tue";"Wed";"Thu";"Fri";"Sat";"Sun"}, 0))

The array constant is the taxonomy; MATCH turns it into ranks; SORTBY sorts on ranks. If the labels are stored on the sheet — say a hidden lookup range in Z2:Z8 — swap the constant for the reference and the pattern still holds. This is the recipe every custom-order sort in Excel reduces to. For a longer walkthrough of the surrounding function family, see our tour of Excel functions from basic to advanced.

Keep the header row and auto-expand with tables

Two problems SORT alone will not solve show up the first time you use it for real. First, the spill contains only data — the header row is gone, because you pointed the formula at the data below the header. Second, the range is fixed: add a row past row 100 and the sort ignores it.

Point the formula at a Table

VSTACK fixes the header. Convert the source to a Table (Ctrl+T) and the range fixes itself. Combine both and the sort becomes maintenance-free.

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

=VSTACK(tbl_Sales[#Headers], SORT(tbl_Sales, 2, -1))

The Table reference does two useful things. It absorbs new rows without any range edit, and it shrugs off column insertions inside the Table because the structured reference tracks the column by name, not by position. Nested with VSTACK, the sorted output looks and behaves like a real table view. Pair this with a FILTER wrapper — see our FILTER with multiple criteria walkthrough — and you have a live view that sorts and filters at the same time.

The two errors you will actually hit

Every dynamic array function has a specific pair of failure modes, and for SORT and SORTBY they are the same two, over and over.

#SPILL! means Excel wants to write the result into cells that already hold something — a value, another formula, even a stray space. The cure is to clear the block below and to the right of the formula cell until nothing blocks it. Merged cells inside the spill area also trigger this, and they will not clear until you unmerge them.

#VALUE! from SORTBY almost always means by_array1 and array have different lengths. If array is A2:C11 (ten rows), every by_array must also be ten rows tall. Off-by-one is the common shape — B2:B10 against a ten-row array — and Excel does not point at the argument that broke.

Cross-workbook links break silently

SORT and SORTBY both return #REF! when the source lives in another workbook and that workbook is closed. The formula worked yesterday; today the linked file is closed, and every spill turns red. Keep the source in the same file, or wrap the whole thing in a query that materializes the data locally. For the general dynamic-array failure playbook — including the merged-cell trap that catches everyone once — see our fix guide for the SPILL error.

One decision, one setup, done

Reach for SORT when the sort column is part of the data you are returning. Reach for SORTBY when the sort key lives elsewhere — a hidden score, a custom priority list, or several keys pulling in different directions. Wrap the result in VSTACK to keep the header row, and point it at a Table so new rows fold in without you touching the formula.

• ✓ Sort column is inside the returned array → SORT
• ✓ Sort key is separate or should stay hidden → SORTBY
• ✓ Custom order (priority, weekday) → SORTBY + MATCH
• ✓ Header row and future rows → VSTACK + Table reference