SORT function
SORT returns a sorted copy of a range that updates when the data changes, without touching the original. It spills into the cells below, so those must be empty.
Example
Read it as a sentence: sort the rows of B2:E7 by their 3rd column, from largest to smallest, and show the first value, which gives Candle.
Syntax
SORT(array, [sort_index], [sort_order], [by_col])
Arguments in [square brackets] are optional.
-
array(required) - Range or array 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) - TRUE to sort columns instead of rows.
Returns: The array in sorted order.
One SORT formula can fill more than one cell. When the result has several values, they spill into the cells below or to the right of the formula.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=SORT({3;1;2}) | 1 | Vertical |
=SORT({3,1,2}) | 3 | Horizontal, default by_col |
=SORT({3,1,2},,,TRUE) | 1 | by_col TRUE |
=SORT({"b";"A";"a";"C"}) | A | Case of text |
=SORT({3;"a";TRUE;1;"10"}) | 1 | Mixed types |
=SORT(Items!B2:E7,3,-1) | Candle | By column 3 descending |
=SORT(Items!B2:E7,5) | #VALUE! | Sort index past columns |
=SORT(Items!B2:B7,,0) | #VALUE! | Order 0 |
The Items sheet these formulas read
Watch out for
- It spills; the cells below must be empty.
- Sorting is by column index within the array, not the sheet column.
- Pair with FILTER or UNIQUE for dynamic reports.
- SORT reorders rows. A one-row list like {3,1,2} comes back unchanged unless by_col is TRUE: =TEXTJOIN(",",,SORT({3,1,2})) returns "3,1,2" (measured in Excel 16.0.20527).
Where it works
- Excel
- SORT arrived in Excel 2021. Older versions do not have it.
- Google Sheets
- Same name, but behaves differently. Sheets SORT(range, sort_column, is_ascending, ...) takes TRUE/FALSE for order, not 1/-1, and supports several sort columns.
Part of Lookup and reference functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .