This is part of a multi-part discussion of some of the new features introduced in Excel in 2019 and 2020. This post is about one of the new dynamic array functions. For a discussion of dynamic arrays, see the intro to this series.

Usage

SORT does exactly what its name suggests. However, because it updates automatically, it is often a superior choice than a pivot table.

Arguments

The arguments are as follows.

SORT(array, [sort_index], [sort_order], [by_col])
array = Columns included in the result. Can be entire or part of source data set.
sort_index = The column index on which to sort. Default is 1.
sort_order = 1 for ascending, -1 for descending. Default is 1.
by_col = If data is in rows instead of columns, set to TRUE. Default is FALSE.

Examples

These examples are all in the SORT demo workbook available for download. Note that data in the samples is fictional but is set in the context of a post-secondary institution.

Descending Order Sort

Using the list of student awards in range A2:D120, the following formula returns the set composed of student name, award name and amount (range B2:D120) sorted on the third column (the amount), in descending order.

=SORT(B2:D120,3,-1)

Multi-Column Sort

Using comma-separated arrays as arguments, it is fairly simply to define multiple sort columns and sort orders for each column.

To continue our example with student award data in A2:D120, we use SORT to retrieve 3 columns, the range B2:D120. Column 1 is the student name, column 2 the name of the award, and column 3 is the award amount. When we refer to index, we refer to the number of the column. To sort by column 2, then by column 3, we would put the 2 and then 3 inside curly brackets. {2,3}.

We’d also like to sort the awards in alphabetical ascending order, but continue to sort awards in descending order. For our sort_order argument, we then enclose a 1, then a -1, in curly brackets. {1,-1}.

Putting it all together, our SORT function is as follows:

=SORT(B2:D120,{2,3},{1,-1})

The SORTBY Function

When it is necessary to sort by a column that should not be included in the result, the SORTBY function can be used. The arguments for this function are as described below.

SORTBY(array, [sort_array], [sort_order])
array = Columns included in the result. Can be entire or part of source data set.
sort_array = The column on which to sort.
sort_order = 1 for ascending, -1 for descending. Default is 1.

In the following example, staff names, positions and annual salaries are list in a table in A1:D16. This is formatted as a table (<ctrl><T>) named Staff. Using named tables with descriptive column names makes formulas much more readable.

The desired result is a list of names and positions, sorted in descending order by salary, without showing the salary. The SORTBY function identifies the array as Staff[[First Name]:[Position]], selecting the first three columns. The second argument identifies Staff[Salary] as the sort column. The final argument of -1 orders the result set in descending order.

=SORTBY(Staff[[First Name]:[Position]],Staff[Salary],-1)

In the final example, on sheet SORTBY2 of the demo workbook that can be downloaded here, the SORTBY and IF functions are used in a simple auditing tool to identify check numbers issued out of date sequence.

SINGLE

There are situations when the SORT or FILTER functions return an array, but a scalar, or single, result is required. In these situations, the function can be prefaced with the @ sign, or nested inside the SINGLE function.

In the demo workbook on the SINGLE worksheet, the formula in B2 is =A2:A5. The result spills down from B2 to B5, as we have come to expect when working with array functions. However, in cell E2, the formula is =@D1:D5. In this case, the result is limited to a single value in E2. The same thing is accomplished with the =SINGLE(G2:G5) in cell H2.

Given a table named Employees in the range A1:D16, the SINGLE and SORTBY functions can be combined in a formula to return the name of the single highest-paid employee in the table.

=@SORTBY(Employees[Name],Employees[Salary],-1)

Looking Ahead

The SORT and SORTBY functions are deceptively simply. However, as we will begin to see in future posts, combining these with other functions unlocks some of their amazing potential.

Download the SORT demo workbook.

Here are links to the Microsoft documentation for the SORT, the SORTBY and the SINGLE functions.