GROUPBY and PIVOTBY in Excel: Build Monthly Summaries That Update Themselves
9 min read
The Excel GROUPBY function builds a summary table with a single formula. Point it at a column to group by, a column to add up and a function such as SUM, and it returns one row per group with a total at the bottom. Its sibling PIVOTBY adds a second axis, so you get a small pivot-style grid. Both recalculate as soon as the source data changes, which makes them a good fit for monthly summaries that you would otherwise rebuild or refresh by hand. All the examples below use one small sales table, so you can check every result by hand.
Which Excel versions have GROUPBY and PIVOTBY
Microsoft released both functions to Microsoft 365 users on the Current Channel in September 2024. They work in Excel for Microsoft 365 on Windows and Mac and in Excel for the web.
They are not in the perpetual versions. Excel 2024, 2021 and older don’t have them. If someone opens your file in one of those versions, the cells show #NAME? once they recalculate. Check this before you build a report that gets shared outside your team. You can see your version under File > Account.
GROUPBY syntax
=GROUPBY(row_fields, values, function, [field_headers], [total_depth], [sort_order], [filter_array], [field_relationship])
Only the first three arguments are required:
row_fieldsis what you group by, such as month, region or product. It can be more than one column.valuesis what you aggregate, such as an amount column.functionis how you aggregate:SUM,AVERAGE,COUNT,MAX,PERCENTOFor your ownLAMBDA.
The optional arguments control headers, totals, sorting and filtering. The examples below use the ones you will need most.
Example data
All examples use this sales data, formatted as an Excel table (Ctrl+T) and named Sales. A table matters here: when you add next month’s rows, Sales[Amount] grows with them and every formula below picks them up.
| Date | Region | Product | Amount |
|---|---|---|---|
| 2026-01-08 | North | Widget A | 1,200 |
| 2026-01-15 | South | Widget B | 800 |
| 2026-01-22 | North | Widget B | 450 |
| 2026-01-29 | South | Widget A | 950 |
| 2026-02-05 | North | Widget A | 1,100 |
| 2026-02-12 | South | Widget A | 700 |
| 2026-02-19 | North | Widget B | 600 |
| 2026-02-26 | South | Widget B | 900 |
| 2026-03-05 | North | Widget A | 1,300 |
| 2026-03-12 | South | Widget B | 850 |
| 2026-03-19 | North | Widget B | 500 |
| 2026-03-26 | South | Widget A | 1,000 |
Monthly totals with the Excel GROUPBY function
The data has dates, not months, so the formula turns each date into a month label with TEXT before grouping:
=GROUPBY(TEXT(Sales[Date], "yyyy-mm"), Sales[Amount], SUM)
Result:
| 2026-01 | 3,400 |
| 2026-02 | 3,300 |
| 2026-03 | 3,650 |
| Total | 10,350 |
The yyyy-mm format sorts correctly as text, so months stay in calendar order. Format codes in TEXT depend on your Excel language settings, so if yyyy doesn’t work in your locale, use the year code your version expects. Another option is to group by the first day of each month and format the result column as dates:
=GROUPBY(EOMONTH(Sales[Date] + 0, -1) + 1, Sales[Amount], SUM)
The + 0 matters: given a whole column, EOMONTH returns #VALUE!, and adding zero turns the column into numbers it accepts. The formula returns date serial numbers for 1 January, 1 February and 1 March 2026. Apply a custom number format such as mmm yyyy to the first column of the result.
Because the result spills, you only type the formula in one cell. In April, add the April rows to the table and a 2026-04 row appears above the total.
Filter, sort and add columns
GROUPBY with a filter
The seventh argument, filter_array, takes a TRUE/FALSE test with one result per row. To show only the North region:
=GROUPBY(TEXT(Sales[Date], "yyyy-mm"), Sales[Amount], SUM, , , , Sales[Region] = "North")
Result:
| 2026-01 | 1,650 |
| 2026-02 | 1,700 |
| 2026-03 | 1,800 |
| Total | 5,150 |
The empty commas skip the optional arguments in between. To make the region a dropdown, put it in a cell (say H1) and use Sales[Region] = H1. For two conditions, multiply the tests and compare with 1: ((Sales[Region] = "North") * (Sales[Product] = "Widget A")) = 1.
Sort by the biggest value
sort_order refers to the result columns: 1 is the first row field, 2 is the first values column, and a minus sign sorts descending. This ranks products by total sales:
=GROUPBY(Sales[Product], Sales[Amount], SUM, , , -2)
Result:
| Widget A | 6,250 |
| Widget B | 4,100 |
| Total | 10,350 |
GROUPBY with multiple columns
Pass two adjacent columns as row_fields and set total_depth to 2 to get subtotals:
=GROUPBY(Sales[[Region]:[Product]], Sales[Amount], SUM, , 2)
The result groups products within each region: North has Widget A at 3,600 and Widget B at 1,550 with a subtotal of 5,150, South has Widget A at 2,650 and Widget B at 2,550 with a subtotal of 5,200, and the grand total is 10,350. Subtotals only work when row_fields has at least two columns. If the columns you want aren’t next to each other, combine them with HSTACK(Sales[Product], Sales[Region]), which also lets you choose their order.
Several calculations at once
Give function more than one function with HSTACK to get one result column per function. This shows the monthly total and the number of transactions:
=GROUPBY(TEXT(Sales[Date], "yyyy-mm"), Sales[Amount], HSTACK(SUM, COUNT))
For January the row shows 3,400 and 4. The total row shows 10,350 and 12.
Share of total with PERCENTOF
PERCENTOF is an aggregation function made for these summaries. It returns each group’s share of the total:
=GROUPBY(Sales[Product], Sales[Amount], PERCENTOF)
With the result formatted as a percentage, Widget A shows 60.4%, Widget B 39.6% and the total 100%.
PIVOTBY: months across the top
PIVOTBY works like GROUPBY with an extra col_fields argument:
=PIVOTBY(row_fields, col_fields, values, function, [field_headers], [row_total_depth], [row_sort_order], [col_total_depth], [col_sort_order], [filter_array], [relative_to])
For a regional report with months as columns:
=PIVOTBY(Sales[Region], TEXT(Sales[Date], "yyyy-mm"), Sales[Amount], SUM)
Result:
| 2026-01 | 2026-02 | 2026-03 | Total | |
|---|---|---|---|---|
| North | 1,650 | 1,700 | 1,800 | 5,150 |
| South | 1,750 | 1,600 | 1,850 | 5,200 |
| Total | 3,400 | 3,300 | 3,650 | 10,350 |
When April’s data arrives, a new 2026-04 column appears before the Total column. With PERCENTOF, the last argument relative_to sets what each value is a percentage of: 0 for column totals (the default), 1 for row totals, 2 for the grand total.
PIVOTBY vs PivotTable
PivotTables are still the better tool for some jobs:
| GROUPBY / PIVOTBY | PivotTable | |
|---|---|---|
| Updates when data changes | Automatically | After Refresh |
| Changing the layout | Edit the formula | Drag and drop fields |
| Slicers and PivotCharts | Not built in (workarounds exist) | Built in |
| Grouping dates by month or quarter | Do it in the formula or source data | Right-click > Group |
| Data Model and multiple tables | No | Yes, with Power Pivot |
| Formatting | Manual or conditional formatting | PivotTable styles |
| Custom calculations | LAMBDA |
Calculated fields, DAX |
| Excel versions | Microsoft 365 only | All desktop versions |
When the functions were still in preview in March 2024, Excel Campus listed the gaps that matter most in daily use: no built-in formatting styles, slower layout changes, no slicers or PivotCharts, and less control over subtotals. Each has a workaround with conditional formatting or helper formulas, but you have to build it.
My rule of thumb: use a PivotTable when people need to explore the data themselves by dragging fields around. Use GROUPBY or PIVOTBY when the layout is fixed and the numbers must be current, such as a monthly summary that feeds a dashboard or another calculation.
Feeding charts and dashboards
A summary that feeds a chart usually shouldn’t contain the Total row, or the chart gets a giant extra bar. Set field_headers and total_depth to 0:
=GROUPBY(TEXT(Sales[Date], "yyyy-mm"), Sales[Amount], SUM, 0, 0)
Say that formula sits in F2. Other formulas can refer to the whole result with the spill reference F2#. This returns the latest month’s total, 3,650 in our data:
=TAKE(F2#, -1, -1)
In Excel for Microsoft 365 and Excel 2024, a chart built on a spill range grows when the spill range grows, so a column chart of F2# gains a bar each month without editing the chart’s data range. On a dashboard sheet, that gives you a small set of formulas that stay current without anyone pressing Refresh.
Compatibility warning before you share
Because GROUPBY and PIVOTBY exist only in Microsoft 365, check who opens the file. A client on Excel 2021 or 2024 will get #NAME? errors once the workbook recalculates. If the file has to leave your organization, paste the summary as values into a copy or keep a PivotTable version for those readers. Also leave room below and to the right of each formula: if anything blocks the spill area, the formula shows #SPILL! instead of a result.
FAQ
What is the Excel GROUPBY function?
GROUPBY is an Excel function that groups rows by one or more fields and aggregates the values for each group, such as summing sales by month. It returns a spilled summary table with optional totals, sorting and filtering, and it updates automatically when the source data changes.
How do I use GROUPBY to sum by month?
Wrap the date column in TEXT to create month labels: =GROUPBY(TEXT(Sales[Date], "yyyy-mm"), Sales[Amount], SUM). Alternatively, use EOMONTH(Sales[Date] + 0, -1) + 1 to group by the first day of each month and format the result as dates.
Can GROUPBY replace PivotTables?
For fixed reports that should always be current, often yes. PivotTables are still better for drag-and-drop exploration, slicers, PivotCharts, date grouping and the Data Model, and they work in every desktop version of Excel.
Why do I get #NAME? with GROUPBY?
Your Excel version doesn’t have the function. GROUPBY and PIVOTBY are available in Excel for Microsoft 365 and Excel for the web, but not in Excel 2024, 2021 or older perpetual versions.
Can GROUPBY group by multiple columns?
Yes. Pass adjacent columns such as Sales[[Region]:[Product]], or combine separate columns with HSTACK. Set total_depth to 2 for subtotals.
Want monthly summaries that build themselves?
Most monthly reporting is a few summaries like the ones above, sitting on data that someone first has to export, clean and paste. I build the whole chain in the Excel you already have: Power Query to bring in the data, GROUPBY or PivotTables to summarize it, and a dashboard on top. See Dashboards and Report Automation, both at a fixed price agreed before work starts, or book a free 20-minute call.
👉 Download the sample workbook (.xlsx, 31 KB) with every example from this article. It needs Excel for Microsoft 365 or Excel for the web.