SORT Function

Sorts a range with a formula, so the result updates automatically.

Category

Lookup & Reference

Available in

Microsoft 365, Excel 2021+

Returns

A spilled, sorted array

Taught hands-on in Level 4: Advanced Dynamic Arrays & Logic · Dynamic Arrays module

Overview

SORT returns a sorted copy of your data without touching the original. Change the source and the sorted list updates instantly.

Choose which column to sort by, ascending or descending, and even sort across columns instead of rows.

Syntax

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

Arguments

ArgumentRequired?What it does
arrayYesThe data to sort.
sort_indexOptionalWhich column (or row) to sort by. Default 1.
sort_orderOptional1 = ascending (default), -1 = descending.
by_colOptionalFALSE = sort rows (default), TRUE = sort columns.

Examples

Example 1 — Highest sales first

Sort the table by column 2, descending.

D2fx=SORT(A2:B4, 2, -1)
ABCDE
1RepSalesRepSales
2Ana400Cara500
3Ben350Ana400
4Cara500Ben350
=SORT(A2:B4, 2, -1) Explain

Example 2 — Sort by two columns

Region A→Z, then sales high→low within each region.

=SORT(A2:C100, {2, 3}, {1, -1}) Explain
Combine with FILTER and TAKE for a live top 5: =TAKE(SORT(A2:B100, 2, -1), 5).

Common mistakes

  • #SPILL! errors. Cells in the spill area aren't empty.
  • Including headers. Start the array below the header row, or the header gets sorted into the data.

Notes

Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.