SORT

Excel Functions

Sorts range or array

Syntax

EXCEL

=SORT(array, [sort_index], [sort_order], [by_col])

Arguments

Parameter	Description
`array`	Range or array to sort.
`sort_index`	[optional] Column index to use for sorting. Default is 1.
`sort_order`	[optional] 1 = Ascending, -1 = Descending. Default is ascending order.
`by_col`	[optional] TRUE = sort by column. FALSE = sort by row. Default is FALSE.

Return Value

Sorted array

Details

The SORT function sorts the contents of a range or array in ascending or descending order. The result is a dynamic array of values that will "spill" onto the worksheet. If values in the source data change, the result from SORT updates automatically. Note that the SORT function cannot sort data in place like Excel's Sort command on the Ribbon. SORT always outputs results to a new location. By default, SORT sorts values in ascending order using the first column in array. Use sort_index to specify which column (or row) to sort by, and sort_order to control direction: 1 for ascending, -1 for descending. To sort horizontally by columns instead of rows, set by_col to TRUE. SORT only accepts a single value for sort_index, but you can sort by multiple columns at once using array constants. For exa

Examples

Basic usage

To sort in ascending or descending order:

EXCEL

=SORT(A1:A10) // sort A-Z (ascending)
=SORT(A1:A10,,-1) // Z-A (descending)

Basic usage

To sort by a specific column:

EXCEL

=SORT(A1:B10) // sort by column 1, ascending
=SORT(A1:B10,2) // sort by column 2, ascending
=SORT(A1:B10,2,-1) // sort by column 2, descending

Simple A-Z sort

In its simplest form, the SORT function sorts a single column of data in ascending order. In the worksheet below, the goal is to sort names in column

EXCEL

=SORT(B5:B16)

Sort by specific column

When data has multiple columns, use sort_index to specify which column to sort by. In the worksheet below, the goal is to sort names and scores by sco

EXCEL

=SORT(B5:C16,2,-1)

Sort data horizontally

The SORT function has the ability to sort data vertically (by row) or horizontally (by column). To sort data horizontally, set by_col to TRUE. In the

EXCEL

=SORT(B4:K5,2,-1,TRUE)

Filter on top n values

The SORT function pairs well with FILTER to filter and sort data in one step. In the worksheet below, the goal is to extract the top n scores (where n

EXCEL

=SORT(FILTER(B5:C16,C5:C16>=LARGE(C5:C16,F2)),2,-1)

SORT

Syntax

Arguments

Return Value

Details

Examples

See Also