Excel’s GROUPBY and PIVOTBY explained

Excel’s new functions, GROUPBY and PIVOTBY, offer a smarter way to build reports.  

Instead of relying on traditional pivot tables, these new tools allow you to summarise datasets using dynamic, formula-based solutions that update automatically when the underlying data changes. 

In this episode: 

• Understand the key differences between GROUPBY and PIVOTBY 

• See how these tools can save time and reduce manual errors 

• Explore practical applications for accountants and analysts 

• Discover how to modernise reporting with flexible, auto-updating formulas 

For fast reference use the episode timestamps.  

00:17 - Two new functions that can change report creation 
00:35 - PivotTables can create summary report based on a data set 
00:52 - The limitation of PivotTables – Refreshing the data 
01:06 - New functions GROUPBY and PIVOTBY 
01:12 - Use a function to create a PivotTable report – no Refresh needed 
01:36 - GROUPBY reports – row-based reports 
01:41 - PIVOTBY reports - row and column-based reports 
01:59 - Best practice data source – a formatted table 
02:14 - GROUPBY arguments listed and explained 
02:46 - Multiple columns and subtotals are possible but require other functions 
03:09 - Two functions that combine columns – HSTACK and CHOOSECOLS 
04:03 - Structured references include table names and column names 
04:18 - Formatted tables automatically expand when new data is added 
04:35 - Formatting subtotal rows differently using conditional formats 
05:05 - Filtering and sorting is possible in the GROUPBY report 
05:25 - Companion video has an example of a Slicer as filter 
05:43 - Controlling sorting 
06:18 - New function to add percentages to reports called PERCENT OF 
06:42 - Companion video has a hack to easily combine SUM and PERCENT OF 
07:02 - Multiple calculations add headings to the report – they can be removed 
07:09 - Remove heading with the DROP function 
07:25 - PIVOTBY function description and explanation 
08:09 - Dates can be problematic as you want to group them by month 
08:31 - Companion video shows the TEXT function solving the problem 
08:53 - Allowing the user to modify the report 
09:15 - INDIRECT function solution 
10:05 - Explanation of the INDIRECT function 
10:38 - Adding extra calculations to the report 
11:07 - The LET function allows you to capture and modify the report 

Whether optimising month-end processes or improving team workflows, GROUPBY and PIVOTBY help turn complex datasets into clear, actionable reports. 

Listen now to stay up to date with these Excel capabilities. 

Host: Neale Blackwood CPA. He has more than 20 years of experience as a Microsoft Excel educator and is the author of more than 200 INTHEBLACK articles as well as a book, Advanced Excel Reporting for Management Accountants. 

You can find a CPA at our custom portal on the CPA Australia website.  

Would you like to listen to more Excel Tips episodes? Head to CPA Australia’s YouTube channel.  

CPA Australia publishes four podcasts, providing commentary and thought leadership across business, finance, and accounting:  

With Interest 

INTHEBLACK  

INTHEBLACK Out Loud 

Excel Tips 

Search for them in your podcast platform.  

Email the podcast team at [email protected]