--- title: "GROUPBY: Excel's Answer to Pandas groupby - Without a Pivot Table in Sight" date: 2026-06-15T00:00:00Z updated: 2026-06-14T07:51:37Z tags: ["GROUPBY", "Excel", "Dynamic Arrays", "Pivot Tables", "pandas", "Microsoft 365", "Modern Excel Functions"] canonical: https://bianca.codes/blog/groupby-excel-answer-to-pandas-groupby/ --- # GROUPBY: Excel's Answer to Pandas groupby - Without a Pivot Table in Sight _A pivot table is a tool you operate. GROUPBY is the same group-and-aggregate logic as a single spilled formula - the summary recalculates itself, with no fields to drag and nothing to refresh._ Anyone who has spent time in pandas knows the one line that does the work of a whole pivot table. `df.groupby("Region")["Amount"].sum()` takes a flat table, splits it into groups, aggregates each one, and hands back a tidy summary. It is the first thing data people reach for, because grouping and aggregating is most of what analysis actually is. Excel has always been able to do the same thing. It just made you do it by hand. You built a pivot table, dragged Region into Rows and Amount into Values, picked SUM from a menu, and then spent the rest of the project right-clicking Refresh every time the source data moved. GROUPBY is that whole ritual collapsed into a function. You type the grouping, the values, and the aggregation, and the grouped summary spills out wherever the cursor is - and stays current on its own. ## The mental model A pivot table and GROUPBY do the identical thing under the hood: split, apply, combine. Split the rows into groups by some key, apply an aggregation to each group, combine the results into one row per group. The pandas crowd calls this split-apply-combine and says it out loud. Excel buried it behind a drag-and-drop surface so thoroughly that most people never notice it is the same operation. The difference that matters is what kind of object you get back. A pivot table is a tool you operate. It has a cache, a layout, a refresh button, and a set of fields you rearrange by hand. GROUPBY is a formula you write. It has arguments. That single distinction is the reason to care: a tool you operate goes stale the moment you look away, because operating it is a manual step. A formula reads its inputs live every time the sheet recalculates, the same as SUM or VLOOKUP. There is no refresh because there is nothing cached to go stale. So the mental shift is small but total. Stop thinking of grouping as something you assemble in a dialog. Start thinking of it as something you call, with the grouping key, the values, and the function as three arguments you pass in. Once it is a function, it composes with everything else: you can filter its input, sort its output, stack two aggregations side by side, and nest it inside another formula. A pivot table can do none of those things without you standing over it. ## The syntax The function has three required arguments and a row of optional ones: ```vba =GROUPBY(row_fields, values, function, [field_headers], [total_depth], [sort_order], [filter_array], [field_relationship]) ``` The three that matter map exactly onto a pivot table. `row_fields` is the Rows area - the column you group by. `values` is the Values area - the numbers being summarised. `function` is the aggregation you would have picked from the dropdown: SUM, AVERAGE, COUNT, MAX, MIN, and the rest. The bare minimum is one of each: ```vba =GROUPBY(Sales[Region], Sales[Amount], SUM) ``` That spills one row per region with its summed amount, sorted, ready. The thing worth noticing is the third argument. You are passing `SUM` itself, the function, not `SUM(...)` with a range inside it. GROUPBY accepts a function as a value - the same eta-reduced LAMBDA trick that BYROW and MAP use - and applies it to each group internally. Which means you are not limited to the built-in list. A custom LAMBDA works just as well in that slot, so any aggregation you can express, you can group by. Two aggregations at once, by stacking the values and functions: ```vba =GROUPBY(Sales[Region], HSTACK(Sales[Amount], Sales[Amount]), HSTACK(SUM, AVERAGE)) ``` That returns region, total, and average side by side. The shape is always the same: line up the value columns with the functions that summarise them. ## A real example Take a flat sales table called `Sales` with Region, Channel, and Amount columns, the kind of export that lands in your inbox every Monday. You want total amount by region and channel, with a subtotal per region and a grand total at the bottom, sorted so the biggest regions sit on top. In pivot-table terms that is two row fields, a values field, subtotals switched on, and a sort applied - a few minutes of clicking, repeated every week. As a formula it is the optional arguments doing the work: ```vba =GROUPBY( Sales[[Region]:[Channel]], Sales[Amount], SUM, 3, 2 ) ``` What the arguments are doing: `Sales[[Region]:[Channel]]` passes two columns as the grouping key, which gives you the hierarchy - Channel nested inside Region. The `3` is `field_headers`, telling GROUPBY the data has headers and to show them. The `2` is `total_depth`, which asks for both the grand total and a subtotal for each region. The output spills as a finished report: ```plaintext Region Channel Amount East 48,200 Online 31,000 Reseller 17,200 West 39,500 Online 22,750 Reseller 16,750 Total 87,700 ``` Need only this year's rows? The `filter_array` argument takes a boolean array the same height as the data, so you skip the empty optional slots and pass a condition: ```vba =GROUPBY(Sales[Region], Sales[Amount], SUM, , , , Sales[Year]=2026) ``` No helper column, no filtering the source, no second pivot. When next Monday's export overwrites the table, every one of these recalculates in place. The report you built once is the report you have forever. ## What it doesn't do It is Microsoft 365 only. GROUPBY is one of the newest functions in Excel, rolled out across Microsoft 365 through 2024, and it does not exist in Excel 2021, 2019, or the perpetual Excel 2024. On those, the formula returns `#NAME?` and the pivot table stays. If you are not sure your build has it yet, type `=GROUP` and see whether autocomplete offers it. It is not interactive. A pivot table lets a user drag fields around, expand and collapse groups, and slice with a slicer. GROUPBY produces a fixed output shape that changes only when you edit the formula. If the deliverable is a dashboard people poke at, that interactivity is the whole point, and a pivot table is still the right tool. GROUPBY is for the summary that should just be correct and current, not played with. It summarises down, not across. GROUPBY groups into rows. The moment you want a true cross-tab - regions down the side, months along the top - that is its sibling PIVOTBY, which takes a fourth argument for the column fields. Same idea, same arguments, one more dimension. And it has a scale ceiling like every in-grid array function. On a few thousand rows it is instant. On hundreds of thousands it will make the sheet think, and the work belongs in Power Query or the data model instead. The formula bar is the right home for this at report scale, not at warehouse scale. ## Frequently Asked Questions ### **Is GROUPBY the same as the GROUPBY in pandas or DAX?** The idea is shared, the function is not. Excel's GROUPBY, pandas `groupby`, Power Query's Table.Group, and DAX's GROUPBY all perform split-apply-combine, but they are separate implementations in separate environments with different syntax. A formula written for one does not carry over to another. This post is about the Excel worksheet function only. ### **Does GROUPBY work in Excel 2021 or 2019?** No. It requires Microsoft 365 and is not in any perpetual licence, including Excel 2021, 2019, and the perpetual Excel 2024. Those versions return `#NAME?` because the function name is not recognised. Excel for the web and the Microsoft 365 desktop apps on a current channel have it. ### **Can I group by more than one column?** Yes. Pass a multi-column range as the first argument, for example `Sales[[Region]:[Channel]]`, and GROUPBY builds a hierarchy with the second field nested inside the first. Combine that with a `total_depth` of 2 to get a subtotal for each top-level group plus a grand total. ### **How do I get sum and average in the same result?** Stack them. Pass the value column once per aggregation with HSTACK, and pass the matching functions with HSTACK in the same order: `=GROUPBY(Sales[Region], HSTACK(Sales[Amount], Sales[Amount]), HSTACK(SUM, AVERAGE))`. The value columns and the functions line up position by position. ### **Why use GROUPBY instead of a pivot table?** Because it recalculates itself. A pivot table reads from a cache taken at refresh time, so it goes stale until you refresh it. GROUPBY reads the live range on every recalculation, like any formula, so the summary is never out of date. It also composes - you can filter, sort, and nest it - which a pivot table cannot do without manual steps. Reach for a pivot table when you need the interactive surface; reach for GROUPBY when you need a summary that maintains itself. ### **What is the difference between GROUPBY and PIVOTBY?** GROUPBY groups into rows only. PIVOTBY adds a column-fields argument and produces a two-way cross-tab, with one set of groups down the side and another across the top. If your summary is a list, use GROUPBY; if it is a matrix, use PIVOTBY. The arguments are otherwise the same family. ## Where this fits in the modern Excel kit The ninety-second version - swap your single most-refreshed pivot table for one GROUPBY and never refresh it again - is the Jun 13 Click Bait. For a full build that wires GROUPBY into a real reporting sheet from a messy export, see the [Jun 16 Build It](#). And for the argument about whether pivot tables have a future at all once functions can do this, there is the [Jun 17 Hot Take](#). Between them, the pivot table stops being the default and becomes what it always should have been: one option among several, picked on purpose.