Sort by two columns
=SORTBY(data,col1,order,col2,order)
To sort by two columns, you can use the SORTBY function. In the example shown, data is sorted first by the Group column in ascending order, then by the Score column in descending order. The formula in F5 is:
=SORTBY(B5:D14,D5:D14,1,C5:C14,-1)
The result is data sorted by group, then by score, with highest scores appearing first.
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 group A-Z:
=SORTBY(B5:D14,D5:D14,1) // sort by group only
To extend the formula to sort next by score, in descending order, we need to add:
- by_array2 = C5:C14
- sort_order2 = -1
With these arguments added, the complete formula is:
=SORTBY(B5:D14,D5:D14,1,C5:C14,-1)
Ascending vs. Descending
Data is sorted in ascending order (A-Z) by default. This behavior is controlled by the sort_order arguments. Sort_order can be set to 1 (ascending) or -1 (descending). The formula below is the same as above except that it sorts scores in ascending order:
=SORTBY(B5:D14,D5:D14,1,C5:C14,1)
Download 200+ Excel Shortcuts
Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.
