Microsoft Excel has recently introduced two new aggregation functions, GROUPBY and PIVOTBY, which make it easier to collect and organize data into compact summaries or tables. Along with these functions, a new PERCENTOF function has been added to quickly return percentages from raw data. While these functions are currently in beta and limited to Microsoft 365 Insider builds, they offer great potential for simplifying data analysis and reporting.
The GROUPBY Function
The GROUPBY function in Microsoft Excel is incredibly simple and requires just three arguments. These arguments include what to group by, the values that need to be aggregated, and the function to be used for aggregation. For example, using the function =GROUPBY(tbl[Category], tbl[Sales], SUM) reduces complex sales information into a two-column table. The left side of the table lists product categories, while the right side contains the aggregated sales numbers using the SUM function. This makes it easier to analyze sales data by category.
Microsoft Excel provides a demonstration of the GROUPBY function, showing how it can be used to split item sales into product categories with corresponding sales figures. The GROUPBY function simplifies the process of organizing and summarizing data, making it an invaluable tool for data analysis and reporting.
The PIVOTBY Function
The PIVOTBY function in Microsoft Excel is similar to the GROUPBY function, but it takes four arguments instead of three. Using the same dataset as the previous example, the function =PIVOTBY(tbl[Category], tbl[Year], tbl[Sales], MAX) can be used to show the largest sales by product category and year. Total sales for each product category are also included. The PIVOTBY function allows for more advanced data analysis and reporting, providing a comprehensive view of sales data by category and year.
Microsoft plans to expand lambda support to all functions that currently support lambda, making data aggregation even more streamlined and legible. This will further enhance the capabilities of both the GROUPBY and PIVOTBY functions, allowing for more efficient data analysis and reporting.
The PERCENTOF Function
In addition to the GROUPBY and PIVOTBY functions, Microsoft Excel has introduced the PERCENTOF function. This function is particularly useful when paired with GROUPBY or PIVOTBY, as it can generate complex percentage values with relatively few steps. The PERCENTOF function is logically equivalent to =SUM(data_subset)/SUM(data_all). It provides a convenient way to calculate percentages from raw data, further enhancing the data analysis capabilities of Microsoft Excel.
While these new functions offer great potential for data analysis and reporting, it is important to note that they are currently in beta and limited to Microsoft 365 Insider builds. It is advised not to use these functions in important spreadsheets, as they may be modified in a stable release. However, for those using Microsoft 365 Insider builds, these new functions can greatly simplify data organization and analysis, making it easier to derive valuable insights from complex datasets.
Microsoft Excel’s new GROUPBY, PIVOTBY, and PERCENTOF functions are powerful tools that streamline data analysis and reporting. By leveraging these functions, users can easily collect and organize data, generate compact summaries or tables, and calculate percentages with ease. While still in beta, these functions demonstrate Microsoft’s commitment to continuously improving Excel’s capabilities and making data analysis more accessible to users.
