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
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
| Argument | Required? | What it does |
|---|---|---|
| array | Yes | The data to sort. |
| sort_index | Optional | Which column (or row) to sort by. Default 1. |
| sort_order | Optional | 1 = ascending (default), -1 = descending. |
| by_col | Optional | FALSE = 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)
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Rep | Sales | Rep | Sales | |
| 2 | Ana | 400 | Cara | 500 | |
| 3 | Ben | 350 | Ana | 400 | |
| 4 | Cara | 500 | Ben | 350 |
=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

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.