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.
Returns the range in sorted order, spilled into neighboring cells. Available in Excel 365 / 2021 and Excel Online.
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.
| Expression | Result | Why |
|---|---|---|
| =INDEX(SORT(C2:C8),1) | 100 | Smallest sale (ascending) |
| =INDEX(SORT(C2:C8,1,-1),1) | 400 | Largest sale (descending) |
| =MAX(SORT(C2:C8)) | 400 | Maximum of the sorted list |
| =SUM(SORT(C2:C8)) | 1500 | Sorting does not change the total |
| =INDEX(SORT(A2:A8),1) | Alice | First name alphabetically |
| =INDEX(SORT(A2:A8,1,-1),1) | Dave | Last name alphabetically |
| =SORT(C2:C8) | 100 | Top-left of the spill (Excel shows all 7) |
| =INDEX(SORT(C2:C8),7) | 400 | Seventh (largest) value |
| Expression | Result | Why |
|---|---|---|
| A9 | 100 | smallest |
| A10 | 100 | second |
| A11 | 150 | third |
| A12 | 200 | fourth |
| A13 | 250 | fifth |
| A14 | 300 | sixth |
| A15 | 400 | largest |
| Expression | Result | Why |
|---|---|---|
| =SORT(C2:C8,1,1) | ascending | sort_order 1 = A→Z / small→large |
| =SORT(C2:C8,1,-1) | descending | sort_order -1 = Z→A / large→small |
| =SORTBY(A2:A8,C2:C8,-1) | Dave first | sort names by sales, descending |
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.
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.
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.
| Platform | Support | Notes | Gotcha |
|---|
SORT is one piece. The full course walks the toolkit end to end with live, measured examples.