In November 2023, Microsoft began rolling out two powerful new functions that allow you to create a report similar to a pivot table using a formula. The rollout started with the Microsoft Insiders Beta channel and will continue rolling out over the upcoming months.
Both functions start with a simple version that requires as few as three arguments. However, more optional arguments are available in order to control sorting and subtotals.
The GROUPBY function uses this syntax: =groupby(row_fields, values,function, [field_headers],[total_depth],[sort_order],[filter_array])
Simple Use of GROUPBY
In Figure 1, the GROUPBY function returns the array shown in F6:G9. The formula specifies that the function should group by the categories in column A and Sum the sales from column C.
Note that, by default, the function provides one grand total row and no headings. You can override both of these defaults using the total_depth and field_headers arguments as shown later.
People familiar with legacy functions such as SUBTOTAL and AGGREGATE might be surprised that the Excel team abandoned the traditional 11 numbers used to specify that calculation option. For example, you frequently see the =SUBTOTAL(9 to specify a Sum or =SUBTOTAL(3, for a Count. Although those 11 codes for function number were well established in Excel, Microsoft wanted to make GROUPBY and PIVOTBY to be even more flexible than the legacy functions.
Currently, these functions are built in: Sum, Average, PercentOf, Median, Count, CountA, Max, Min, Product, ArrayToText, Concat, StDev.S, StDev.P, Var.S, Var.P, and Mode.Sngl. However, you’re actually allowed to write any possible LAMBDA function and use that LAMBDA function as the function argument. In fact, Microsoft is treating the word SUM as a shortcut for LAMBDA(x,SUM(x)).
Using GROUPBY with Two Fields
In this example, both Category and Product are used as the row fields. Further, the Values include both Sales and Cost. Excel automatically builds a unique list of categories and products, sorts them, and provides totals for each combination with a grand total at the top. Note the addition of an extra argument: 3 is used for the field_headers argument to add headers at the top of the result.
Specifying Subtotals and Sort Order
The next two optional arguments are total_depth and sort_order. Excel provides a single grand total at the bottom of the results as the default. Your choices for this field are: No Total, Grand Total at Bottom, Grand Total at Top, Grand Total and Subtotals, or Grand Total and Subtotals at the top.
In the previous example, the rows were arranged alphabetically. You can override this using the sort_order argument. Let’s say that you want to sort ascending by the sales column. You would use 3 as the sort_order because Sales is the third column. What if you want those sales arranged in descending order? Specify a negative 3. The minus sign is an easy way to specify that the sort order is descending.
Look carefully at Figure 3. I’ve specified that the sort order is based on column 3 descending. The Vegetables group is at the top because it has the largest sales. Within the Vegetables group, the Peppers are at the top because they have the largest sales within the category.
Filtering Out Records
The final argument is the filter_array. You can use this to remove items from the original data. Say that you wanted to remove Herbs from the report. Adding A4:A21<>"Herbs" as the final argument produces the report shown in Figure 4.
Also in Figure 4, I changed the total_depth from 2 to -2. This tells Excel to put the totals and subtotals at the top of each group.
Adding Column Fields with PIVOTBY
If you want to have column fields across the top of your report, switch over to the PIVOTBY function. It uses this syntax:
=PIVOTBY(row_fields, col_fields,
values, function,
[field_headers], [row_total_depth],
[row_sort_order],[col_total_depth],
[col_sort_order],[filter_array])
Figure 5 shows a simple report with regions across the top and categories down the first column. All of the various options shown from GROUPBY also apply to PIVOTBY, with extra complexity for column sort order and column totals.
December 2023
