New Functions Provide Superior Options!
Origins of Excel's SORT and FILTER Functions
Dynamic arrays are one of the most significant developments in Excel to appear in the past decade. Before dynamic arrays, we had to enter (or copy) a formula into each cell where we needed a calculation. Some refer to this as the one formula, one value paradigm. With dynamic arrays, we can enter one formula that takes action on many cells at the same time, meaning many of our Excel processes just got significantly easier!
Using Excel's SORT and FILTER Functions
=SORT(Array, Sort_index, Sort_order, By_col)
The array is the range of data you want to sort. The sort_index is the column by which you want to sort the array. By default, the sort_index is the first column, but you can enter a numerical reference to any other column. The sort_order defaults to a value of “1,” representing ascending; you can change this to “-1” to sort in descending order. Finally, the by_col reference defaults to False to facilitate sorting by row. If you need to sort by column instead, change the value to True.
To illustrate working with the SORT function, consider the example presented in Figure 1. The SORT function used sorts the data in cells B2 through C51 on the second column (population), and it sorts the data in descending order. Notice that the formula places the results into E2 through F51. The procedure does not alter the source data range (B2 through C51) at all.
Working With the Filter Function
=FILTER(Array, Include, if_empty)
Like SORT, when using FILTER, the array argument is the range you wish to screen to meet specific criteria. The include argument allows you to specify your screening criteria. Finally, you can use if_empty to indicate what gets returned if no records match the specified criteria.
Figure 2 illustrates working with the FILTER function in Excel. In this example, the formula filters the data in cells B2 through C51 to just those states with populations exceeding 7,000,000. Like the SORT function, the FILTER function does not disturb or otherwise rearrange the source data.