Excel SORT Function

SORT takes a range or array and returns it in a new order, ascending or descending, without disturbing the original data. It's a dynamic array function, so the result spills into as many cells as it needs. The one thing that trips people up: SORT never sorts data in place. It always writes a fresh, sorted copy somewhere else on the sheet.

Available in: Excel 365, Excel 2021. Not available in Excel 2019 or earlier.

Syntax

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

Parameter	Required	Description
array	Yes	The range or array to sort. Leave headers out. SORT treats every row you feed it as data, header included.
sort_index	No	Which row or column to sort by, counted from 1 inside your array. Defaults to 1, meaning the first column (or first row, if by_col is TRUE).
sort_order	No	1 for ascending, -1 for descending. Defaults to 1.
by_col	No	TRUE sorts left to right across columns. FALSE, the default, sorts top to bottom down rows.

Basic Example

You're building a sales leaderboard from a named range called sales_data, three columns wide: Rep, Region, and Sales.

=SORT(sales_data, 3, -1)

// sales_data = the range B2:D15 (Rep, Region, Sales)
// 3           = sort by the third column, Sales
// -1          = descending, highest sales first

SORT reads all 14 rows of sales_data, ranks them by the value in column 3, and spills the entire three-column result starting at the cell where you typed the formula. Add or edit a sales number in the source range and the leaderboard reorders itself. It won't include a new rep added below row 15, though. That range reference has to be updated (or converted to a table) to pick up new rows.

How SORT Works

The output is a live spill, not a static formula

Type SORT into one cell and Excel fills the whole result range for you. You can't edit or delete an individual cell inside that range. Try it, and Excel blocks you with "You can't change part of a spill." Select the top-left cell and delete the whole formula instead.

Sorting by more than one column

sort_index and sort_order both accept array constants, so you can rank by one column and break ties with another in a single formula:

=SORT(sales_data, {2,3}, {1,-1})

// {2,3}   = sort by Region first, then Sales
// {1,-1}  = Region ascending, Sales descending within each region

SORT is a stable sort. Rows that tie on every sort key keep their original relative order, so you never get random reshuffling on identical values.

Headers are never part of the argument

If you feed SORT a range that includes your header row, it sorts the header along with the data, since it has no way to know a header from a value. Reference only the data rows, then rebuild the header separately above the spill range.

SORT and Excel Tables interact in a specific way

Point SORT at a table column using a structured reference, like SORT(Orders[Amount], 1, -1), and it sorts just that column, on its own, detached from the rest of the row. To sort whole rows and keep them intact, reference the full table with SORT(Orders, 4, -1) instead, where 4 is the position of the column you're ranking by inside the table. Mixing these up is a common cause of a leaderboard where names and totals no longer match.

Common Use Cases

Rank a commission tracker by revenue

You want reps ranked highest to lowest without touching the raw data feed.

=SORT(commission_data, 4, -1)   // ranks by column 4 (Revenue), descending

Sort inside a Table without breaking row alignment

You have a product Table called Inventory with columns ID, Name, Category, Stock, and you want it ranked by stock level, low to high, so you can spot what needs reordering first.

=SORT(Inventory, 4, 1)

// Inventory = the full table, all columns, referenced as one array
// 4         = column position of Stock inside the table
// 1         = ascending, lowest stock first

Reference the whole table, not Inventory[Stock] alone. Sorting just the Stock column detaches it from the rest of each row and scrambles which quantity belongs to which product.

Build a live, sorted dashboard view with FILTER

You want only orders over $500, sorted highest to nearest.

=SORT(FILTER(orders, orders[Amount]>500), 3, -1)

// FILTER first narrows orders to Amount > 500
// SORT then ranks that filtered result by column 3, descending

FILTER runs first and hands its array straight to SORT, no helper column and no intermediate range needed. This nested pattern is where SORT earns its keep on a real dashboard: filter, then rank, in one cell.

Handling Errors

SORT throws three distinct errors, and each points to a different fix.

Common causes:

• #VALUE! when sort_index points to a column that doesn't exist in array (a common typo when someone inserts a column and forgets to update the number)
• #SPILL! when the spill range isn't empty, or when the array is too large to fit before hitting the edge of the worksheet
• #REF! when the array references a closed workbook

=IFERROR(SORT(sales_data, 3, -1), sales_data)

If SORT fails for any reason, this formula falls back to the unsorted sales_data range instead of a cell full of errors. IFERROR can't tell you which of the three errors occurred, though, so treat this as a display fallback, not a diagnosis. Use the checklist below to find the actual cause.

Notes & Gotchas

Why does SORT return #SPILL!?

#SPILL! means the cells SORT needs to fill are blocked by existing data, or the calculated result is too large for the space remaining on the sheet. Clear every cell in the spill zone, including cells that look empty but hold a stray space or formatting. Deleting adjacent data usually fixes it immediately.

Can SORT sort data in place, like the ribbon's Sort button?

No. SORT always writes its result to a new spill range; it never rearranges the source cells. If you genuinely need to reorder the original data, use Data > Sort on the ribbon, or copy the SORT output and paste it back over the source as values.

Does SORT automatically include new rows added below my source range?

Only if the reference expands to cover them. A fixed range like A2:C15 stops at row 15 no matter what you add below it. Convert the source to an Excel Table, or reference a dynamic named range, so SORT's array grows automatically as rows are added.

What's the difference between SORT and SORTBY?

SORT ranks an array by a column that's already inside that same array. SORTBY ranks an array by a separate array, one that doesn't have to appear in the output at all, and doesn't even need to be part of the same table. If you're sorting by a helper column you don't want visible in the result, use SORTBY instead.

Does SORT slow down large workbooks?

SORT recalculates on every worksheet change just like any other formula, but it's not volatile the way RANDARRAY is: it won't resort on every keystroke unless something in its dependency chain actually changed. On datasets in the tens of thousands of rows, nested SORT and FILTER combinations do add measurable recalculation time. If a workbook feels sluggish, convert the SORT output to static values with Paste Special once the ranking no longer needs to update live.

How do I get SORT's behavior on Excel 2019 or earlier?

You can't use SORT itself; it doesn't exist before Excel 2021. The workaround is a helper column with RANK or LARGE/SMALL, combined with INDEX and MATCH to pull the matching row for each rank. It's more setup, but it reproduces the same result on older Excel versions.

Related Functions

Function	Use this when...
FILTER	You need to narrow a range to matching rows, often nested inside SORT to build a sorted, filtered view.
UNIQUE	You need duplicates removed before or after sorting.
INDEX	You need a static, non-spilling way to pull ranked values on Excel 2019 or earlier.