Description
The Excel SORT function sorts the contents of a given range or array and returns a dynamic array result. The values can be sorted by one more multiple columns.
Syntax
SORT( array, [sort_index], [sort_order], [by_col] )
Arguments
array – The range or array to sort
sort_index – [optional] A number representing the row or column to sort by
sort_order – [Optional] A number indicating the sort order (1 = ascending, -1 = descending)
by_col – [Optional] A logical value indicating whether to sort by row or column
Return value
A sorted range or array
How to use the SORT function in Excel
Use the Excel SORT function to sort a given range or array by one or more columns. The result is a dynamic array of values that will be spilled into the surrounding cells if directly entered into a cell (not wrapped by another function). Every time a value changes in the source range, the SORT function automatically updates and re-sorts the range.
In the above example, we sort the range B6:B13 by the second column (2) in a descending order (-1) using the following formula:
= SORT(B6:C13, 2, -1)
If no optional arguments are provided, SORT will sort by the first column in an ascending order.
SORT by multiple columns
The SORT function also allows you to sort by multiple columns at once. To to that, you have to provide an array constant in curly brackets like this:
= SORT(B6:C13, {2,4,5}, {-1,1,1})
In the above statement, the range B6:C13 is sorted by the column two in a descending order, then by column 4 in an ascending order, and finally by column 5 in an ascending order.
Sort by another range
If you need to sort values in a range based on another range, the SORTBY function is what you need. SORTBY allows you to separately define the array that will be sorted and the array to be sorted by.
Formula examples
Related Tutorials
Related Functions
Excel FILTER function
The Excel FILTER function filters a range of data based on given criteria. Unlike the common lookup functions, FILTER returns all matching rows.
Excel UNIQUE function
The Excel UNIQUE function returns a list of unique values in a given range. The result is returned as a dynamic array.
Excel SORTBY function
The Excel SORTBY function sorts the contents of a given range or array based on the values of another range or array. It returns a dynamic array result.
