A Simple Sort with One Argument
August 12, 2022 - by Bill Jelen

Sorting with a formula in Excel used to require an insane combination of formulas. Take a look at the following data, which is used throughout this example.

To greatly simplify our sorting in such a situation, Joe McDaid and his team have brought us SORT
and SORTBY
.
Let's start with SORT
. Here is the syntax: =SORT(array, [sort_index], [sort_order], [by_col])
.

Let's say you want to sort A3:C16 by the Score field. Score is the third column in the array, so your sort_index value will be 3.
The choices for the sort_order value are 1 for ascending or -1 for descending. I am not complaining, but there will never be support for sorting by color, formula, or a custom list using this function.

The fourth argument will rarely be used. It is possible in the Sort dialog to sort by columns instead of rows, but 99.9% of people sort by rows. If you need to sort by columns, specify True for the final argument, by_col. This argument is optional and defaults to False.

Here are the results of the single formula entered, in this case, in cell O2. Thanks to the new calc engine, the formula spills into adjacent cells.

A Sort Based on Two or More Columns of Results
What if you need a two-level sort, such as sorting by column two ascending and column three descending? In this case, you supply array constants for the second and third arguments: =SORT(A2:C17,{2;3},{1;-1}).

This article is an excerpt from Power Excel With MrExcel
Title photo by Andre Taissin on Unsplash