Unleash the full potential of Excel’s GROUPBY() function, the game-changer for summarising data in Microsoft Excel. While GROUPBY() seems simple at first glance, clever combinations with other functions open up advanced capabilities: multi-level grouping with automatic subtotals, creating multiple summaries in one formula, custom headings, and even pulling results into other parts of your workbook for consolidation and re-use. This guide walks you through these lesser-known tricks, making your data summaries faster, cleaner and more flexible.
GroupBy() is a wonderful new-ish function in Excel 365 (Microsoft 365), Excel 2024 (Office 2024) and online. It seems simple but, as often happens with Excel function, Microsoft hides within some clever tricks. Strange and seemingly useless functions like HStack() and Drop() can suddenly make sense.
Office Watch went through the basics of Groupby() when it was first released, see Master ExcelGroupBy(): Your Fast-Track to Smarter Data Summaries
Let’s look at some more useful but perhaps, not obvious uses for Groupby(). Multi-level grouping with sub-total either included or above, showing many calculations (Sum, Count, % of total) from one Groupby() with custom heading. Also how to extract values from a Groupby() table to appear or use in other parts of a sheet.
We’ll use this simple table of transactions (SalesTrans) for all the examples.
Group with totals
A basic Groupby() (see above) looks like this:
=GROUPBY(SalesTrans[Region],SalesTrans[Sales],SUM,0)SalesTrans[Region] – which column to group together
SalesTrans[Sales] – which column to use with the summary function
SUM – the summary function, in this case add all the Sales for each Region.
0 – whether to include the table headers in Groupby(), 0 means no.
Multi-level grouping with automatic subtotals
Let’s do a two level summary, grouping first by Region then Product.
Instead of SUM, we’ll use COUNT.
=GROUPBY(SalesTrans[[Region]:[Product]],SalesTrans[Sales],COUNT,0,2)The extra parameter: 2 tells is called “total_depth”. Groupby() adds subtotals for each first level (Region) plus a Grand Total at the bottom.
Totals on top
Change total_depth to a negative number -2 moves the totals to the top of the list.
Many results with one Groupby()
Groupby() can show more than one result from a single formula because the third parameter (function) can use the HSTACK() function.
With that trick you can list many summary functions at once.
Replace with your own headings
Excel automatically adds the headings using the names of the summary functions (COUNT, SUM etc). You can change that to show nicer, custom headings.
VSTACK() puts a list of headings before the Groupby() list.
DROP() removes the headings that Groupby() makes.
Using Groupby() results in other places
Since Groupby() can make sub-totals automatically, why not use those results in other places? That saves Excel calculating the same thing twice and ensures a consistent result.
Start by making a named range (SalesTotalbyRegion) with a Groupby() formula such as
=GROUPBY(SalesTrans[Region], SalesTrans[Sales], SUM, 0,0)
Now you can pluck a sub-total from the Groupby() like this:
=INDEX(SalesTotalbyRegion, MATCH("East", INDEX(SalesTotalbyRegion,,1), 0), 2)The inner INDEX(SalesTotalbyRegion,,1) returns a list of the regions.
MATCH() finds the correct row number that equals the given region “East).
The outer INDEX() finds the matching value in another column with that row number.
If you like, make a dynamic drop-down list of the regions instead of the hard-coded “East”.
=INDEX(SalesTotalbyRegion, MATCH(A3, INDEX(SalesTotalbyRegion,,1), 0), 2)
Where A3 is the drop-down list cell linked to a dynamic array with the formula:
=UNIQUE(INDEX(SalesTrans,,2)) using a ‘two step’ process because Excel still can’t accept a dynamic array formula in Data Validation.
Master ExcelGroupBy(): Your Fast-Track to Smarter Data Summaries
Pivotby – Excel’s new faster PivotTables
