If you're navigating the expansive realm of Microsoft 365, you're in for a treat! The dynamic array functions SORT and SORTBY are here to revolutionize your data sorting experience. In this article, we'll delve into several examples showcasing the incredible capabilities of these functions, empowering you to perform tasks with newfound efficiency.
The Dynamics of SORT and SORTBY
Traditionally, data sorting in Excel involves the familiar DATA SORT or the A to Z / Z to A buttons. However, with the advent of dynamic array functions, specifically SORT and SORTBY, you can now elevate your sorting game using functions. What makes these functions even more remarkable is their ability to seamlessly update when new data is added, provided your source data is within an Excel table.
The Original Dataset
Let's explore the functionality of SORT and SORTBY in the context of a sales transactions dataset. In the "Original" worksheet, our data range A5:D67 captures key details of 63 sales transactions:
- Salesperson name
- Product Sold
- Units Sold
- Revenue from Transaction
The SORT Function Unveiled
The magic begins with the SORT function. Consider the formula =SORT(A6:D67,3,-1)—this dynamic array formula sorts the data in descending order based on the 3rd column (Units). The beauty lies in its ability to automatically "fill down" all the data in the correct order. For instance, the first row (F4:I4) reveals details of the transaction with the highest units, while the last row (F65:I65) corresponds to the transaction with the fewest units.
Syntax of SORT Function:
=SORT(array, sort_index, [sort_order], [by_col])
- Array: The data range to be sorted (A6:D67).
- Sort_index: Indicates the sort is based on the specified column of data (3 in this case).
- Sort_Order: -1 indicates sorting in descending order. Omitting or using +1 sorts in ascending order.
- By_col: Omitting or entering FALSE creates a sort by row. TRUE creates a sort across columns.
The SORTBY Function Unleashed
Now, let's take it up a notch with the SORTBY function. In cell K3, enter the formula =SORTBY(A6:D67,C6:C67,-1,A6:A67,1). This formula orchestrates a descending sort by units and further refines the order by sorting salesperson names in ascending order. Rows with the same unit count are then sorted alphabetically by the salesperson's name.
Syntax of SORTBY Function:
=SORTBY(array, by_array1, sort_order1, [by_array2, sort_order2, ...])
- Array: The data range to be sorted (A6:D67).
- By_array1: The first array to sort by (C6:C67, representing Units).
- Sort_order1: -1 indicates descending sort for the first array.
- By_array2, Sort_order2, ...: Additional arrays and sort orders for refining the sort.
As seen in Figure 3, the first Sort_Order argument of -1 results in a descending sort of units, while the second Sort_Order argument of +1 resolves ties by performing an A-Z sort based on the salesperson's name.
Unraveling the Potential
SORT and SORTBY functions not only streamline your sorting processes but also bring a dynamic and automated dimension to your data management. The ability to handle ties and perform multi-level sorting enhances the precision and depth of your analyses.
What's Next?
In the next installment, we will unravel the capabilities of the FILTER function—an incredible tool that empowers you to filter an Excel database using a formula, bypassing the traditional Excel "filter" icon. Stay tuned as we continue our exploration of Microsoft 365's dynamic array functions, unlocking new possibilities for efficient data manipulation.
Note: The content shared in this article is for informational purposes and does not constitute tax advice. For advice tailored to your specific situation, it is recommended to consult a tax advisor.