When should I use PIVOTBY instead of GROUPBY?

Use PIVOTBY when you want a matrix layout, with categories down the side and months across the top like a classic crosstab. GROUPBY groups down a single axis, so it produces an indented, report-style layout instead. It is the same data in a different shape, so pick the function that matches the output you want.

Can I group by more than two fields in GROUPBY?

Yes. The row_fields argument can hold as many columns as you like. Stack them with HSTACK in the order you want the hierarchy, outermost first, and GROUPBY builds a nested level for each, with subtotals at every level as long as total_depth allows for them.

How do I show two metrics like revenue and units with GROUPBY?

Pass multiple value columns and a matching set of functions. Use HSTACK(Sales[Revenue], Sales[Units]) for the values and HSTACK(SUM, SUM) for the functions to get two aggregated columns side by side. The functions can differ, such as SUM for one and AVERAGE for the other, as long as they line up with the value columns.

Will GROUPBY slow down on a large transaction table?

GROUPBY does its work in a single pass over the data, so it holds up well into the tens of thousands of rows. If a workbook genuinely crawls, the cause is usually volatile formulas elsewhere or whole-column references, not GROUPBY itself.

Can I filter the data before grouping it with GROUPBY?

Yes. The filter_array argument takes a column of TRUE and FALSE values the same height as your data. Passing something like (Sales[Region]="West") as that argument summarises only the western sales without touching the source table or adding a helper column.

Build a Self-Updating Sales Summary with Excel GROUPBY

Q: Which versions of Excel have GROUPBY?

GROUPBY is available in Excel for Microsoft 365 and Excel for the web. It is not in the perpetual releases such as Excel 2021 or 2024, and opening the file there shows the formula as #NAME?. If your work machine lags the update channel, you may not have it yet even on Microsoft 365.

By the end of this you'll have a monthly sales summary, broken down by product category, that lives in a single cell. Add a row to your transaction table and the summary updates on its own. No PivotTable, no Refresh All, no copy-paste into a second sheet that's stale by lunchtime.

You need Excel for Microsoft 365 or Excel for the web - GROUPBY doesn't exist in the perpetual versions (2021, 2024). Your source is the usual thing: a flat list of transactions with a date, a category, and an amount on every row. Mine has Date, Category, Product, Units, and Revenue. Yours can have more columns; GROUPBY only touches the ones you point it at.

Why a formula beats a PivotTable here

A PivotTable is the obvious tool, and it's fine right up until someone adds yesterday's sales and forgets to hit Refresh. Then the number on the report is wrong and nobody knows. GROUPBY is a formula, so it recalculates the moment the data changes - the same way SUM does. Pair it with a structured Table as the source and the range grows itself when rows are added. That combination is the whole trick: the Table handles "new data showed up", the formula handles "the summary reflects it". Nothing to refresh, because nothing was ever frozen.

Step 1: Turn the raw data into a Table

Click any cell in your transaction data and press Ctrl+T. Confirm that your data has headers, then go to the Table Design tab and rename it from Table1 to Sales.

This is the step people skip, and it's the one doing the heavy lifting. A structured reference like Sales[Revenue] means "every revenue row, however many there are right now". When a new row lands at the bottom of the Table, it joins Sales automatically, so any formula pointing at Sales[Revenue] picks it up with no edit from you. Skip this and you're back to maintaining A2:A500 ranges by hand, which is exactly the chore we're here to kill.

Step 2: Get the smallest summary that works

Pick an empty cell with room below it and to the right, and write:

vba

=GROUPBY(Sales[Category], Sales[Revenue], SUM)

Three arguments, in the order GROUPBY always wants them: what to group by, what to aggregate, and how to aggregate it. row_fields is the column whose distinct values become your rows. values is the column being totalled. function is the aggregation - here the eta-reduced SUM, which is just SUM with no brackets after it.

It spills a two-column result: one row per category, with total revenue beside it. That's the entire summary. Everything from here is making it readable.

Step 3: Sort it and give it a total

vba

=GROUPBY(Sales[Category], Sales[Revenue], SUM, , 1, -2)

The two additions are positional arguments, so the commas matter. The skipped one is field_headers, left blank to let GROUPBY decide for itself. Then total_depth of 1 adds a grand total row at the bottom. sort_order of -2 sorts by the second column - revenue - in descending order, because the minus sign means reverse.

Now the biggest category sits on top and there's a total you didn't have to write a separate SUM for:

plaintext

Category        Revenue
Hardware        184,200
Software        142,750
Services         98,400
Accessories      31,900
                457,250

Step 4: Break it down by month

