Syntax:
SORT(Range [; SortIndex] [; SortOrder] [; ByColumn])
Description:
This function sorts a cell range and returns it as an ascending or descending list.
Range is the cell range or array from which the values are to be sorted and returned. The range can have one or more columns (or rows for tables that are to be read horizontally).
SortIndex (optional) specifies the column to be sorted. If omitted, the default value 1 is used.
SortOrder (optional) specifies whether the values are to be sorted in ascending or descending order. If set to 1, sorting is in ascending order and if set to -1, sorting is in descending order. If omitted, the default value 1 is used.
ByColumn (optional) specifies whether to sort by column or by row. FALSE sorts in the direction of the column and TRUE in the direction of the row. If omitted, FALSE is used as the default value and the table is thus sorted vertically.
Note:
You can also apply the function to multi-column tables and the formula will automatically "spill" over into the adjacent cells as required. In addition, the result is automatically updated when changes are made to the initial list. For this reason, such functions are also called dynamic matrix formulas.
If you apply the function to multi-column tables and this causes a spill over to adjacent cells in the results area, a #SPILL! error value will be displayed if those cells are already filled with content.
Compatibility notes:
Microsoft Excel supports this function only in version 2021 or later. The function is unknown in older versions.
Examples:
Example 1: List sorted for column 1 across one column
There is a list with several first names in column A and their corresponding department in column B.
The formula SORT is applied in cell D2:
SORT(A2:A9) returns a sorted list of names. Since not specified, the default value (1 = column A) was used for the SortIndex, and the default value (1 = ascending) was used for the SortOrder. Sorting is performed in vertical direction, as byColumn also uses the default value (FALSE).
Example 2: List sorted for column 1 across several columns
There is a list with several first names in column A and their corresponding department in column B.
The formula SORT is applied in cell D2: This example shows that the function automatically extends the result to the adjacent column E ("spill"):
SORT(A2:B9) returns a sorted list of names and their corresponding department, as the Range extends across several columns here. Otherwise, as in example 1, the default values were used here too (nothing specified).
Example 3: List sorted for column 2 across several columns and in descending order
Once again, there is a list with several first names in column A and their corresponding department in column B.
The formula SORT is applied in cell D2:
SORT(A2:B9, 2, -1) returns a sorted list of departments in column E (due to the parameter 2 for SortIndex) and the corresponding names in column D. Due to the parameter -1 for SortOrder, the departments are sorted in descending order.
See also: