SORT Function in Excel — The Live Sort (2026) | Sheets & Cells
⚡ Dynamic Array Starters

SORT Function in Excel

Returns a sorted copy of a range — as a live, spilling formula. The function that killed the manual re-sort ritual, powers every "top N" leaderboard tile, and finally makes multi-key grouped reports readable. Change source data → sort reorders instantly. No re-clicks, no re-selections.

Modern Excel Only
SORT requires Microsoft 365, Excel 2021+, Excel Online, Google Sheets, or LibreOffice 7.4+. Excel 2019 and earlier will show #NAME?. Check your version before deploying to shared workbooks.

🔃 The Live Sort — What SORT Actually Changed

Before SORT, ordering data meant clicking Data → Sort, picking columns, hitting OK, and repeating the whole ceremony every single time source data changed. Not anymore.

Before 2020 — Legacy Excel

Data → Sort dialog or complex LARGE/INDEX array formulas

{=INDEX($A$2:$A$100,MATCH(LARGE($B$2:$B$100,ROW()-1),$B$2:$B$100,0))}
The dialog was one-shot — click, sort, done. Source changed? Do it again. The formula approach worked for simple cases but broke silently on ties, needed Ctrl+Shift+Enter, and couldn't handle multi-key sorts at all without helper columns.
2020+ — Modern Excel

One formula, spills the entire sorted array

=SORT(A2:F100, 6, -1)
Type once. Excel spills the sorted copy into the cells below with a blue-dashed spill border. Add a row → sort adjusts. Change a value → position updates. Multi-key sorts with array constants ({1,2}, {1,-1}) work out of the box.
The paradigm shift: sorting became a formula, not a menu action. The source stays untouched; the sorted view lives elsewhere and updates itself. Multiple different sorted views of the same data can coexist — biggest-orders view, alphabetical customers view, region-grouped view — each in its own cell, each live.

Quick Answer

SORT returns a sorted copy of an array. Live formula — updates when source changes. Sort by a column index (1-based), ascending (1) or descending (-1), with multi-key support via array constants.

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

Example: =SORT(A2:F100, 6, -1) sorts the range by column 6 in descending order. Multi-key: =SORT(A2:F100, {4,6}, {1,-1}) sorts by column 4 ascending, then column 6 descending — the standard "grouped report" pattern.

⚡ Dynamic Arrays Practice Workbook

All 5 SORT examples plus FILTER and UNIQUE patterns on 30 realistic Q1 2026 sales orders.

↓ Download .xlsx (Free)
Filedynamic-arrays-2026.xlsx
Size~30 KB
Sheets6 tabs
CoversFILTER · UNIQUE · SORT
2020
Introduced
1–4
Arguments
365 / 2021+
Required
90+
Templates Use It

Syntax

SORT takes 1 required argument and 3 optional arguments that control the ordering.

ArgumentTypeDescription
array Required The range or array to sort. Can be one column, one row, or a multi-column table.
sort_index Optional Which column (or row) to sort by. 1-based. Default is 1. For multi-key sort, pass an array constant: {2, 5}.
sort_order Optional 1 for ascending (default), -1 for descending. Match with sort_index if multi-key: {1, -1} means first key asc, second key desc.
by_col Optional FALSE (default) sorts rows. TRUE sorts columns — useful when data is laid out horizontally (dates across the top row).

sort_order at a glance

1
Ascending (default)

A → Z, smallest to largest, oldest to newest. Omit the argument entirely or pass explicit 1.

-1
Descending

Z → A, largest to smallest, newest to oldest. Pass -1. Common for "top N" or "biggest first" reports.

SORT vs SORTBY — The Big Choice

SORT and SORTBY do different jobs. Getting the choice right saves you from awkward workarounds. Rule of thumb: does the sort key need to appear in the output?

