SORT

How SORT works

SORT takes a range and reorders it. By default it sorts the first column ascending and spills the result. Pass a different sort_index (1-based) to sort by the second, third, or Nth column instead. Pass -1 as sort_order for descending.

The result is live: edit, add, or remove a row in the source and the spilled output re-sorts itself instantly. No re-clicking the sort button, no broken formulas pointing at stale rows.

When you need to sort by multiple keys (e.g. department ascending, then hire date descending within each department), reach for SORTBY instead. SORTBY accepts pairs of by_array + order, so you can stack as many sort keys as you need.

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

Arguments

SORT basic: alphabetize a name list

The simplest case: one column of names in random order, you want them alphabetized. SORT walks the column, returns it sorted ascending, and spills the result.

=SORT(A2:A11)

• •A2:A11 -> the column to sort (10 names, unordered)
• •sort_index -> omitted, defaults to 1 - sort by the only column there is
• •sort_order -> omitted, defaults to 1 - ascending (A to Z)
• •Spill -> 10 rows of input means 10 rows of output, in the cells below the formula

SORT descending: biggest orders on top

Two-column source, sort by the second column (Amount), and put the biggest numbers at the top. This is the everyday 'top N spenders' pattern that used to require manual re-sort every refresh.

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

• •A2:B11 -> the order log (Order ID + Amount, two columns)
• •2 -> sort_index - sort by the second column (Amount)
• •-1 -> sort_order - descending, so the biggest number lands at the top
• •Spill shape -> result is the same shape as the source: 10 rows by 2 columns

SORT by column index: highest paid first

Three columns wide: name, department, salary. Sort the whole block by salary descending. The sort_index is 3 because Salary is the third column inside the array argument - 1-based, counted from the leftmost column of the source range.

=SORT(A2:C10, 3, -1)

• •A2:C10 -> the roster (Name + Department + Salary, three columns)
• •3 -> sort_index - sort by the third column (Salary)
• •-1 -> descending, so the highest paid lands on top
• •Whole row moves -> when SORT reorders, every column travels together - the salary stays glued to its name

SORT + FILTER: top performers, sorted

FILTER subsets the rows that meet a threshold. SORT wraps that subset and orders it. The combination is the everyday 'top N who scored 80 or above' report - one formula, no helper columns, no manual re-sort when scores change.

=SORT(FILTER(A2:B11, B2:B11>=80), 2, -1)

• •FILTER(A2:B11, B2:B11>=80) -> inner step - keep only rows with a score of 80 or higher
• •SORT(..., 2, -1) -> outer step - sort the filtered rows by column 2 (Score) descending
• •6 of 10 rows clear 80 -> so the spill is 6 rows tall, not 10
• •Live -> edit any score in column B and the spill rewrites itself - filter and sort both refresh

SORTBY: department asc, hire date desc

When you need two or more sort keys, SORT alone runs out. SORTBY accepts pairs of by_array + order, so you can stack as many keys as you need. Here we group employees by department alphabetically, and within each department put the most recent hire on top.

=SORTBY(A2:C10, B2:B10, 1, C2:C10, -1)

• •A2:C10 -> the array to reorder (Name + Dept + Hire date)
• •B2:B10, 1 -> first key: department, ascending - groups the rows by dept alphabetically
• •C2:C10, -1 -> second key: hire date, descending - within each dept, newest first
• •Stack more keys -> add more pairs as the function grows: by_array3, order3, by_array4, order4, …

Where people go wrong

1. 01
   Passing 'asc'/'desc' or TRUE/FALSE for sort_order

   sort_order is the literal number 1 or -1, not a string and not a boolean. =SORT(A2:A10, 1, "desc") returns #VALUE!. =SORT(A2:A10, 1, FALSE) coerces FALSE to 0, also invalid.

   Fix: Use 1 for ascending, -1 for descending. Memorize the pair: 1 = up, -1 = down.
2. 02
   Spill blocked by content below

   SORT spills downward (and rightward for 2D ranges). If a cell underneath your formula already has data, you get a #SPILL! error. Same gotcha as FILTER and UNIQUE.

   Fix: Clear every cell inside the spill range, or move the formula somewhere with empty space below it. Hover the #SPILL! cell - Excel highlights the obstructing cell with a dashed outline.
3. 03
   sort_index of 0 or out of range

   sort_index is 1-based, not 0-based. =SORT(A2:C10, 0) returns #VALUE!, and so does =SORT(A2:C10, 4) when the source only has 3 columns. Off-by-one is the most common cause.

   Fix: Count your columns from the leftmost column of the array argument. First column is 1, second is 2, third is 3 - never 0.
4. 04
   Forgetting that the result is volatile to its source

   SORT does not snapshot the source. If a value in the source changes, the spilled output re-sorts itself instantly. Sometimes you want that; sometimes you wanted a frozen view of yesterday's order.

   Fix: If you need a snapshot, copy the spill (Ctrl+Shift+Down to select, Ctrl+C, Paste Values). Live SORT is great for dashboards, paste-values is great for archived reports.

Notes

• SORT is a dynamic-array function. The result spills into adjacent cells - you cannot put other content in the spill range without breaking it.
• Available in Excel 365 and Excel 2021. Older versions do not have it; share files cautiously.
• sort_index is 1-based, counted from the leftmost column of the array argument.
• sort_order is the literal number 1 (ascending) or -1 (descending) - never a string, never a boolean.
• Text comparison is case-insensitive. "alpha" and "ALPHA" sort as equal.
• Pair with FILTER and UNIQUE for the canonical 'subset, dedupe, sort' workflow in one formula.
• Reach for SORTBY when you need multiple sort keys or want to sort by a column that is not in the array argument.

What to learn next

Now prove it