Blog Post

Excel Blog
3 MIN READ

New aggregation functions: GROUPBY and PIVOTBY

JoeMcDaid's avatar
JoeMcDaid
Icon for Microsoft rankMicrosoft
Nov 14, 2023
September 25th 2024 Status These functions are now generally available to users in the Current Channel.     I’m excited to announce Excel's new GROUPBY and PIVOTBY functions. These functions all...
Updated Sep 25, 2024
Version 9.0
blog
excel
Austine Ezemelue's avatar
Austine Ezemelue
Copper Contributor
Feb 21, 2024

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.