New aggregation functions: GROUPBY and PIVOTBY
Published Nov 14 2023 01:11 PM 86.1K Views
Microsoft

November 14th 2023 Status
These functions are currently rolling out to users enrolled in the beta channel for Windows Excel and Mac Excel 

 

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. 

 

Note: These are preview functions, their signature and results may change substantially before final release based on user feedback. Until final, we do not recommend using them in important workbooks.


GROUPBY

To do a simple GROUPBY, you need just 3 arguments: i) What to group by, ii) the values to aggregate and iii) the function you'd like to use for the aggregation. 

 

Aggregate Sales by CategoryAggregate Sales by Category

 

PIVOTBY

PIVOTBY is the same as GROUPBY but it let's you group by column too. 

 

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 extreme 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 our all our functions that accept lambdas, greatly improving formula readability for simple scenarios. Consider 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 of their outputs. For instance, you can control how they sort, whether your data has headers, and 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])

Detailed information on these arguments can be found on the function help pages. (link, link)

 

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 I aggregate text values. Here I 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 an additional function: PERCENTOF. It's a simple function that SUM's its first argument and divides 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

More information on the PERCENTOF function can be found on the help page. (link)

 

Don’t have it yet? It’s probably us, not you.

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. You can also submit new ideas or vote for other ideas via Microsoft Feedback.

 

Want to know more about Excel? See What's new in Excel and subscribe to our Excel Blog to get the latest updates. Stay connected with us and other Excel fans around the world – join our Excel Community and follow us on Twitter.

 

Joe McDaid (@jjmcdaid)
Program Manager, Excel

 

 

66 Comments
Bronze Contributor

Great news! Excited to get two functions.

Copper Contributor

How long it would take to be available in Office 365?

Btw, great innovation!

Bronze Contributor

Does anybody know tentative release date of these two functions to end users (Not member of beta channel or insider).

Copper Contributor

Small spell check: in this phrase: "...but it let's you group by column too.", "let's" should be spelled "lets".

 

But these are great functions.

 

Silver Contributor

I've been putting PIVOTBY and GROUPBY through the paces with a very large data set (1.2 million elements) to see what they can handle.  It's been quite evident that if I can fulfill the filter_array argument it will improve performance of the functions tremendously.

 

My suggestion for both functions concerns the placement of the filter_array argument in each.  I think it would be helpful to place it after 'function' to read like this:

 

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

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

Copper Contributor

Happy to use this feature. I am not yet a member of the Insider Program, is it known when this update will be implemented so that regular users can use this feature?

Copper Contributor

This is going to be great!  Looking forward to employing this in workbooks used by our team once it reaches GA.

Copper Contributor

Is there a target date for these features to be rolled out to current user using Oficce365?  Microsoft has informed me it is only in beta and stand alone editions of Office.

@ronblaisdell , for each new functionality the situation is the same. With rare exceptions. First we see it on Beta for Windows Desktop, at least 10% of Beta users have it right after announcement. Or few hours before since announcement takes time to publish. Next, other Beta users are added wave by wave. Finally 100% of Beta is covered, if someone doesn't have here most probably that's technical problem. Unfortunately Microsoft doesn't share, at least directly, which % of Beta is covered and when we have 100%. Full coverage could take weeks, could take months. Depends on stats, feedbacks, loading and availability of Excel teams, have no idea on what else.

Copper Contributor

Excel crashes when you try to use the PIVOTBY or GROUPBY functions on a table Column in which the values are derived from a Lambda Function.

I have an excel table containing a Column (Date). The values in Date column are derived from 3 other columns in the table using the following LAMBDA function 

=LAMBDA(d,m,y, LET(pre_date,INDIRECT(ADDRESS(ROW()-1,COLUMN(),4)), pre_day,IF(ISNUMBER(pre_date),DAY(pre_date),0), pre_month,IF(ISNUMBER(pre_date),MONTH(pre_date),0), pre_year,IF(ISNUMBER(pre_date),YEAR(pre_date),0), RD,IF(d=0,pre_day,d), RM,IF(m<>0,m,IF(RD>=pre_day,pre_month,IF(pre_month=12,1,pre_month+1))), RY,IF(y<>0,CHOOSE(LEN(y),VALUE(200&y),VALUE(20&y),VALUE(2&y),y), IF(RM<pre_month,pre_year+1,pre_year)),DATE(RY,RM,RD) ))

If I use the Date Column in the PIVOTBY or GROUPBY Functions, Excel crashes and restarts.

@Austine Ezemelue , nice finding. Definitely the bug.

Copper Contributor

This is awesome, and we know about this, what could possibly still be the hold up for release

Copper Contributor

A new parameter has been added to GROUPBY & PIVOTBY

Microsoft

Well spotted, there will be another change coming out to GROUPBY's signature in the next two or so weeks. We are in the process of finalizing the signatures based on user feedback. 

 

 

Copper Contributor

Hi,

 

This feature looks great and I've been anticipating it for some time now. When will it roll out!?

Like many business users, I my corporate policy prevents me from using the Beta access...

 

I have the same issue with all the exciting new excel features like checkboxes and python.

 

Your biggest fan

This is Stan

@StanSikorski , afraid nobody knows when it'll be in production. Depends on bugs found and people feedback on preview stage.

Co-Authors
Version history
Last update:
‎Nov 14 2023 04:46 PM
Updated by: