GROUPBY and PIVOTBY Functions in Excel – Pivot Table Killers

About the Course:

Discover how Microsoft's new dynamic array functions, GROUPBY and PIVOTBY, can transform your Excel analysis.

These functions allow you to generate Pivot Table–style summaries directly using formulas, and unlike traditional Pivot Tables, they update instantly when the source data changes. No manual refresh required.

In this session, we will:

• Deep dive into GROUPBY syntax and required vs optional arguments
• Build multi-level grouped summaries
• Control totals and subtotals using total depth
• Sort results dynamically (ascending, descending, multi-column sorting)
• Apply filter arrays inside the function
• Use HSTACK to control grouping hierarchy
• Display text results in the values area using ARRAYTOTEXT
• Create unique text lists using LAMBDA + UNIQUE
• Generate unique counts
• Recreate full cross-tab reports using PIVOTBY
• Compare performance and flexibility vs traditional Pivot Tables

This session will also cover the current limitations of these functions, the status of Insider/Beta availability, and scenarios where Pivot Tables remain the preferred choice.

Course Objectives:

By the end of this webinar, participants will be able to:

• Write GROUPBY formulas accurately with structured references or ranges.
• Distinguish between mandatory and optional inputs in GROUPBY.
• Manage header behaviour, including showing, hiding, generating, or treating headers as data.
• Use HSTACK to build grouping hierarchies.
• Control grand totals and subtotals with total depth.
• Apply dynamic sorting with positive and negative column indexes.
• Perform multi-column sorting with array syntax.
• Use filter arrays to dynamically filter results.
• Display text results within grouped outputs.
• Build unique text lists with LAMBDA, UNIQUE, and ARRAYTOTEXT.
• Generate unique counts without Power Pivot.
• Create cross-tab reports with PIVOTBY.
• Control row and column sorting independently.
• Recognise current beta limitations and potential bugs.
• Identify scenarios where Pivot Tables outperform formula-based summaries.

Who is the Target Audience?

• Excel users are already comfortable with Pivot Tables.
• Business analysts and reporting professionals frequently use these tools.
• Excel power users are exploring modern dynamic arrays.
• Finance professionals often work with summarised reports.

Basic Knowledge:

• Demonstrate proficiency with basic Excel formulas.
• Understand the purpose and functionality of Pivot Tables.
• Be familiar with structured tables (Ctrl + T).
• Preferably use Excel 365, which supports GROUPBY and PIVOTBY.