Dynamic sorting and filtering can transform how you work with data in Excel, making complex tasks simpler and more efficient, says Traci Williams
Using Excel’s ‘Sort’ and ‘Filter’ functions is fairly second nature to us now, as they tend to be used daily by most people who use spreadsheets.
But did you know that we can now use these features as FORMULAS to extract data from the source? They’re pretty ACE!
They are perfect if you want your raw data to remain untouched, but you want to build external summaries from it.
Sort Formula
This formula will dynamically extract a list of values sorted in a specified order (ascending, descending, etc.) using a formula, and NOT the function, from the raw data. The dynamic nature of this formula means it will continually update (and expand or contract) as the raw data changes, without any manual intervention.
In this example, we have a simple list of names of salespeople:
We can use the ‘Sort’ formula to extract this list and sort it into alphabetical order, as follows:
Select a cell where you want the list to start from (e.g., C3).
From the ‘Formulas’ ribbon (1), select ‘Insert Function’ (2), then type ‘Sort’ (3) and click ‘Go’ (4):
A list of potential formulas will appear at the bottom. Select the one called ‘Sort’ (5), then click ‘OK’ (6).
The wizard will appear to guide you as to what to enter:
The boxes should be completed as follows:
Array
Enter the range to be used (e.g., A3:A8).
The next three boxes are optional, as follows:
Sort_Index
This is a number indicating the row or column to sort by (this is usually obvious from the selected range).
Sort_Order
This is the basis of sort (1 = ascending (default), -1 = descending).
By_Col
This is the sort direction (FALSE = by row (default), TRUE = by column).
Then click ‘OK’ and the formula (and result) will appear like this:
The formula ONLY includes the array (=SORT(A3:A8)), as none of the other optional fields were relevant in this instance, and the list now appears in alphabetical order (ascending).
Of course, if I wanted the list to appear in descending order, I could simply amend the formula like this:
This formula only appears in the first cell (C3) and is ‘SPILLED’ into the rows below using as many rows as there is data. This is the DYNAMIC nature of this formula.
The benefit of this is that if the list of names in column A expands or reduces (inserting or deleting rows), so too will the SPILLED range in column C… without the need for any manual intervention.
This also means that this formula does not need to be copied down into multiple rows, and Excel is not having to perform multiple calculations, making the processing quicker and keeping the file size low.
SORTBY Formula
With this formula it’s possible to define the criteria to sort by (not just base it on the source range).
In this instance, we might want to SORT the salespeople according to the highest sales:
The syntax of this formula would be:
Array
Enter the range to be used (e.g., A3:A8).
By_Array1
This is the range by which to sort the data (e.g., B3:B8 (Qty)).
The next box is optional, as it will default to ascending order if non-populated.
Sort_Order1
This is the desired sort order (1= ascending order / -1 = descending order).
The result will appear like this:
You can also add multiple criteria if required.
Filter Formula
Just like Sort, the Filter formula also works dynamically and will extract data based on defined filter criteria.
In this example, we have lots of data and we might want to extract all of the data for Customer D:
Of course, we could apply a filter directly on the raw data, but this would temporarily hide all other rows, preventing us from seeing those.
This is where we can use the Filter formula to extract the data for that customer:
Select a cell where you want the data to start from (e.g., H3).
From the ‘Formulas’ ribbon, select ‘Insert Function,’ then type ‘Filter’ and click ‘Go.’
A list of potential formulas will appear at the bottom. Select the one called ‘Filter,’ then click ‘OK’:
The wizard will appear to guide you as to what to enter:
The boxes should be completed as follows:
Array
Enter the range to be used (e.g., A3:F201).
Include
Enter the criteria to be filtered by (e.g., B3:B201=”Customer D”).
The next box is optional.
If_empty
This is the opportunity to tell the formula what to return if it is unable to find a match for the criteria. This will return an error if not defined.
Click OK.
The result will appear like this:
Notice that I have used the Array: A3:F201, meaning that this will return ALL of the columns. I could have made the Array: A3:C201, and this would have just returned the first three columns.
The ‘None Found’ part of the formula is what would be returned if there was no data matching that criteria.
As with the Sort formula, you can see that the formula is only entered into the first cell (H3) and then SPILLS into the remaining cells as appropriate, returning all the columns of data, ONLY for those rows that include Customer D.
We can amend the customer name in that formula to return a different subset of data easily and quickly.
Of course, if any of the raw data was to update, then the result of the Filter formula would also automatically update too.
We may prefer to filter the list based on a product instead, and to do this, we would simply amend the contents of the ‘Include’ part of the formula, as follows:
It may also be a requirement to filter by BOTH customer AND product, and this would require a slight modification to the ‘Include’ part of the formula, as follows:
Notice the part of the formula highlighted with the red box contains the criteria for filtering by BOTH customer and product, both in brackets, and joined with an asterisk (*). This means that BOTH conditions must be met.
You can include multiple criteria in this formula.
Top Tip:
Using * applies AND logic (ALL conditions must be met).
Using + applies OR logic (at least one condition must be met).
Multiple conditions can be included inside the ‘Include’ argument.
Conclusion
These formulas provide powerful ways to organise and analyse data dynamically, as unlike manual sorting and filtering, these functions automatically update when the source data changes, saving time and improving efficiency.
With practice, dynamic sorting and filtering can transform how you work with data in Excel, making complex tasks simpler and more efficient.