Most people want the summary by month and category, not category alone. GROUPBY takes more than one grouping column - stack them and it builds a hierarchy. The catch: there's no Month column in the source, only a Date. So derive one inline.

vba

=GROUPBY(
  HSTACK(TEXT(Sales[Date], "yyyy-mm"), Sales[Category]),
  Sales[Revenue],
  SUM,
  ,
  2
)

HSTACK glues the derived month onto the category column, so row_fields is now two columns wide. TEXT(Sales[Date], "yyyy-mm") turns each date into text like 2026-03 - deliberately year-first so it sorts chronologically (more on that in a second). total_depth of 2 asks for grand totals and subtotals, so each month gets its own subtotal under its categories.

The result reads like an indented report: month, its categories, a month subtotal, repeat, then a grand total at the very bottom.

plaintext

2026-01
   Hardware        58,300
   Software        41,200
   Services        22,900
   2026-01 Total  122,400
2026-02
   Hardware        61,750
   Software        49,100
   ...

Step 5: Prove it updates itself

Go to the first empty row under the Sales table and type a new transaction. The moment you commit it, that row is part of the Table, Sales[Revenue] now includes it, and every GROUPBY formula pointing at the Table recalculates. The category total moves. If the sale falls in a new month, a new month block appears in the breakdown on its own.

You did nothing but add data. No Refresh, no dragging a range wider, no reopening a PivotTable's source dialog. That's the self-updating part, and it's just structured references and dynamic arrays doing what they were built to do.

Common mistakes

Grouping by a month that sorts alphabetically.

It's tempting to format the month as "mmm yyyy" because "Mar 2026" reads nicely. Then April sorts above January, because alphabetically it does, and your report looks broken to anyone glancing at it. Group by "yyyy-mm" so the text sorts in date order, or group by a real first-of-month date with EOMONTH and format the display separately. Pretty-but-wrong order is the single most common GROUPBY-by-month bug.

Pointing at whole columns instead of the Table.

=GROUPBY(A:A, D:D, SUM) technically runs, but you've thrown away the reason you're here. Whole-column references don't grow and shrink with the data - they're already as large as they can be, so blank rows get pulled into the grouping and you get a stray empty category. You've also decoupled the formula from the Table, which means the self-updating behaviour is now down to luck. Point at Sales[Column], never at A:A.

No room for the result to spill.

GROUPBY returns a whole array, not one value. If anything is sitting in the cells it needs to fill, you get #SPILL! and nothing else. Put the formula somewhere with empty space below and to the right - ideally on its own sheet, or well clear of the source data - and the error clears. It isn't a problem with the formula; it's furniture in the way.

Frequently Asked Questions

Which versions of Excel have GROUPBY?

Excel for Microsoft 365 and Excel for the web. It isn't in the perpetual releases - Excel 2021, 2024, or anything older - and if you open the file there the formula shows as #NAME?. If your work machine lags the update channel, you might not have it yet even on 365.

When should I use PIVOTBY instead?

Reach for PIVOTBY when you want a matrix - categories down the side, months across the top, like a classic crosstab. GROUPBY groups down a single axis, so it gives you the indented, report-style layout instead. Same data, different shape; pick the one that matches the output you're after.

Can I group by more than two fields?

Yes. row_fields can be as many columns as you like - HSTACK them in the order you want the hierarchy, outermost first. Region, then month, then category builds three nested levels with subtotals at each, as long as total_depth leaves room for them.

How do I show two metrics, like revenue and units?

Pass multiple value columns and a matching set of functions. HSTACK(Sales[Revenue], Sales[Units]) for the values and HSTACK(SUM, SUM) for the functions gives you two aggregated columns side by side. The functions can differ - SUM one, AVERAGE the other - they just have to line up with the value columns.

Will it choke on a big transaction table?

GROUPBY does its work in a single pass over the data, so it holds up well into the tens of thousands of rows. If a workbook genuinely crawls, the culprit is usually volatile formulas elsewhere or whole-column references, not GROUPBY itself.

Can I filter the data before grouping?

Yes - the filter_array argument takes a column of TRUE/FALSE the same height as your data. Passing (Sales[Region]="West") as that argument summarises only the western sales, without touching the source table or adding a helper column.

Where to go from here

If you just want the one-formula version without the month breakdown, the three-minute GROUPBY win strips it back to a single line that replaces your most-used PivotTable.

For what's actually happening under the hood - why this maps so cleanly onto the way analysts already think about grouped data - GROUPBY: Excel's answer to pandas groupby lines the same function up against the tool data people reach for first.

And if you're wondering whether this finally retires the PivotTable, this week's Hot Take makes that case - it lands Wednesday.