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 
<span class="TextRun SCXW185