SORT Function. Sort range of cells.(Microsoft Excel)


Japanese version.

The SORT function is a Spill related function implemented in 2019.
It is a function to sort a given list in the order of specific columns.
It works similarly to sorting data.

How it works

arrayRequired argument.
Cell range to sort.
sort_index1Specify the column or row to be sorted by a number with the first digit as 1.
sort_order1Sort order.
1…ascending order
-1…Descending order
by_colFALSESort direction.
Column (horizontal) direction
Row (vertical) direction


Argument 1: array

When the SORT function is entered, the result is displayed with the cell range specified in argument 1 sorted.

The function will automatically expand to cells other than the cell where the formula was entered, but these cells are called ghost cells and are not editable. If only argument 1 is specified, the cells are sorted in ascending order by the first column.

Argument 2: sort_index

Argument 2 specifies the column or row on which to base the sorting by a numerical value, with the first row as 1.

If omitted, it is set to 1.

Argument 3: sort_order

Specify 1 for argument 3 for ascending order, -1 for descending order.
If omitted 1.

Argument 4: by_col

If FALSE is specified for argument 4, the rows are sorted vertically; if TRUE, the columns are sorted horizontally. TRUE is specified for sorting in the column direction.

Comparison with other functions

SORTBY Function

The differences from the SORTBY function added at the same time are shown in the table below.

Sorting criteria1 column only.Multiple columns.
Specify columnsNumerics.Cell range.
Sort directionRow or column.Row only.

Combination with other functions.

FILTER function.

The results of the FILTER function can be reordered by specifying the result of the SORT function as argument 1.

VSTACK and HSTACK functions.

The VSTACK and HSTACK functions allow multiple cell ranges to be arguments to the SORT function.



Microsoft Excel Functions Lookup & Reference

Examples of Use

Formulas to randomly reorder a range of cells.

Formulas to sort text in cells.

Formula for cross tabulation in Spill.