Excel's new GROUPBY and PIVOTBY functions allow for aggregations previously impossible on the platform

Excel debuts 2 new functions. GROUPBY, and PIVOTBY, which allow users to easily aggregate data using a single formula. Microsoft says these new formulas are not only going to save up a lot of time, but they are quite easy to learn, as well, according to their latest blog post.

I’m excited to announce Excel’s new GROUPBY and PIVOTBY functions. These functions allow you to perform data aggregations using a single formula. And while these functions are extremely powerful, they are also simple to get started with. In fact, you can do a data aggregation with just 3 arguments, the same number as a simple XLOOKUP. 

Joe McDaid, Excel

Excel recently got some interesting updates: the data-managing app now has an improved Web connector that will ease the work for many users, and of course, the most expected addition is happening this month. Copilot is also coming to Excel.

The new GROUPBY and PIVOTBY functions are currently rolling out to users enrolled in the beta channel for Excel. Windows Excel, as well as Mac Excel, are eligible to get it.

How to use the new Excel GROUPBY and PIVOTBY functions?

According to Microsoft, using the GROUPBY and PIVOTBY functions is easy. You’ll need to define 3 arguments:

1. What to group by (or pivot by, in the latter case).
2. The aggregate values.
3. The function you’d like to use for the aggregation.

The 2 functions are the same, but PIVOTBY will aggregate data by columns too.

Microsoft also released additional arguments for these 2 new functions, so users can have greater control over their outputs. You can find these additional arguments in the functions’ signatures below.

GROUPBY(row_fields,values,function,[field_headers],[total_depth],[sort_order],[filter_array])

PIVOTBY(row_fields,col_fields,values,function,[field_headers],[row_total_depth][row_sort_order],[col_total_depth],
[col_sort_order],[filter_array])

This is not all, though. If you want to have your outputs in percentages, Excel has a new function that goes together with the GROUPBY and PIVOTBY: PERCENTOF.

Here is its signature:

PERCENTOF(data_subset,data_all)

There are multiple benefits to using these 2 functions, as Microsoft describes:

• They are lightweight, fully descriptive, and will automatically update.
• They can depend on the result of other calculations.
• GROUPBY and PIVOTBY also aggregate using lambda functions, which unlocks other aggregation capabilities. For example, they can aggregate text values, using the ARRAYTOTEXT function. Excel says this type of aggregation was previously impossible on the platform.

As we previously reported, these two new functions are currently rolling out in the beta channel of Excel. If you’re enlisted in the channel and don’t see the new functions, you should give it a try later.