Dynamic arrays · ordered rows · Nov 2026

The SORT function,
measured & explained.

Syntax, a live playground, a measured ordering gallery, sort_index/sort_order, SORT vs SORTBY, and a sourced error library: the dynamic-array SORT function.

D1
fx
=SORT(C2:C8,1,-1)
0
measured gallery rows
0
sourced errors fixed
0
platforms checked
// 01 — Anatomy

How SORT works

Returns the range in sorted order, spilled into neighboring cells. Available in Excel 365 / 2021 and Excel Online.

=SORT(array, [sort_index], [sort_order], [by_col])
arrayThe range or array to sort.
sort_indexWhich column to sort by (1 = first; default 1).
sort_order1 = ascending (default), -1 = descending.
by_colFALSE sorts rows; TRUE sorts columns.
.bool-moat td.res-text{color:var(--cyan);font-weight:700;font-family:var(--mono)}
// 02 — Ordered rows

What SORT actually returns

SORT returns the range in order and spills it. The live gallery wraps SORT in INDEX/MAX/SUM so each result is one measurable value; the full sorted spill is shown below. Every row was measured live in HyperFormula. Click any row to load it.

Ordering gallery — measured wrappers

ExpressionResultWhy
=INDEX(SORT(C2:C8),1)100Smallest sale (ascending)
=INDEX(SORT(C2:C8,1,-1),1)400Largest sale (descending)
=MAX(SORT(C2:C8))400Maximum of the sorted list
=SUM(SORT(C2:C8))1500Sorting does not change the total
=INDEX(SORT(A2:A8),1)AliceFirst name alphabetically
=INDEX(SORT(A2:A8,1,-1),1)DaveLast name alphabetically
=SORT(C2:C8)100Top-left of the spill (Excel shows all 7)
=INDEX(SORT(C2:C8),7)400Seventh (largest) value

The full spill: =SORT(C2:C8) ascending

ExpressionResultWhy
A9100smallest
A10100second
A11150third
A12200fourth
A13250fifth
A14300sixth
A15400largest

Arguments: sort_index and sort_order

ExpressionResultWhy
=SORT(C2:C8,1,1)ascendingsort_order 1 = A→Z / small→large
=SORT(C2:C8,1,-1)descendingsort_order -1 = Z→A / large→small
=SORTBY(A2:A8,C2:C8,-1)Dave firstsort names by sales, descending
⚠ SORT spills the whole ordered range; blocked cells cause #SPILL!. SORTBY (sort one range by another) is a separate Excel 365 function and is not evaluated by this page's engine — the row above shows its Excel result.
SORT is a stable sort: equal values keep their original relative order. sort_index picks which column to sort by (1 = first); sort_order is 1 (ascending, default) or -1 (descending). Nest UNIQUE or FILTER inside SORT to order a derived list.
// 03 — Try it live

Interactive SORT playground

Edit the table or type any formula — the HyperFormula engine evaluates it instantly. Load a preset to read the smallest/largest value, sort descending, and confirm the total is unchanged.

A1Sample data — click any cell to edit, the formula re-evaluates live
Result
—
✓ readyHyperFormula 3.5
// 04 — Error library

The real-world error library

8 failure patterns mined from actual user reports on Reddit, Stack Overflow and Super User — each with the authentic symptom, root cause, a fix, and a runnable formula. Not textbook theory.

// 05 — Cross-platform

Where SORT works

SORT requires dynamic arrays: Excel 365 / 2021, Excel Online and Google Sheets. Excel 2019 and earlier return #NAME?; use LARGE/SMALL or Data > Sort there.

PlatformSupportNotesGotcha
✓ Supported full SORT◐ Partial works with caveatsCompiled from public docs · verify against your build
// Go deeper

Master dynamic arrays

SORT is one piece. The full course walks the toolkit end to end with live, measured examples.

  • UNIQUE / FILTER / SORT — the dynamic-array trio
  • sort_index and sort_order arguments
  • SORT vs SORTBY (sort by another range)
  • Stable sorting and tie-breaking
Free while in beta
Browse the function library

No account needed. Every page has a live playground.

// 07 — FAQ

Frequently asked