SORT
Sort by a column that IS in the output
=SORT(OrdersTable, 9, -1)
Use when: your source array contains the column you want to sort by. Pick it by its column index (1-based). Result includes the sort column.
SORTBY
Sort by a column that is NOT in the output
=SORTBY(OrderProduct, OrderTotal, -1)
Use when: you want to show a compact result (just product names) but order them by another column (Total) that shouldn't appear in the output. Perfect for lean dashboard tiles.
The key question: is your sort column part of what you want to display? Yes → SORT. No → SORTBY. Both accept multi-key arguments; both spill; both update live. Pick the one that expresses your intent cleanly.

5 Worked Examples

Example 01

Sort the whole order table by Total (descending)

The bread-and-butter SORT. Reference the whole table for the array; specify column 9 (Total) as sort_index; pass -1 for descending. Result spills as a fresh sorted copy — source stays untouched.

=SORT(OrdersTable, 9, -1)

Spill Zone (top 5 shown)

Order IDCustomerRegionProductQtyTotal
ORD-1012Summit PartnersWestLaptop Pro 153$3,897
ORD-1001Acme CorpNorthLaptop Pro 152$2,598
ORD-1028Vertex SolutionsEastLaptop Pro 152$2,598
ORD-1009Datawise IncEastCAD License3$2,397
ORD-1020Summit PartnersWestErgo Chair8$1,992

Spills all 30 orders, biggest first. Change any Qty or Unit Price in Source Data → order rearranges automatically. Zero re-clicks.

Example 02

Same source, three different sorted views (by column index)

The sort_index is 1-based, relative to the source array. Point to different columns for different sorted views — each formula produces its own independent spill zone. Same source data, multiple live perspectives.

By Customer (asc) =SORT(OrdersTable, 3, 1)
By Region (asc) =SORT(OrdersTable, 4, 1)
By Qty (desc) =SORT(OrdersTable, 7, -1)

Three independent spilled tables from one source. Perfect for dashboards with multiple views of the same underlying data — no data duplication, no manual re-sorts.

Example 03

SORTBY — show one column, sort by another

SORTBY is the compact-output sibling of SORT. When you want a lean result (just product names) ordered by something that shouldn't be in the output (Total), SORTBY expresses it directly. No filtering the SORT result later.

=SORTBY(OrderProduct, OrderTotal, -1)
Product (ordered by Total, top 8)
Laptop Pro 15
Laptop Pro 15
Laptop Pro 15
CAD License
Ergo Chair
Office 365
Monitor 27"
CAD License

30 product names spilled in Total-descending order. Notice: no Total column in the output, but the sort respects it. Great for compact leaderboards and "biggest impact items" lists.

Example 04

Multi-key sort: Region ascending, then Total descending (the grouped report pattern)

Pass array constants to sort by multiple keys. {4, 9} = column 4 (Region) then column 9 (Total). {1, -1} = first key ascending, second key descending. This is THE standard reporting pattern.

=SORT(OrdersTable, {4, 9}, {1, -1})
Order IDRegionProductTotal
— East group (alphabetically first) —
ORD-1028EastLaptop Pro 15$2,598
ORD-1009EastCAD License$2,397
ORD-1021EastOffice 365$1,980
ORD-1017EastMonitor 27"$1,974
— North group next —
ORD-1001NorthLaptop Pro 15$2,598
ORD-1018NorthCAD License$1,598
…………

Regions grouped alphabetically; inside each region, biggest Totals first. The reporting pattern for board decks, monthly reviews, and any "grouped and ranked" ask.

Example 05

Top-N pattern: top 5 orders by Total

SORT + INDEX + SEQUENCE. Sort by Total descending, then use INDEX with SEQUENCE(5) for rows and SEQUENCE(1,9) for columns. Grabs exactly the first 5 rows across all 9 columns — the "leaderboard" pattern.

=INDEX(SORT(OrdersTable, 9, -1), SEQUENCE(5), SEQUENCE(1, 9))
Order IDCustomerProductTotal
ORD-1012Summit PartnersLaptop Pro 15$3,897
ORD-1001Acme CorpLaptop Pro 15$2,598
ORD-1028Vertex SolutionsLaptop Pro 15$2,598
ORD-1009Datawise IncCAD License$2,397
ORD-1020Summit PartnersErgo Chair$1,992

