Sorting with formulas is one of those traditionally hard problems in Excel that new dynamic array formulas have made much easier. 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 in descending order by score. There are two new functions in Excel for sorting: SORT and SORTBY. Either function will work in this case, but we’ll use the SORT function. We’ll start off by placing the cursor in cell F5, then I’ll type the first few characters of the word “sort”. Once we have a match, I’ll press the TAB key. 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 provide the sort index argument. Sort index is just a number that corresponds to the columns in the data. We want to sort by the score, which is the second column, so I need to use 2. Finally, we want to sort this data in descending order by score, so we need to provide the optional argument called “sort order”. You can see the options are 1 for ascending, and -1 for descending. When I enter -1 and hit return, we get our sorted data. Notice these results are dynamic. If I temporarily increase a score in the source data, the results update automatically. I’ll undo that change. Lastly, a quick note on sort order. As you saw earlier, the value for ascending sort is 1. If I make that change, you can see the data sorted in ascending order. However, note the sort order argument is optional. If I remove the 1, the sort order is unchanged. This means the SORT function will sort in ascending order by default.
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.
