New aggregation functions in Excel: GROUPBY and PIVOTBY
Published Aug 25 2024 06:04 PM 2,839 Views
Microsoft

(Originally published on December 4, 2023 by Joe McDaid)

 

Hey, Microsoft 365 Insiders! I’m Joe McDaid, a Product Manager on the Excel team, and  I’m excited to announce the availability of two new functions in Excel for Windows and Excel for Mac: GROUPBY and PIVOTBY.

 

New aggregation functions in Excel: GROUPBY and PIVOTBY

 

These new 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. 

 

NOTE: These are preview functions. Their signature and results may change substantially before being broadly released, based on your feedback. So, we do not recommend using these functions in important workbooks until they are generally available.

 

How it works

GROUPBY

To do a simple GROUPBY, you need just 3 arguments:

  • What to group by
  • The values to aggregate
  • The function you want to use for the aggregation

Aggregate Sales by CategoryAggregate Sales by Category

 

PIVOTBY

PIVOTBY works just like GROUPBY, but also lets you group by column.  

Largest Sale by Category and YearLargest Sale by Category and Year

 

Functions as arguments

In the examples above, we pass the SUM and MAX functions to GROUPBY and PIVOTBY respectively. These are lambda functions, “eta reduced lambda” functions to be precise. And while they have a complex name, they are extremely easy and intuitive to use – you don’t even need to know anything about lambda functions - you can just pick them from a list!

Function Autocomplete ListFunction Autocomplete List

 

We also added “eta lambda” support to all our functions that accept lambdas, greatly improving formula readability for simple scenarios. Check out the BYCOL example below. Previously you'd have to have write: =BYCOL(C3:F6,LAMBDA(x,SUM(x)).

SUM by ColumnSUM by Column

 

Function Signature

GROUPBY and PIVOTBY have additional arguments that give you control over their outputs. For instance, you can:

  • Control how they sort
  • Whether your data has headers
  • Whether you want to filter out some values 

You can see their full 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])

 

 

 

 

For more information about these arguments, check the function help page for GROUPBY and PIVOTBY

 

Why use GROUPBY and PIVOTBY?

As functions, GROUPBY and PIVOTBY are lightweight, fully descriptive, automatically update, and can depend on the result of other calculations. They also aggregate using lambda functions which unlock new aggregation capabilities.

 

Consider the following example where you aggregate text values. Here you make use of a lambda to sort the unique items before joining them together using ARRAYTOTEXT. This type of aggregation was previously impossible in Excel.  

Items sorted alphabetically by CategoryItems sorted alphabetically by Category

 

Bonus function

We added one more function: PERCENTOF. It's a simple function that calculates the sum of its first argument, and then divides it by the sum of its second argument. While you can use it on its own, it's particularly useful with GROUPBY and PIVOTBY as it allows you to return percentages. 

 

 

 

 

PERCENTOF(data_subset,data_all)

 

 

 

 

Percentage Sales by CategoryPercentage Sales by Category

For more information, check out the PERCENTOF function help page.

 

Availability

These functions are currently available to Beta Channel users running:

  • Windows: Version 2312 (Build 17116.10000) or later
  • Mac: 16.80 (Build 23111917) or later

 

Features are released over some time to ensure things are working smoothly. We highlight features that you may not have because they’re slowly releasing to larger numbers of Insiders. Sometimes we remove elements to further improve them based on your feedback. Though this is rare, we also reserve the option to pull a feature entirely out of the product, even if you, as an Insider, have had the opportunity to try it.

 

Feedback

If you have any feedback or suggestions, you can submit them by clicking Help > Feedback in Excel. 

 

You can also submit new ideas or vote for other ideas via Microsoft Feedback

 


Learn about the Microsoft 365 Insider program and sign up for the Microsoft 365 Insider newsletter to get the latest information about Insider features in your inbox once a month!

Co-Authors
Version history
Last update:
‎Aug 25 2024 11:04 AM
Updated by: