SORT Function

Excel Functions › Lookup & Reference

Excel 365 Excel 2021+ Dynamic Array

The Excel SORT function returns a sorted copy of a range that spills onto the worksheet — the original data never moves, and the result re-sorts itself automatically whenever the source changes. One formula replaces the manual Data › Sort routine forever. Pair it with FILTER and UNIQUE and you have a live, self-maintaining report.

Quick answer: to sort a table by its 3rd column, largest value first:
=SORT(A2:C7, 3, -1)
3 is the column to sort by (counted inside the range), and -1 means descending. Omit both and SORT sorts by the first column, ascending.

Syntax

=SORT(array, [sort_index], [sort_order], [by_col])
ArgumentDescription
arrayRequiredThe range or array to sort. Do not include the header row.
sort_indexOptionalWhich column (or row, with by_col) to sort by, counted within the array. Default is 1.
sort_orderOptional1 = ascending (default) · -1 = descending. Nothing else is accepted.
by_colOptionalFALSE (default) sorts rows top-to-bottom. TRUE sorts columns left-to-right — for horizontal data.

Available in: Excel for Microsoft 365, Excel 2021+, and Excel for the web. Older versions show #NAME?. The result is a dynamic array: it spills into the cells below and to the right of the formula.

Sort a table by one column

The product table sits in A2:C7 (headers in row 1). One formula in E2 returns the whole table sorted by Units, biggest sellers first — the headers in E1:G1 are typed normally:

ABCEFG
1ProductCategoryUnitsProductCategoryUnits
2Laptop StandAccessories1,840Ring LightVideo3,120
3USB-C HubAccessories2,650USB-C HubAccessories2,650
4Monitor 27inDisplays980Webcam ProVideo2,210
5Ring LightVideo3,120Laptop StandAccessories1,840
6Desk Mat XLAccessories1,410Desk Mat XLAccessories1,410
7Webcam ProVideo2,210Monitor 27inDisplays980
=SORT(A2:C7, 3, -1) // spills 6 rows x 3 columns into E2:G7

Type a new number in column C and the spilled copy reorders instantly. To refer to the whole result elsewhere, use the spill operator: E2#.

For a single column, the defaults are all you need:

=SORT(A2:A7) // one column, A to Z

Try it: interactive SORT demo

Live demo

Sort the product table live: pick a column and a direction, and watch the spilled result reorder.

Multi-level sorts and dynamic-array combos

Pass array constants to sort_index and sort_order for a multi-level sort — here Category A→Z, then Units largest-first within each category:

=SORT(A2:C7, {2,3}, {1,-1}) // sort by column 2 asc, then column 3 desc

SORT shines as the outer layer of other dynamic array functions:

=SORT(FILTER(A2:C7, B2:B7="Video"), 3, -1) // filtered rows, biggest first
=SORT(UNIQUE(B2:B7)) // alphabetical list of categories, no duplicates

For horizontal data — months running across the top — set by_col to TRUE:

=SORT(B1:G2, 2, -1, TRUE) // reorder columns by the values in row 2

Need to sort by a column you don’t want in the output? That’s exactly what SORTBY is for.

Errors & common pitfalls

#SPILL! — the landing zone isn’t empty. SORT needs as many blank cells as the array it returns. Anything in the way — even a stray space — blocks the spill. Click the error’s warning icon to highlight the obstruction, then clear it.

#VALUE! — bad sort_index or sort_order. sort_index must point at a column that exists inside the array, and sort_order only accepts 1 or -1 (not 0, not TRUE/FALSE).

#NAME? — older Excel. SORT exists only in Excel 2021+ and Microsoft 365. In Excel 2019 and earlier the name isn’t recognized — there is no direct equivalent short of helper columns or VBA.

Pitfall: headers inside the array. Include row 1 in array and your header line gets sorted into the data. Start the range at the first data row and type headers above the spill yourself.

Pitfall: you can’t edit a spilled result. The sorted copy is read-only output — typing in it triggers #SPILL! on the source formula. Edit the original data instead; the spill follows along.

Practice workbook

📊
Download the free SORT practice workbook
Every example on this page, ready to open in Excel — plus practice challenges with answers on a separate tab. No sign-up required.

Frequently asked questions

What's the difference between SORT and SORTBY?
SORT orders an array by one of its own columns. SORTBY orders an array by any other array — even one you don’t include in the output. For "show names sorted by score, without the score," use SORTBY.
How do I sort by multiple columns with SORT?
Pass array constants: =SORT(A2:C20, {2,3}, {1,-1}) sorts by column 2 ascending, then column 3 descending. For unlimited keys with cleaner syntax, use SORTBY.
Does SORT change my original data?
No. SORT returns a new, spilled copy and leaves the source range untouched. That's its biggest advantage over the Data › Sort command, which rearranges the data itself.
Why does SORT return #NAME? on my computer?
Your Excel version predates dynamic arrays. SORT requires Excel 2021, Excel for Microsoft 365, or Excel for the web. In older versions the function name isn't recognized.
How do I sort Z to A (descending)?
Set the third argument to -1: =SORT(A2:A20, 1, -1). The sort_order argument accepts only 1 (ascending) or -1 (descending).

Master functions like this in one day

This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related functions: SORTBY · UNIQUE · FILTER · TAKE · XLOOKUP