Excel SORTBY Function
Summary
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.
Purpose
Sorts range or array by column
Return value
Sorted array
Syntax
=SORTBY (array, by_array, [sort_order], [array/order], ...)
Arguments
- array - Range or array to sort.
- by_array - Range or array to sort by.
- sort_order - [optional] Sort order. 1 = ascending (default), -1 = descending.
- array/order - [optional] Additional array and sort order pairs.
Version
Usage notes
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 the source data. In the example shown, the formula in E5 is:
=SORTBY(B5:B14,C5:C14,-1) // sort by grades descending
The output in E5:E14 is the list of names sorted by scores in descending order. To sort by grades in ascending order use:
=SORTBY(B5:B14,C5:C14,1) // sort by grades ascending
Notes
- 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.
SORTBY is a new function available in Excel 365 only.
SORTBY formula examples
In the example shown, we want to sort data in B5:D14 first by group in descending order. Here is the configuration needed:
array = B5:D14
by_array1 = D5:D14
sort_order1 = 1
The formula below will sort data by...
The SORTBY function can sort values in a range with an array that doesn't exist on the worksheet.
In this example, we want to sort the values in B5:B15 by the number of characters each string contains. Working from...
The new dynamic array formulas in Excel 365 make it much easier to solve certain tricky problems with formulas.
In this example, the goal is to generate a list of random 6-character codes. The randomness is handled by...
The SORTBY function allows sorting based on one or more "sort by" arrays, as long long as they have dimensions that are compatible with the data being sorted. In this example, there are 10 values being sorted, the...
In this example, we are sorting a table with 10 rows and 3 columns. In the range J5:J7 (the named range "custom"), the colors "red", "blue", and "green" are listed in the desired sort order. The goal is to sort the...
The SORT function sorts a range using a given index, called sort_index. Normally, this index represents a column in the source data.
However, the SORT function has an optional argument called "by_col" which allows...
At the core, this formula uses the INDEX function to retrieve 10 random names from a named range called "names" which contains 100 names. For example, to retrieve the fifth name from the list, we use INDEX like this...
Related videos
With the introduction of dynamic array formulas, Excel includes 6 brand new functions that directly leverage dynamic array behavior. In this video, we quickly demo all 6.
In this video, we’ll look at how to perform a random sort with the SORTBY function, with help from the RANDARRAY function.
In this video, we’ll look at a basic example of sorting with the SORTBY function. With the SORTBY function, you supply sort order in pairs of inputs. Each pair has an argument for range, and an argument for sort order.
In this video, we’ll look at how to sort by multiple columns with the SORT and SORTBY functions.
In this video, we'll look at how to sort with SORTBY function using a custom list. We use the custom list to specify a custom order.
Related functions
See also
Download 200+ Excel Shortcuts
Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.