Exactly 5 rows. Change SEQUENCE(5) to SEQUENCE(10) for top 10. Or use the cleaner modern =TAKE(SORT(OrdersTable, 9, -1), 5) — same result, less typing. Excel 365 only.

Decoding Multi-Key Sort — Array Constants Explained

The {4,9}, {1,-1} syntax looks cryptic the first time. Once decoded, multi-key sorts read like sentences.

=SORT(OrdersTable, {4, 9}, {1, -1})
Two array constants working together. The first says which columns to sort by, in priority order. The second says which direction for each.
sort_index
{4, 9}
Sort by column 4 first (Region). For rows that tie, break the tie using column 9 (Total). Priority left-to-right.
sort_order
{1, -1}
Column 4 ascending (A → Z regions). Column 9 descending (biggest Total first inside each region). Pairs up positionally.

Read it as: "Group by Region alphabetical, then rank biggest-first inside each group." The array constants line up positionally — first key with first direction, second with second, and so on. Any number of keys.

Top-N Pattern — Classic vs Modern

Getting the top 5 (or top N) rows is one of the most-requested SORT patterns. Two approaches — one works everywhere with dynamic arrays, one is cleaner but newer.

Classic — Works in All Excel 365/2021+

INDEX + SORT + SEQUENCE

=INDEX(SORT(data, 9, -1), SEQUENCE(5), SEQUENCE(1, 9))

Two SEQUENCE calls: one for rows (top 5), one for columns (all 9). INDEX picks out the top-left rectangle. Verbose but rock-solid; works in every dynamic-array Excel.

Modern — Requires Excel 365

TAKE + SORT

=TAKE(SORT(data, 9, -1), 5)

TAKE grabs the first N rows of an array. Reads like plain English. Requires TAKE (added to Excel 365 in 2022) — not yet in Excel 2021 perpetual, Google Sheets, or LibreOffice.

For dashboards deployed to mixed audiences, use the classic pattern for max compatibility. For personal or 365-only workbooks, TAKE is the cleaner choice. Both produce identical results.

Interactive Playground

30-row Orders table with 9 columns
Column 3 → Customer
Column 4 → Region
Column 6 → Category
Column 7 → Qty
Column 9 → Total
Formula & Sort Behavior
=SORT(OrdersTable, 9, -1)
30 rows, biggest Total first
=SORT(OrdersTable, 3)
30 rows, Customer A → Z
=SORTBY(OrderProduct, OrderTotal, -1)
Product names ordered by Total
=SORT(OrdersTable, {4,9}, {1,-1})
Region asc, then Total desc
=TAKE(SORT(OrdersTable, 9, -1), 5)
Top 5 orders by Total

The Dynamic Array Starters

Three modern-Excel functions that spill results. Learn one, understand all three — they compose naturally.

Common Errors

SymptomCauseFix
#NAME? You're on Excel 2019 or earlier — SORT doesn't exist. Or you typo'd the name. Upgrade to Excel 365, 2021, or 2024. On legacy Excel, use LARGE/SMALL+INDEX array formulas (Ctrl+Shift+Enter) or the Data → Sort dialog.
#SPILL! Something is blocking the spill zone — a value in a cell where the sorted result needs to go. Clear the blocking cells. Hover the spill indicator — Excel highlights the block. Move the formula to a location with room to grow.
#VALUE! sort_index is out of range (e.g., 15 when your array has only 9 columns), or you passed a non-numeric value. Check the sort_index against your source array's column count. Remember it's 1-based — first column is 1, not 0. For multi-key, every value in the array constant must be valid.
Sort seems wrong — text mixed with numbers oddly The sort column contains a mix of text and numbers. Excel sorts numbers as numbers and text as text — text sorts after numbers by default. Normalize the column: convert everything to numbers or everything to text before SORT. Use VALUE() or TEXT() inside a wrapper: =SORT(A2:B100, ...) with cleaned inputs.
Multi-key sort's second key doesn't seem to work Your first sort key has NO ties, so the second key never gets applied. Or the array constants aren't the same length. Verify the first key has ties to break. Check both array constants have matching lengths: {2,5} pairs with {1,-1}, not {1}. Positional pairing is strict.
Sort broke when I added a row to source You used fixed cell references (A2:F100) instead of named ranges or Table references, and your new row is outside the range. Convert source to an Excel Table (Ctrl+T) and reference the Table name: =SORT(OrdersTable, 9, -1). Auto-extends as you add rows. Or use OFFSET/named ranges. Never hardcode ranges for live workbooks.

