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 podcasts@cpaaustralia.com.au