SORT vs. SORTBY in Microsoft Excel: Which Should You Use?

The SORT and SORTBY functions let you extract certain columns and rows from a dataset and sort them in a certain order, all while preserving the source data. Even though they work in similar ways, there are key differences you should be aware of if you’re deciding which one to use.

The SORT and SORTBY Syntaxes

Before I go into the different behaviors of the SORT and SORTBY functions in Excel, here’s a quick overview of their syntaxes and a straightforward example of each in use.

Microsoft Excel’s SORT function has four arguments—one required and three optional:

=SORT(a,b,c,d)

where

• a (required) is the array (a whole table or part of a table) containing the columns or rows you want to sort,
• b (optional) is an index number indicating which column or row to sort by,
• c (optional) is a number determining the sort order (1 = ascending; -1 = descending), and
• d (optional) is a Boolean value indicating the sort direction (FALSE = sort vertically by row; TRUE = sort horizontally by column).

If argument b is omitted, the first row or column is returned. If argument c is omitted, Excel defers to an ascending order. If argument d is omitted, the data is sorted vertically by row.

In this example, typing:

=SORT(T_Scores,5,-1)

extracts and sorts the whole T_Scores table by the Total column (5) in descending order (-1).

Whether you’re using the SORT or SORTBY function, to avoid the table’s column headers being considered data rows in the sorting process, make sure that argument a only includes the rows beneath the header row. Then, duplicate the column headers directly above the result by typing them manually or using a formula, such as:

