Interactive lesson · Excel & Google Sheets

The Excel SORT function, explained interactively.

Q: How do I sort by a column that's not the first one?

Set sort_index to the column's relative position inside array — not its letter on the sheet. =SORT(A2:C8, 3, -1) sorts rows by the third column of the range (the range's own third column, which may or may not be column C depending on where array starts), descending. The index is 1-based.

Q: What's the difference between SORT and SORTBY?

SORT sorts a range by one of its own columns. SORTBY sorts by a separate range — useful when you want the sort key to live elsewhere, or to sort by multiple keys at once. SORTBY handles multi-level sorts cleanly (name ascending, then date descending); SORT needs a nested call or a helper column for that.

Last updated: April 2026

SORT returns the rows of a range reordered by the column and direction you choose. One formula spills an entire sorted table — no Ctrl+Shift+Enter, no helper columns, no menu clicks. Combined with FILTER and UNIQUE it’s the last piece of the dynamic-array trio that replaces most pivot-table setup.

01 · See it work

How to use SORT

Type =SORT( and supply the array — a range like A2:B8, or another dynamic-array formula.
Add sort_index — relative position (1-based) of the column inside array to sort by. 1 for the first column, 2 for the second.
Add sort_order — 1 for ascending (the default, A→Z and low→high), -1 for descending.
Close the parenthesis. Place the formula in a cell with enough empty space below and to the right — SORT spills the full table, and blocked spill ranges throw #SPILL!.

Flip the pickers — sort_index = 2 sorts by Sales; sort_order = -1 puts the top seller at the top; combined they give the classic top-performer list in one formula.

FUNCTIONSORT

Returns the rows of array reordered by the chosen column in the chosen direction. One formula in one cell spills the full sorted table into the range below.

ARG 1array

The range or dynamic array to sort. Preserves every column — SORT reorders rows as a unit so each row’s columns stay together. Works on 1D and 2D ranges.

ARG 2[sort_index]

Which column in array to sort by. 1 means the first column of array (not of the sheet) — indexing is relative to the range, not absolute. Defaults to 1.

ARG 3[sort_order]

1 = ascending (A→Z, low→high), the default. -1 = descending. Any other value returns #VALUE! — SORT is strict about the 1/-1 contract.

=SORT(A2:B8, 2, -1)

	A	B	D	E
1	Rep	Sales	Rep (sorted)	Sales (sorted)
2	Ava	12,400	Chloe	15,300
3	Ben	8,700	Gus	14,050
4	Chloe	15,300	Ava	12,400
5	Dev	6,200	Eva	11,100
6	Eva	11,100	Finn	9,950
7	Finn	9,950	Ben	8,700
8	Gus	14,050	Dev	6,200

D2 is the active cell — the single formula there spills its results across D2:E8. sort_index = 2, sort_order = -1: SORT returns all 7 rows ordered by Sales descending. The single formula in D2 spills across D2:E8 — no Ctrl+Shift+Enter, no helper column.

02 · Syntax, argument by argument

SORT syntax and arguments

One required argument, three optional. See Microsoft’s official SORT reference for the canonical specification.

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

array

The range or dynamic array to sort. Rows stay intact — SORT reorders them as whole units so each row’s columns don’t get scrambled independently. Can be a literal range (A2:C100) or another spilled formula (FILTER(...)).

[sort_index]

Which column to sort by, as a 1-based index relative to array. If array is C2:E8, sort_index = 1 sorts by column C, 2 by column D. Defaults to 1 when omitted.

[sort_order]

1 = ascending (default), -1 = descending. SORT is strict — any other value returns #VALUE!. Don’t pass TRUE / FALSE; it accepts only the two integers.

[by_col]

FALSE (default) sorts rows — the common case. TRUE sorts columns left-to-right based on a row’s values. Useful for reordering fields in a single header row; rare in practice.

03 · In the wild

SORT examples

Four patterns that cover most real-world SORT use.

Example 1: SORT — top N rows by value

The canonical leaderboard case — sort a range descending by the metric column, then take the top N with a range reference.

=SORT(A2:B8, 2, -1)

Returns the full sales table ordered by the Sales column, biggest first. For just the top 3, wrap in TAKE (Excel 365): =TAKE(SORT(A2:B8, 2, -1), 3). In older Excel, reach the same by placing the SORT formula and reading only the first 3 spilled rows.

Example 2: SORT + FILTER — sorted subset

Compose SORT with FILTER to get just the rows that match a condition, sorted by another column. One formula replaces a pivot table.

=SORT(FILTER(A2:B8, A2:A8="Ava"), 2, -1)

FILTER keeps rows where Rep is Ava; SORT orders those rows by Sales descending. Combine with SUM / AVERAGE over the same spill for live per-person metrics. Add FILTER’s if_empty argument to avoid #CALC! when nothing matches.

Example 3: SORT + UNIQUE — dedupe and order

Build a sorted distinct list — useful for dropdown sources, axis labels, or category headers on a dashboard.

=SORT(UNIQUE(A2:A100))

UNIQUE drops duplicates, SORT orders what remains. Ascending is the default, so no sort_order needed for the common case. Feed the spill into a data-validation list or into another formula’s array argument.

Example 4: SORT — reverse a range with SORTBY + SEQUENCE

SORT orders by value, not position. To genuinely reverse a list (last row first), use SORTBY with a SEQUENCE key and descending order.

=SORTBY(A2:A8, SEQUENCE(ROWS(A2:A8)), -1)

SEQUENCE(ROWS(A2:A8)) produces {1;2;3;4;5;6;7}; descending order flips it to {7;6;5;4;3;2;1}, which SORTBY uses as the row order. A small idiom that comes up often once you need it.

04 · Errata

Common SORT errors and fixes

Four failure modes, each with what to check and how to recover.

SORT returns #SPILL!

Cause: another cell in the output rectangle isn’t empty. Dynamic arrays need the full spill range free — even a single stray value blocks the whole output.

Click the formula cell; Excel highlights the intended spill range with a blue dashed outline. Clear any cells inside that rectangle, or move the formula to a roomier spot. On old Excel versions without dynamic arrays, SORT isn’t available.

SORT returns #VALUE!

Cause: sort_order is something other than 1 or -1. SORT doesn’t accept TRUE/FALSE, 0, or strings — it’s strict about the two-value contract.

Pass 1 for ascending or -1 for descending. Same thing goes for sort_index: must be a positive integer ≤ number of columns in array. Out-of-range indexes return #VALUE!.

SORT mixes header row into the sorted data

Cause: array includes the header. Unlike Excel’s Data → Sort menu, SORT has no “My data has headers” toggle.

Exclude the header row from the range: =SORT(A2:C100, 3, -1), not A1:C100. If you want the header on top of the spilled output, add it as a literal VSTACK in Excel 365: =VSTACK(A1:C1, SORT(A2:C100, 3, -1)).

SORT gives unexpected order with mixed types

Cause: the column contains mixed numbers and text (e.g. "N/A" strings alongside real numbers). Excel’s collation puts numbers before text before logicals before errors, which feels jumbled.

Clean the data: replace sentinel strings with real empties or a sortable numeric (e.g. -1 for missing values). Or filter the mixed rows out with FILTER before sorting. Mixed types in a sort column are almost always an import bug worth fixing upstream.

05 · Kindred functions

SORT vs SORTBY, UNIQUE & FILTER

Four dynamic-array tools. Pick by whether you’re reordering, picking rows, deduplicating, or sorting by a separate key.

Function	Does what	Args	Typical use
SORT	Orders by one of its own columns	array, [index], [order], [by_col]	Leaderboards, ordered distinct lists, sorted report tables
SORTBY	Orders by a separate key range	array, by_array1, [order1], ...	Multi-level sorts, reverse-a-column, sort by computed key
UNIQUE	Drops duplicates	array, [by_col], [exactly_once]	Distinct lists for axes / dropdowns / grouping
FILTER	Keeps rows that pass a boolean test	array, include, [if_empty]	Dynamic-array AutoFilter, conditional subsets

Rule of thumb: SORT when the key column lives inside the range; SORTBY when the key is a separate array or you need multiple sort levels. Compose all three — SORT outside, FILTER inside, UNIQUE innermost — to replace a huge range of pivot-table workflows with one spilled formula. All three require modern Excel (365 / 2021+) or Google Sheets.

06 · Marginalia

SORT frequently asked questions

6.01How do I sort by a column that’s not the first one?▸

Set sort_index to the column’s relative position inside array — not its letter on the sheet. =SORT(A2:C8, 3, -1) sorts rows by the third column of the range, descending. The index is 1-based.

6.02What’s the difference between SORT and SORTBY?▸

SORT sorts a range by one of its own columns. SORTBY sorts by a separate range — useful when the sort key lives elsewhere, or to sort by multiple keys at once. SORTBY handles multi-level sorts (name ascending, then date descending) cleanly; SORT needs a nested call or a helper column for that.

6.03Can I reverse a column with SORT?▸

Not directly — SORT orders by value, not position. To genuinely reverse a range use =SORTBY(A2:A8, SEQUENCE(ROWS(A2:A8)), -1). SEQUENCE supplies positions 1..n as the key, and descending order flips them to n..1.

6.04Does SORT work on text like it works on numbers?▸

Yes — SORT uses the same locale-aware comparison as Excel’s built-in A→Z sort. Ascending puts "Ava" before "Ben" before "Chloe". Mixed numeric and text values sort with numbers first, then text, then logicals, then errors — the standard Excel collation order.

6.05Why am I getting #SPILL! from my SORT formula?▸

Another cell in the spill range isn’t empty. SORT needs the full output rectangle (rows × cols of array) free. Clear the blocking cells or move the formula somewhere with room. The blue dashed outline in modern Excel shows exactly which cells are needed.

Microsoft Excel is a registered trademark of Microsoft Corporation. Google Sheets is a trademark of Google LLC. Formula Gym is not affiliated with, endorsed by, or sponsored by either company.