SORT and SORTBY

Sorting in Excel used to mean physically rearranging your data — a destructive, manual operation that you had to redo every time numbers changed. SORT and SORTBY changed that completely. Both functions return a sorted copy of your data as a dynamic spill result, leaving the source untouched. SORT handles the common case of sorting by a column within the returned range. SORTBY is more powerful — it can sort by any array, including columns that are not part of the output at all. Together they are the foundation of every leaderboard, ranked table, and automatically ordered report in modern Excel.

SORT — Sort by a Column in the Return Range

SORT takes a range and returns it sorted. You specify which column to sort by using a column index number — column 1 being the first column in the array you passed in, not column A of the sheet.

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

  array       →  The range to sort
  sort_index  →  Which column number to sort by (default: 1)
  sort_order  →  1 = ascending A→Z or smallest first (default)
               -1 = descending Z→A or largest first
  by_col      →  FALSE = sort rows top-to-bottom (default)
                 TRUE  = sort columns left-to-right

=SORT(A2:D10)            →  Sort by column 1, ascending
=SORT(A2:D10, 4, -1)     →  Sort by column 4, descending (largest first)
=SORT(A2:D10, 2)         →  Sort by column 2, A to Z

SORT by Multiple Columns

You can sort by more than one column by passing arrays as the sort_index and sort_order arguments. Excel sorts by the first criterion, then uses the second to break ties, and so on. Wrap the values in curly braces to pass multiple at once.

Sort by Region A-Z, then by Sales descending within each region:
=SORT(A2:C10, {2, 3}, {1, -1})

  {2, 3}   →  Sort by column 2 first, then column 3
  {1, -1}  →  Column 2 ascending, column 3 descending

Sort by Quarter then by Rep name:
=SORT(A2:D20, {2, 1}, {1, 1})

SORTBY — Sort by Any Array

SORT can only sort by columns that are part of the array being returned. SORTBY removes that restriction entirely. You pass the array to return and then separate sort key arrays — and those sort key arrays do not need to be in the returned range at all. This makes SORTBY indispensable when you want to sort output by a computed value or a column you do not want to include in the result.

=SORTBY(array, by_array1, [sort_order1], [by_array2], [sort_order2], ...)

  array      →  The range to return
  by_array1  →  The array to sort by (any range or calculated array)
  sort_order →  1 = ascending (default),  -1 = descending

Return only Rep names, sorted by their Sales (Sales column not in output):
=SORTBY(A2:A6, C2:C6, -1)

Return names and regions, sorted by Sales descending:
=SORTBY(A2:B6, C2:C6, -1)

A practical example: you want to show a name-and-department list sorted by salary, but you do not want the salary column to appear in the output. SORT cannot do this — the sort column must be in the returned array. SORTBY handles it naturally: return A2:B20 (name and department), sort by D2:D20 (salary), and salary never appears in the spill result.

SORTBY With Multiple Sort Keys

SORTBY supports multiple by_array / sort_order pairs, making it ideal for hierarchical sorting — sort by department first, then by salary within each department, using columns that may or may not appear in the output.

Employee data: A=Name, B=Department, C=Start Date, D=Salary

Show Name and Department, sorted by Department A-Z
then by Salary descending within each department:
=SORTBY(A2:B20, B2:B20, 1, D2:D20, -1)

Show Name only, sorted by Start Date (oldest first):
=SORTBY(A2:A20, C2:C20, 1)

Show Name and Salary, sorted by Department then Start Date:
=SORTBY(A2:A20, B2:B20, 1, C2:C20, 1)

SORTBY With Calculated Sort Keys

Because by_array can be any array — not just a column from the sheet — you can sort by a calculated value. This is one of the most powerful and underused features of SORTBY. Sort by absolute variance from a target, by percentage, by a formula result, or by a transformed version of a column.

Sort by absolute distance from a target of £100,000
(closest to target first, regardless of over or under):
=SORTBY(A2:C6, ABS(C2:C6 - 100000), 1)

Sort by percentage of target achieved:
=SORTBY(A2:C6, C2:C6 / 100000, -1)

Sort names alphabetically by last name
(where A2:A6 contains "First Last" format):
=SORTBY(A2:A6, TEXTAFTER(A2:A6, " "), 1)

The SORTBY(A2:A6, TEXTAFTER(A2:A6, " "), 1) example is a classic real-world use case — sorting a list of full names by last name, when the last name is the part after the space. No helper column needed, no manual splitting, just a calculated sort key passed directly into SORTBY.

Combining SORT and SORTBY With FILTER

In most real dashboards and reports, you want to filter and sort together. SORT and SORTBY nest cleanly inside or around FILTER — producing filtered, sorted output from a single formula.

North region rows, sorted by sales highest first:
=SORT(FILTER(A2:C20, B2:B20="North"), 3, -1)

North region rows, sorted by start date oldest first
(date not in returned columns):
=SORTBY(FILTER(A2:C20, B2:B20="North"), FILTER(D2:D20, B2:B20="North"), 1)

Unique regions sorted alphabetically:
=SORT(UNIQUE(B2:B20))

The SORTBY(FILTER(...), FILTER(...)) pattern is the key to sorting filtered output by a column that is not in the result. Both FILTER calls use the same condition, so the sort key array aligns row-for-row with the returned data. This combination is used extensively in professional dashboards.

SORT vs SORTBY — When to Use Each

Next up — UNIQUE, where you will learn how to extract deduplicated lists, count distinct values, and build dynamic category headers that update automatically as your data grows.