⚡ Practice on Real Sales Data

Every SORT pattern — basic sort, sort_index variations, SORTBY, multi-key with array constants, Top-N — on 30 Q1 2026 orders.

↓ Get Workbook

Related Functions

Compatibility

PlatformSupportedVersionNotes
Excel 2019 & earlier✗—Not available — #NAME? error
Excel 2021 / 2024✓PerpetualFull support (TAKE not included; use INDEX+SEQUENCE for Top-N)
Microsoft 365✓CurrentFull support including TAKE for cleaner Top-N
Excel Online✓CurrentFull support
Excel Mobile (iOS/Android)✓CurrentFull support
Google Sheets✓AllSame syntax, identical behavior (SORTBY also supported)
LibreOffice Calc✓7.4+Earlier versions: not available

How to Use SORT — Step by Step

  1. Click an empty cell with room to spill downward — ideally the top of a blank column, ideally with room to the right if you're sorting a multi-column array.
  2. Type =SORT( — Excel auto-suggests as soon as you type "SOR".
  3. Select the source array (a range, a Table name, or a named range), then type a comma.
  4. Specify the sort_index as a number (1-based). Column 1 sorts by the first column, column 5 sorts by the fifth. Omit for default of 1.
  5. Optionally add sort_order: 1 for ascending (default), -1 for descending. Type a comma before adding.
  6. Close with ) and press Enter. The sorted result spills into the cells below with a blue-dashed spill border.
  7. For multi-key sort, pass array constants: =SORT(data, {2, 5}, {1, -1}). Sort by column 2 ascending, then column 5 descending. Any number of keys.
  8. For sort-by-a-different-column, use SORTBY instead: =SORTBY(output_col, sort_col, -1). Keeps the output compact while ordering by an external key.

Explore More Functions

⚡ One More Time — Grab the Workbook

Every SORT pattern plus FILTER and UNIQUE combos, on 30 realistic sales orders. Free, one file, six sheets.

↓ Download Free

Frequently Asked Questions

Why does SORT return #NAME?

Your Excel version doesn't support SORT. It was introduced in Microsoft 365 in 2020 and added to Excel 2021 perpetual. Excel 2019 and earlier always show #NAME? because the function doesn't exist.

Options: upgrade to Excel 365/2021+ (recommended), use the Data → Sort dialog for one-time sorting, or use the legacy LARGE/SMALL+INDEX+MATCH array formula approach. The legacy approach works but is limited to single-column sorts.

What's the difference between SORT and the Data → Sort dialog?

SORT is a formula that returns a sorted copy to a new location — source stays intact and the result updates when source changes. The dialog is a one-shot action that reorders your source data in place — destructive and one-time.

Use SORT for reactive workbooks (dashboards, live reports, sorted views), when you want to preserve source order, or when you need multiple different sorted views of the same data. Use the dialog only for one-time cleanup of static files.

What's the difference between SORT and SORTBY?

SORT sorts by a column that IS in the source array — you pick it by column index. SORTBY sorts by a column that is NOT in the output — you pass the sort column as a separate array.

SORT example: =SORT(OrdersTable, 9, -1) — output includes the Total column (col 9). SORTBY example: =SORTBY(OrderProduct, OrderTotal, -1) — output shows just product names, but they're ordered by Total (which isn't shown). Choose SORT when you want the sort column visible; SORTBY for a leaner output.

How do I sort by multiple columns?

Pass array constants for both sort_index and sort_order: =SORT(data, {2, 5}, {1, -1}). This sorts by column 2 ascending (first priority), then by column 5 descending (tie-breaker).

