SORTBY Function Examples – Excel

This tutorial demonstrates how to use the SORTBY Function in Excel to sort values based on a list of corresponding values.

How to use the SORTBY Function

Sort Ascending Order

To sort the employees in A2:A7 in the ascending order of the hours they worked in B2:B7, we enter the following formula in D2:

=SORTBY(A2:B7, B2:B7)

If we only want to return the list of sorted employees, we enter the following formula in D2:

=SORTBY(A2:A7, B2:B7)

You’ll notice that we’ve set the [array] argument to A2:A7 as we only want the first column returned.

Sort Descending Order

To sort the employees in A2:A7 in the descending order of the hours they worked, we enter the following formula in D2:

=SORTBY(A2:B7,B2:B7, -1)

You’ll notice that the third argument [sort_order] is set to -1 so as to sort the number of hours in descending order. When set to 1 the formula sorts the hours in ascending order as shown in the previous section.

Sort Multiple Columns

To sort the employees in A2:A7 by the second column (hours worked) and then by the first column (employees), enter the following formula in D2:

=SORTBY(A2:B7, B2:B7, 1, A2:A7, 1)

SORTBY Issues

#SPILL!

This error occurs when there is a value in the Spill Range i.e. the range where the SORTBY Function places its results.

To correct this error, clear the range that Excel highlights.

SORTBY Tips and Tricks

1. Ensure that the arguments [array] and [byarray] have the same number of rows:
   

   This will work

   

   This will NOT work

   

2. The [sort_order] can only either be 1(ascending) or -1(descending), when no value is given for this argument the SORTBY function defaults to sorting in ascending order.
3. Ensure that the cells below the input cell are blank to avoid the Spill Error, learn more about the Spill Error ‘here’ — add link to Intro to DAFs.
   
4. SORTBY can be used with other Dynamic Array Functions such as FILTER to create more versatile formulas.
5. You can use the SORT Function in place of the SORTBY Function to sort values by another range or array.

=SORT(A2:B7,2)

The only limitation here is that we have to include the column we use to sort in the output when we use SORT.