=T_Scores[#Headers]

On the other hand, the SORTBY function has two mandatory arguments, and the number of optional arguments you use depends on the number of rows or columns by which you want to sort the data:

=SORTBY(a,b¹,b²,c¹,c²...)

where

• a (required) is the array (a whole table or part of a table) containing the columns or rows you want to sort,
• b¹ (required) is the first array to sort by,
• b² (optional) is a number determining the sort order of the first sort array (1 = ascending; -1 = descending),
• c¹ (optional) is the second array to sort by, and
• c² (optional) is the second sort order number.

In other words, b¹ and b² are the first array-order pairing, c¹ and c² are the second array-order pairing, and the maximum number of these pairings is determined by the number of rows or columns in the dataset you want to sort.

If you only have one sort-by argument (b¹), you can omit the sort-order argument (b²), and the result will default to an ascending order. However, if you have more than one sort-by argument, they must all be accompanied by a sort-order argument.

For example, typing:

=SORTBY(T_Scores,T_Scores[Total],-1)

extracts and sorts the whole T_Scores table by the Total column (T_Scores[Total]) in descending order (-1).

Compatibility With Different Versions of Excel

On Windows and Mac, both the SORT and SORTBY functions are supported in standalone versions of Excel released in 2021 or later and Excel for Microsoft 365. You can also use these functions in Excel for the web, the free online version of the program. Alternatively, the SORT and SORTBY functions are available to those using the Excel mobile and tablet apps or the Microsoft 365 Copilot mobile app.

Identifying the Sort-By Array

I often prefer using the SORTBY function because it’s more reliable than the SORT function when columns are added to or removed from the source data.

Here, after extracting the T_Scores table and sorting it by the Total column in descending order using the SORTBY function, I inserted a column in the source dataset. Because the SORTBY function identifies the sort-by column by a selected array or column name, the result is still sorted by the Total column.

However, if I insert a column in the source data after using the SORT function, the index number in the sort-by argument doesn’t recognize the change to the structure of the source dataset. In other words, the Total column is now column 5, but the formula still references column 4. As a result, I either need to update the formula manually or find a way to automate this process.

So, if you envisage your data changing shape, the SORTBY function will be the better option in the long run.

Extracting and Sorting a Single Column

The most basic use of the SORT and SORTBY functions is to extract and sort a single array from a dataset.

Suppose you want to extract and sort alphabetically the teams from this T_PL table, which is currently sorted by the Capacity column from largest to smallest.

To do this using the SORT function, you need to type:

=SORT(T_PL[Team])

Because this function automatically sorts the data by the first column in ascending (alphabetical) order if only the first argument is given, the formula is nice and short.

On the other hand, to do the same using the SORTBY function, you need to type:

=SORTBY(T_PL[Team],T_PL[Team])

since this function requires you to input both an array and a sort-by array.

Thus, the SORT function is much more efficient than the SORTBY function when extracting and sorting a single array from a dataset, especially if you want the result to be in ascending order.

Extracting and Sorting Data by Multiple Levels

One of the biggest differences between the SORT and SORTBY functions is the method you need to use to extract and sort your data by more than one variable.

In this example, let’s say you want to extract and sort the T_Scores table by the Total column, and where two values in this column are the same (in this case, Tom’s and Dave’s totals), you want to sort the data alphabetically by the players’ names.

To achieve this using the SORTBY function, whose syntax is specifically designed to cater for multilevel sorting, you should type the following formula:

=SORTBY(T_Scores,T_Scores[Total],-1,T_Scores[Name],1)

where

• T_Scores is the name of the table you want to extract and sort,
• T_Scores[Total],-1 as the first sort-by-sort-order pairing tells Excel that the primary column by which you want to sort the array is the Total column, in descending order, and
• T_Scores[Name],1 as the second sort-by-sort-order pairing tells Excel that the secondary column by which you want to sort the array is the Name column, in ascending (alphabetical) order.

Notice how the extracted data is sorted by the Total column, and Dave and Tom—whose totals are the same—are sorted alphabetically.

On the other hand, if you try to achieve the same outcome using the SORT function, you’ll hit a hurdle, since—on face value—it only allows you to input one sort array and one sort-by index number. However, you can get over this hurdle by using array constants:

=SORT(T_Scores,{4,1},{-1,1})

where

• T_Scores is the name of the table you want to extract and sort,
• {4,1} tells Excel that you first want to sort the data by the fourth column before then sorting it by the first column, and
• {-1,1} tells Excel that the fourth column should be in descending order, and the first column should be in ascending order.

As with the SORTBY example above, the result extracts and sorts the data by the Total column, and where any values are equal, the data is then also sorted by the Name column.

Overall, both the SORTBY and SORT functions can sort data on more than one level, and both have their advantages. If you plan to share your workbook with others, they may be more familiar with using conventional formula syntax without array constants, so using the SORTBY function would be the preferred option. On the other hand, the SORT formula containing array constants is much shorter, so if you’re looking to save time, this could be the best route.

However, as I discussed earlier, the SORTBY function is better at coping with new columns being added to the source data, as it doesn’t rely on column index numbers. As a result, this is always something to bear in mind when choosing which function to use.

Result Behavior

The beauty of both the SORT and SORTBY functions is that if the values in the source data change, they’re reflected in the sorted result. What’s more, SORT and SORTBY are dynamic array functions, meaning that if the source data is in a formatted Excel table, the result grows or shrinks if you add or delete rows.

You know that a function produces dynamic arrays when (1) the result spills from the cell where you typed the formula to adjacent cells, and (2) a temporary blue border appears when you select any cell in the result. Here, even though the SORTBY formula is typed into cell F2, the result spills from cell F2 to cell I5.

However, with this dynamism comes a major drawback: you can’t use the SORT and SORTBY functions within a formatted Excel table. This is because Excel tables aren’t compatible with dynamic array formulas, since both these Excel features are designed to grow and shrink automatically.

You might also face problems if the sort array argument for either function references a dataset in another Excel workbook. Specifically, dynamic array functions referencing external sources only work when both workbooks are open. So, if you close the source workbook, any linked dynamic array formulas return a #REF! error when refreshed.

Summary: Should You Use SORT or SORTBY?

Whether you use the SORT function or the SORTBY function depends on many factors, such as whether you plan to insert columns in the source dataset, the number of sort levels you want to apply, and whether you plan to share your workbook with others.

Here’s a summary of the key similarities and differences:

To extract and sort non-adjacent arrays in your data, nest the CHOOSECOLS or CHOOSEROWS functions inside your SORT or SORTBY formula. Alternatively, to extract, filter, and sort columns from a dataset simultaneously, nest the FILTER function instead.