The array constants pair positionally: {2, 5} with {1, -1} means column 2 gets direction 1 (asc), column 5 gets direction -1 (desc). Length must match. Any number of keys — {2, 5, 8} with {1, -1, 1} for three-level sort.

How do I sort descending?

Pass -1 as the third argument: =SORT(data, 3, -1) sorts by column 3 in descending order. For ascending (default), pass 1 or omit the argument.

For multi-key with mixed directions, use an array constant: =SORT(data, {2, 5}, {1, -1}) sorts column 2 ascending, column 5 descending. Any direction combination works.

How do I get the top N rows using SORT?

Two approaches. Classic: =INDEX(SORT(data, col, -1), SEQUENCE(N), SEQUENCE(1, cols)). INDEX pulls the top-left rectangle of N rows across all columns. Works in every dynamic-array Excel.

Modern: =TAKE(SORT(data, col, -1), N). TAKE grabs the first N rows directly. Reads cleaner but requires TAKE (Excel 365 only — not yet in Excel 2021 perpetual). Both produce identical results.

Can SORT handle blank cells?

Yes, but blanks are treated as 0 for numeric columns and as empty strings for text columns. In ascending sort, blanks appear FIRST for text (empty comes before letters) and near the start for numbers. In descending, they appear last.

If blanks in your sort column produce unwanted results, filter them out first: =SORT(FILTER(data, sort_col<>""), sort_col_index, -1). Removes blank rows before sorting.

Can I sort by a custom order (not alphabetical or numerical)?

Not directly with SORT — it only knows alphabetical (text) and numeric ordering. For custom orders like "High → Medium → Low" or day-of-week ordering, use SORTBY with a MATCH lookup.

Example: =SORTBY(data, MATCH(priority_col, {"High","Medium","Low"}, 0)). MATCH converts each value to its position in the custom order (1, 2, 3), and SORTBY orders by those positions. Same trick works for months, days, or any custom sequence.

Why did my SORT break when I added a row to source data?

You probably used a fixed range like A2:F100 and the new row is at row 101 — outside the range. SORT can't see rows it wasn't told about.

Fix: convert source to an Excel Table (Ctrl+T) and reference the Table name: =SORT(OrdersTable, 9, -1). Tables auto-extend as you add rows. Or use named ranges with OFFSET/COUNTA to build a dynamic range. Never hardcode ranges for live workbooks — that's the #1 cause of "why did it break?" tickets.

Does SORT work in Google Sheets?

Yes — Google Sheets has SORT (and SORTBY) with the same syntax and behavior as Excel 365. Same arguments, same array-constant patterns for multi-key sorts, same spill behavior.

The only compatibility difference in this space is TAKE — it's Excel 365 only for now. In Google Sheets, use the classic INDEX+SEQUENCE Top-N pattern instead: =INDEX(SORT(data, col, -1), SEQUENCE(5), SEQUENCE(1, cols)).

Can I sort horizontally (columns instead of rows)?

Yes — set the 4th argument by_col to TRUE: =SORT(A1:Z1, 1, 1, TRUE). This treats each column as a unit and reorders columns instead of rows.

Rarely used because most data is laid out with rows as records. But if your source has dates or categories across the top row (rather than down a column), by_col=TRUE is what you need to reorder them alphabetically or numerically.

Does SORT slow down large workbooks?

SORT is fast — Excel's dynamic-array engine is optimized for it. Performance concerns typically come from source SIZE (500K+ rows) or the number of SORT formulas referencing the same range (dozens).

Best practices: filter down BEFORE sorting when possible (=SORT(FILTER(...))). Avoid volatile functions like NOW() or RAND() in the sort key — they force recalc on every change. For truly huge datasets, consider Power Query for the initial slice, then SORT for the reactive display layer.

Every Leaderboard, Every Grouped Report, One Formula

Excel Wizard writes SORT patterns — SORTBY for compact leaderboards, multi-key array-constant sorts for grouped reports, TAKE+SORT Top-N tiles — from a plain-English prompt. Stop clicking Data → Sort. Start sorting live, right inside Excel.

Get Excel Wizard Add-in →