Excel SORTBY Function

The Excel SORTBY function sorts the contents of a range or array based on the values in a corresponding range or array.  It can sort by multiple columns.  The syntax is:

=SORTBY(array, by_array1, [sort_order1], [[by_array2], [sort_order2]],…)

array is the range or array containing the values you want sorted.

by_array1 is the array or range to sort on.

sort_order1 is optional. It’s a number; 1 for ascending and -1 for descending. If omitted, it will sort in ascending order.

by_array2 is a second optional array or range to sort on.

sort_order2 is optional sort order for the second by_array. Use 1 for ascending and -1 for descending. If omitted, it will sort in ascending order.

Note: The SORTBY function is part of the new Excel Dynamic Arrays family. At the time of writing, Dynamic Arrays are only available in Office 365 and are currently in beta on the Insiders channel. Excel 2019 will not have the Dynamic Array functions.

Excel SORTBY Function Examples

Let’s say we want to sort the table in cells B17:D25 by the employee’s Start Date and then by Salary in ascending order. We can do this easily using the SORTBY Function as shown below:

Sort Randomly

We can use the RANDARRAY function, which is another of the new dynamic array functions, to randomly sort a list. This could be used as an alternative to randomly drawing names out of a hat, as shown in the example below:

Note: The 9 in the RANDARRAY tells it to return 9 random numbers between 0 and 1.

The bonus with this solution is there are no repeats.

Tip: The RANDARRAY function is volatile, so once you have your results be sure to copy and paste them as values, so they don’t keep changing every time you edit a cell etc.

Download the Workbook

Related Tutorials