How to use SORTBY Function in Excel

The Excel SORTBY function sorts the contents of a range or array based on the values from another range or array. The range or array used to sort does not need to appear in results.

Syntax:= SORTBY (array, by_array, [sort_order], [array/order], …)

The SORTBY function syntax has the following arguments:

  • array: The range of cells, or array of values to be returned by the function
  • By_array1:  The range of cells or array of values to sort by.
  • [sort_order1]: 1 = sort By_array1 in ascending order, -1 = sort By_array1 in descending order (if excluded it will default to 1).
  • [By_array2…]:  The range of cells or array of values to apply the second sort by.  This argument is entirely optional; you can exclude this if you only need one sort column.
  • [sort_order2]: the sort order to apply to the By_array2 1= ascending, -1 = descending.

Example: Let’s look at some Excel SORTBY function examples and explore how to use the SORTBY function as a worksheet function in Microsoft Excel:

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

Syntax: =SORTBY(A2:E17;C2:C17;1;E2:E17;1)

Result:

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:

Syntax: =SORTBY(A2:A17;RANDARRAY(16))

Note: The 16 in the RANDARRAY tells it to return 16 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.

Note:

  • All arguments must have compatible dimensions, i.e. array and by_array1 must contain the same number of rows.
  • The by_array arguments can only be one row or one column.
  • The sort_order argument can only be -1 (descending) or 1 (ascending). If no value is provided, SORTBY will sort in ascending order.

Add a Comment

Your email address will not be published. Required fields are marked *