In the world of spreadsheets, Microsoft Excel has just pulled off a magical act with the introduction of three powerful functions—GROUPBY, PIVOTBY, and PERCENTOF. These functions bring a new level of simplicity and organization to data handling. These makes easy to create compact summaries and tables. However, be warned, these tricks are still in beta and exclusive to Microsoft 365 Insider builds.

Let us start with GROUPBY, the wizard of simplicity. With just three arguments, you can group data by a chosen category, aggregate values and specify the function you want to use. For example, the formula =GROUPBY(tbl[Category], tbl[Sales], SUM) transforms complex sales data into a neat two-column table. On the left, you have product categories, and on the right, sales numbers are neatly aggregated using the SUM function.

Now, meet PIVOTBY, the close cousin of GROUPBY. It takes four arguments but works in a similar fashion. Using =PIVOTBY(tbl[Category], tbl[Year], tbl[Sales, MAX), you can showcase the highest sales by product category and year. Total sales for each product category are also there, adding another layer of insight.

What is cool is the use of functions like MAX. These functions come from Excel’s lambda functions. Microsoft plans to extend this lambda support to all functions that support it. It promises a more streamlined and readable experience.

While GROUPBY and PIVOTBY might not be groundbreaking, they are your tidy data companions. They automatically update as your data changes, keeping everything neat and orderly. Microsoft is particularly excited about text aggregation, a trick you can pull off with GROUPBY and a sorting argument plus ARRAYTOTEXT.

Now, enter the stage, PERCENTOF. This function is the sidekick that plays exceptionally well with GROUPBY or PIVOTBY. It effortlessly spits out complex percentage values in just a few steps. If we get technical, PERCENTOF is like saying =SUM(data_subset)/SUM(data_all).