Thursday, July 25, 2024

Microsoft Excel Introduces Powerful GROUPBY and PIVOTBY Functions

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).


Please enter your comment!
Please enter your name here

Saas listing

Share your experience and write review on the Apps you have used and win gifts weekly


0 out of 5
Email Marketing

Pabbly Connect – API Based Automation

0 out of 5

FastComet – Web Hosting

0 out of 5

GoZen Growth – Email Marketing Software

0 out of 5

Related Articles

Affiliate Marketing Model Fuels FASTer Way’s Growth

FASTer Way to Fat Loss has good news for affiliate marketers. It has hit a major milestone lately. It announced...
Read more
Maintaining a strong online presence is important in this digital era. Businesses should aim for it to thrive. LinkDaddy has...
The search engine optimization (SEO) sector is ever-evolving and professionals need to stay updated with current trends to maintain a...