Let’s put together two dynamic array functions to get even better ‘live’ sorting. So far we’ve look at the two main Dynamic Array features in Excel 365 (and Office 2021/LTSC), Sort()/SortBy() and Filter().
Filter() can be combined with Sort() to make a sub-set of the original list/table but in whatever order you like.
We’ll start with the same example table we’ve used to show off SortBy(). The same results table (left) is displayed in two different orders (side-by-side at right).
Now let’s do the same thing but with a filtered or partial table to see only those who passed the test (i.e. scores over 60).
The Filter() formula is:
=FILTER(ExamScores[[Name]:[Score]],ExamScores[Score]>60)
The source is the first two columns of the table ExamScores.
Filtered by ExamScores[Score] more than 60.
In this case there’s no need for the third ‘No Result’ parameter.
That’s OK but the results are in whatever order the original table is in.
Side Note: Filter() will update whenever the original source data changes. That means changing the order of the original table will automatically change the order of a Filter()’d list. Worth keeping in mind as another good reason to combine Filter() with Sort() so your separate list doesn’t unexpectedly resort.
Adding Sort() to Filter()
Now we have a separate Filtered list, it’s simple to sort it to the order you want. Just wrap a Sort() function around the Filter() formula.
=SORT(FILTER(ExamScores[[Name]:[Score]],ExamScores[Score]>60),1,1)
First parameter is the entire Filter() formula from above.
Then choose which column from the filtered list to use. Note this works by number, not name.
Which order to sort in. 1 – Ascending or -1 – Descending.
The last parameter isn’t needed here. You can choose whether to sort by row (TRUE – the default) or column (FALSE).
Here’s the combined Sort() and Filter() list next to it’s unfiltered sister.
Sort() is the function to use here, not SortBy(). Sort() uses column number references and is intended for use with temporary arrays like the one created by Filter(). SortBy() is for use directly against tables/lists with references by name.
Two versions of the same filtered list
Finally, here’s one reason dynamic arrays are such a cool addition to Excel.
On the right are two Passing Grade lists – in both Score and Name order. Those lists will update automatically when there are changes to the original results table (left). Here you can see there’s been some additions to the exam results which are at once reflected in the sorted lists at right.
The Passed (Score) formula is almost the same as the name order formula.
All we did was change the Sort column to ‘2’ – the second or Score column.
And change the sort order to -1 Descending.
=SORT(FILTER(ExamScores[[Name]:[Score]],ExamScores[Score]>60),2,-1)
Filter then Sort
Theoretically you could Filter() a list arranged with Sortby() e.g. =FILTER( SortBy(…) ) but that would be a horrible waste of computer resources. Far more efficient to reduce the array size first with Filter(), then sort what remains.
Better live sorting in Excel 365
Easy and better lists with Excel 365’s Filter()
SUMIFS Excel SUM with filters galore