SORT - Order records and keep their fields together
Build a descending sales report or an ascending directory using a spilled copy of the source rather than rearranging it.
By the end, you can
- Choose a sort column within the returned array.
- Use 1 or -1 for order.
- Keep related record fields aligned.
Before you start: Understand numeric versus text values and spilled output cells.
In this lesson
A sorted result without editing source order
SORT returns an array ordered by a column or, with by_col, by a row. A sales manager can view highest revenue first while preserving the original input order. Return the whole record when names, revenue, and city must travel together; sorting one column alone is not a safe way to rebuild a joined report.
Syntax and arguments
=SORT(array, [sort_index], [sort_order], [by_col])Defaults are first sort index, ascending order, and sorting rows.
| Argument | Purpose | Example |
|---|---|---|
arrayRequired | The record array to order. | A2:C5 |
sort_indexOptional | 1-based column within array for row sorting, or row for column sorting. | 2 |
sort_orderOptional | 1 ascending (default), -1 descending. | -1 |
by_colOptional | FALSE sorts rows; TRUE sorts columns using a row as the key. | FALSE |
Revenue-ranked sales records
Meera| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Salesperson | Revenue | City | Ranked name | Revenue | City | |
| 2 | Anika | ₹12,500 | Delhi | Meera | ₹15,000 | Pune | |
| 3 | Dev | ₹9,800 | Mumbai | Anika | ₹12,500 | Delhi | |
| 4 | Meera | ₹15,000 | Pune |
Name order and a standalone numeric ranking
=SORT(A2:C5)With defaults, the names determine row order and the other fields remain attached.
=SORT(B2:B5,1,-1)A one-column result is suitable when the report needs only the ranked amounts, not names.
For sorting across columns, by_col TRUE uses a row within the array as the sort key. When several ordered tie-breakers are needed, SORTBY can express a key sequence more clearly.
Spill and version rules
- SORT returns a copy as a spill; it does not physically reorder the original cells. A blocked result area gives #SPILL!.
- Keep the output anchor outside an Excel Table. Use structured references for an expanding source if new records must be included.
- Numeric values and numeric-looking text can sort differently. Clean imported amounts before calling a report a numeric ranking.
- SORT is available in Microsoft 365 and Excel 2021/2024. A single-key sort does not express a business tie-break rule; add an explicit secondary key when that rule matters.
Common mistakes and Practice scope
Check your understanding
Choose an answer for instant feedback. You can try again; answers stay only on this page.