GroupBy is a new and genuinely great function coming to Excel. It’s one of two new ‘aggregation functions’ but we prefer to call them fast summary powers. The more we use Groupby() , the more we like it. So we decided to focus on it with detail.
Both Groupby() and PivotBy() are in beta testing for some Microsoft 365 Insiders. Look for them in upcoming public Excel 365 releases.
Excel has been able to group or combine data for many years but now all you need is a single function to summarize a table like this:
There’s a related Pivotby() function that we’ll explain separately.
GroupBy()
GroupBy() lets you aggregate a table with just three parameters.
- What to group e.g Category
- What to group by e.g. Sales Value
- How to combine (SUM, Count, Average, Max, Min and many more).
Beware!
A few things to watch out for.
Groupby() returns a dynamic array so make sure you leave room below to avoid #SPILL errors.
A manually filtered table is reflected in the GroupBy() results. Here we’ve filtered the sales table to show only 2017 rows, Groupby() has updated to the same filter.
That could be what you want but be aware that a change in the source data filtering will affect the Groupby() summary. If the source is on another sheet, the filtering might go unnoticed.
Many Calculation options
Have multiple GroupBy() formulas to summarize data in different ways and Microsoft has even added a new function to help.
Here’s the (almost) full list of available summary functions.
- SUM
- PERCENTOF
- AVERAGE
- MEDIAN
- COUNT
- COUNTA
- MAX
- PRODUCT
- ARRAYTOTEXT
- CONCAT
- STDEV.S
- STDEV.P
- VAR.S
- VAR.P
- MODE.SNGL
All those options are existing Excel functions except one, PERCENTOF. More on that below.
Add any Lambda() function to GroupBy
We said ‘almost all’ because in addition to the list of in-built functions, you can choose any compatible Lambda() function to process the data.
Here’s a Lambda function which returns a ✅ for each item instead of a total from Count() using LAMBDA(x,REPT("✅", COUNT(x)))
Or make it a separately listed Lambda function like this:
Can we say again how much we LOVE Excel Labs!!!!
PERCENTOF
Percentof() is a new and very simple function that gives the percentage that a data subset is of complete data set.
Microsoft is clear that PercentOf() is the same as writing =SUM(data_subset)/SUM(data_all)
We suspect that PercentOf() is an in-built Lambda function because it’s simple to make your own version like this
Percentof() can be used on its own though it’s been added to Excel so GroupBy() can have a simple and obvious percentage summary. Nice touch.
Sorting
You could wrap Groupby() around a Sort() function to reorder the list, but you don’t have to because there’s a sort option included.
[sort_order] paramater lets you choose the result column to sort by; 1, 2, 3 in ascending order.
Use negative numbers for descending order -1, -2 etc.
Headings
Choose whether to include column headings or not.
0 – No headings.
1 – Yes but don’t show the headings
2 – No headings but generate them – default.
3 – Show headings.
We’re not clear on the uses of options 1 and 2 either and the current Microsoft documentation doesn’t mention this option at all!
Totals and sub-totals
Good options for showing totals and subtotals
0 – no totals
1 – show Grand totals
2 – show Grand and sub-totals – default
-1 – show at top the Grand totals
-2 – show at top the Grand and subtotals
Choose your rows
Select which rows to include in the grouping using an array of Boolean (True/False) values. One for each row in the table data and the array length has to be the same as the number of rows.
Who gets it?
Is in Excel 365 for Windows and Mac PLUS Office 2024.
Microsoft warns that the two functions are still in testing and might change before public release. They say “Until final, we do not recommend using them in important workbooks” though you’ll be very tempted to drop Groupby() into sheets right now!
Pivotby – Excel’s new faster PivotTables
A better way to view formulas in Excel
Lambda – strange name for a really good extra in Excel
