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.
#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.
Data → Sort dialog or complex LARGE/INDEX array formulas
One formula, spills the entire sorted array
{1,2}, {1,-1}) work out of the box.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.
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.
Syntax
SORT takes 1 required argument and 3 optional arguments that control the ordering.
| Argument | Type | Description |
|---|---|---|
| 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
Ascending (default)
A → Z, smallest to largest, oldest to newest. Omit the argument entirely or pass explicit 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?
5 Worked Examples
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.
Spill Zone (top 5 shown)
| Order ID | Customer | Region | Product | Qty | Total |
|---|---|---|---|---|---|
| ORD-1012 | Summit Partners | West | Laptop Pro 15 | 3 | $3,897 |
| ORD-1001 | Acme Corp | North | Laptop Pro 15 | 2 | $2,598 |
| ORD-1028 | Vertex Solutions | East | Laptop Pro 15 | 2 | $2,598 |
| ORD-1009 | Datawise Inc | East | CAD License | 3 | $2,397 |
| ORD-1020 | Summit Partners | West | Ergo Chair | 8 | $1,992 |
Spills all 30 orders, biggest first. Change any Qty or Unit Price in Source Data → order rearranges automatically. Zero re-clicks.
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.
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.
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.
| 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.
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.
| Order ID | Region | Product | Total |
|---|---|---|---|
| — East group (alphabetically first) — | |||
| ORD-1028 | East | Laptop Pro 15 | $2,598 |
| ORD-1009 | East | CAD License | $2,397 |
| ORD-1021 | East | Office 365 | $1,980 |
| ORD-1017 | East | Monitor 27" | $1,974 |
| — North group next — | |||
| ORD-1001 | North | Laptop Pro 15 | $2,598 |
| ORD-1018 | North | CAD 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.
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.
| Order ID | Customer | Product | Total |
|---|---|---|---|
| ORD-1012 | Summit Partners | Laptop Pro 15 | $3,897 |
| ORD-1001 | Acme Corp | Laptop Pro 15 | $2,598 |
| ORD-1028 | Vertex Solutions | Laptop Pro 15 | $2,598 |
| ORD-1009 | Datawise Inc | CAD License | $2,397 |
| ORD-1020 | Summit Partners | Ergo 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.
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.
INDEX + SORT + SEQUENCE
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.
TAKE + SORT
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 4 → Region
Column 6 → Category
Column 7 → Qty
Column 9 → Total
Formula & Sort Behavior
The Dynamic Array Starters
Three modern-Excel functions that spill results. Learn one, understand all three — they compose naturally.
Common Errors
| Symptom | Cause | Fix |
|---|---|---|
| #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.
Related Functions
=SORT(FILTER(...), col, -1) for sorted filtered results.=SORT(UNIQUE(...)) is the standard dropdown-source pattern.=TAKE(SORT(...), 5). Excel 365 only.=INDEX(SORT(...), SEQUENCE(N), ...).Compatibility
| Platform | Supported | Version | Notes |
|---|---|---|---|
| Excel 2019 & earlier | ✗ | — | Not available — #NAME? error |
| Excel 2021 / 2024 | ✓ | Perpetual | Full support (TAKE not included; use INDEX+SEQUENCE for Top-N) |
| Microsoft 365 | ✓ | Current | Full support including TAKE for cleaner Top-N |
| Excel Online | ✓ | Current | Full support |
| Excel Mobile (iOS/Android) | ✓ | Current | Full support |
| Google Sheets | ✓ | All | Same syntax, identical behavior (SORTBY also supported) |
| LibreOffice Calc | ✓ | 7.4+ | Earlier versions: not available |
How to Use SORT — Step by Step
- 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.
- Type
=SORT(— Excel auto-suggests as soon as you type "SOR". - Select the source array (a range, a Table name, or a named range), then type a comma.
- 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.
- Optionally add sort_order:
1for ascending (default),-1for descending. Type a comma before adding. - Close with
)and press Enter. The sorted result spills into the cells below with a blue-dashed spill border. - 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. - 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.
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.
Templates That Use SORT
Sales Leaderboard
Team rankings that update live as orders come in. TAKE+SORT for top-10 tile, multi-key SORT for grouped-by-region board.
USES SORTTop-N Report Generator
Dropdown selects the metric (revenue, qty, margin) and N value; SORT+INDEX+SEQUENCE generates the exact leaderboard. Reusable frame.
USES SORTPriority Queue Sorter
Task list with SORTBY+MATCH for custom priority order (Critical, High, Medium, Low). Task marked done → auto-reorders.
USES SORTRegional Rankings Dashboard
Multi-key SORT groups sales by region then ranks reps within each. THE standard executive-review layout, formula-driven.
USES SORTAlphabetical Directory
SORT+UNIQUE builds a clean A-Z contact/product directory from any source. Add an entry → appears in its correct alphabetical slot.
USES SORTAuto-Sorted Activity Log
Newest-first log view via SORT by date descending. Data entry happens in random order; display stays chronological.
USES SORTRanked Feature List
Product feature voting sheet — team scores items, SORT orders by aggregate score descending. Live re-ranking as votes change.
USES SORTCustom-Order Report
SORTBY+MATCH pattern for month-of-year sorting, day-of-week reports, and any sequence where alphabetical isn't right.
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 →