Practice free

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.

Practice this in the app

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.

FormulaExcel showsCase
=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 .