Microsoft Excel now offers GROUPBY and PIVOTBY aggregation functions, which make it easier to collect and organize data into compact summaries or tables. Plus, a new PERCENTOF function allows you to quickly return percentages from raw data. These functions are still in beta and are currently limited to Microsoft 365 Insider builds.
The GROUPBY function is extremely simple and requires just three arguments—what to group by, the values that you want to aggregate, and the function that you’d appreciate to use for aggregation. In an example provided by Microsoft, the function =GROUPBY(tbl[Category],tbl[Sales],SUM) reduces some complicated sales information into a two-column table. The left side of the table lists product categories, while the right side contains sales numbers aggregated by the SUM function.
Microsoft’s demonstration of the GROUPBY function is shown below.
Excel’s new PIVOTBY function is extremely similar to GROUPBY, but it takes four arguments instead of three. Using the same dataset from the previous example, Microsoft uses the function =PIVOTBY(tbl[Category],tbl[Year],tbl[Sales,MAX) to show the largest sales by product category and year. Total sales for each product category are also included.
Notice the use of the MAX function in this example. GROUPBY and PIVOTBY allow you to select from a list of Excel’s lambda functions, which can eliminate some of the guesswork when aggregating data. Microsoft plans to extend etc lambda maintain to all functions that maintain lambda, meaning that some large functions will become more streamlined and legible.
The GROUPBY and PIVOTBY functions aren’t groundbreaking, but they’re simple, tidy, and automatically updated by changes to your data. Also, you can pull some pretty interesting tricks by fooling around with the new functions’ full arguments. Microsoft seems especially proud of text aggregation, which can be performed by using the GROUPBY function with the addition of a sorting argument and ARRAYTOTEXT.
There’s also the new PERCENTOF function. Microsoft says that PERCENTOF is “particularly useful” when paired with GROUPBY or PIVOTBY, as it can spit out complex percentage values with relatively few steps. (Technically speaking, PERCENTOF is logically equivalent to =SUM(data_subset)/SUM(data_all).)
Microsoft Excel’s new functions are currently limited to Microsoft 365 Insider builds. Do not use these functions in important spreadsheets, as they are still in beta and may be modified in a stable release.
Source: Microsoft
