Exceljet

Quick, clean, and to the point

Sort by two columns

Excel formula: Sort by two columns
Generic formula 
=SORTBY(data,col1,order,col2,order)
Summary 

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.

Explanation 

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)

Example - sort by two columns in ascending order

Dynamic Array Formulas are available in Office 365 only.
Author 
Dave Bruns
Related formulas 
Excel formula: Sort by one column
Sort by one column
The SORT function requires very little configuration. In the example shown, we want to sort data in B5:D14 by the third column, Group. For array, we provide entire range, B5:D14. For sort_index, we provide 3: = SORT ( B5:D14 , 3 ) With this formula...
Related functions 
Excel SORTBY function
Excel SORTBY Function
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.
See also 
Dynamic array formulas in Excel
Alternatives to Dynamic Array Functions

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

500 Formulas | 101 Functions

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.