# SORTBY Function Examples – Excel

Written by

Reviewed by

*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**

- Ensure that the arguments
*[array]*and*[byarray]*have the same number of rows:

**This will work**#### This will NOT work

- 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. - 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*.

- SORTBY can be used with other Dynamic Array Functions such as FILTER to create more versatile formulas.
- 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.