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.

The original data has name in column A, team in column B (either Red or Blue) and Score in column C. Currently, the data is sorted by Name. — Figure 608. Data in

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]).

The syntax for the SORT function is array, sort index, sort order, by column. Only the first argument is required - the others are optional. — Figure 609. The

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.

So far, the formula says =SORT(A3:C16,3, The tooltip for Sort Order shows that you use 1 for Ascending or -1 for Descending. If you leave this argument off, the default is ascending. — Figure 610. Specify

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.

The fourth argument is By Column. True will sort by column. False with sort by row. Choosing True here will be equivalent to using Sort Left to Right in the Sort Options dialog box. If you don't use the fourth argument, it defaults to False which does a normal sort of the rows. — Figure 611. If you need to sort by columns, use

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.

The original data is sorted by score descending using a formula of =SORT(A3:C16,3,-1,False). — Figure 612. The original data is now sorted.

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}).

Can you do a two level sort with the SORT function? You have to use an array constant to pass two values to both the second and third argument. Sort by columns {2;3} and {1;1}. In English, this means sort by column 2 ascending and column 3 descending. — Figure 613. Two-level sort.

This article is an excerpt from Power Excel With MrExcel

Title photo by Andre Taissin on Unsplash