In this worksheet, we have a list of names, scores, and groups. Currently the data is not sorted. Our goal is to sort this data by group, then by score in descending order. I’ll start off by placing the cursor in cell F5, then typing an equals sign (=) and the first few letters of “sortby”. Once we have a match, I’ll press the TAB key to complete. The first argument is called array. This is the data we want to sort. In this case, that’s the range B5:D14. Next, we need to enter a pair of arguments. 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. Since we want to sort by Group first, the range is D5:D14. For sort_order1, we use 1, because we want to sort in ascending order. Now, if I stop there and enter the formula, notice we now have the data sorted by group. Inside each group, the sort order is unchanged, and reflects the order that the names appear in the source data. To finish the problem, and sort the data by group, and then by score, I need to add another pair of arguments. For by_array2, I’ll use the scores in C5:C14. For sort_order2, I’ll use -1, since we want to sort in descending order. Now when I enter the formula, the data is sorted first by group, and then by score, with highest scores appearing first. Depending on your needs, you can add more arguments to the SORTBY function to extend the sort operation to other columns. Finally, note that sort_order defaults to 1, which specifies ascending order. This means I could remove the first sort_order argument. Personally, however, I like to provide values for optional arguments as a reminder to myself of the behavior I’m expecting.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
