Why build a report without a PivotTable
A PivotTable is an object you drop on the sheet and refresh with a click. GROUPBY and PIVOTBY are formulas, so they live in the grid, spill their results, and recalc the moment the source changes. For a dashboard fed by a Table, a formula report means one less thing to right-click and refresh.
The trade-off is control. PivotTables give you drag-and-drop fields and a field list; these functions give you exact arguments you must spell out. Once you know the pattern, the formula is faster to reuse than rebuilding a PivotTable every month on a new file.
GROUPBY: one field, one total
With a Sales table named Sales, type =GROUPBY(Sales[Region],Sales[Amount],SUM) in an empty cell. Excel groups every row by Region and sums Amount for each. The result spills down as many rows as you have regions, like West, East, North.
If Sales holds West 92,300, East 80,100, North 112,100, the formula returns those three rows with totals. Because the source is a Table, adding a South row next month extends the spill with no edit from you.
Swap SUM for AVERAGE to get mean Amount per region, or COUNT for row counts. I recommend SUM for revenue and AVERAGE for ticket size; a common mistake is leaving SUM when the manager asked for an average and the numbers look too big.
I recommend wrapping GROUPBY in a header row by setting the field header argument, so the spill shows 'Region' and 'Amount' labels. A common mistake is leaving headers off, which makes the spilled grid ambiguous when you paste it into a slide.
Filter and sort inside GROUPBY
GROUPBY takes extra arguments for filtering and sorting without you touching the source. You can show only regions above a threshold or sort the report largest first, all inside the one formula.
Add the filter argument: =GROUPBY(Sales[Region],Sales[Amount],SUM,,,Sales[Amount]>50000). This drops any region under 50,000 from the spill. In my experience this keeps a monthly deck from listing ten tiny regions nobody reads.
Use the sort argument to order by total descending. The spill then leads with North at 112,100 instead of alphabetical West. A common mistake is sorting the source Table and expecting the report to follow; the function sorts on its own terms, not the Table's.
Set the totals argument so the spill ends with a Total row summing every region. This will break the layout if you already have a total in the source; keep the total in one place, either the function or the Table, never both.
PIVOTBY: region down, month across
PIVOTBY adds a column field, so you get a cross-tab instead of a single list. Think of it as GROUPBY with a second grouping axis. It is the function form of a two-field PivotTable.
Type =PIVOTBY(Sales[Region],Sales[Month],Sales[Amount],SUM). Region goes down the rows, Month across the columns, and each cell is the summed Amount. A Q1 grid shows Jan, Feb, Mar totals per region in one spilled block.
If West totals 30,000 in Jan, 31,000 in Feb, 31,300 in Mar, those land in the West row across three columns. Because the fields are Table columns, new months spill new columns automatically as rows arrive.
Reverse the first two arguments to put Month down and Region across. I recommend region down for a regional review and month down for a trend review; the data is the same, only the question changes which axis leads.
Avoid mixing a PivotTable and a PIVOTBY on the same source if both must match; they refresh on different triggers. I recommend picking the function route for any sheet that also uses FILTER or XLOOKUP, so everything recalcs together in one pass.
GROUPBY versus a real PivotTable
Both answer 'total by group', but they behave differently when shared. A PivotTable is familiar to most colleagues and supports slicers natively. GROUPBY is lighter, formula-driven, and chains with other dynamic arrays. Choose by who will maintain the file.
Because the output is a spill, wrap it: =SORT(GROUPBY(Sales[Region],Sales[Amount],SUM),2,-1) sorts the report by total. A PivotTable cannot be fed straight into SORT without copying values first, so the function wins for layered calculations.
If the report needs clickable Region buttons for a non-technical reader, build a PivotTable and insert a Slicer. A common mistake is forcing GROUPBY on a manager who expects to click; give them the object they know, and keep functions for your own models.
Common GROUPBY and PIVOTBY mistakes
These functions expect structured references like Sales[Region]. If you pass A2:A100, the spill still works but will not grow. This will break the report when rows are added; always reference a Table column so the spill extends on its own.
A #SPILL error appears when something sits in the cells the result needs. A common mistake is a label or total under the formula. Clear the area below, or the report refuses to spill and shows only the error.
Summing a text column returns 0, and averaging a Region column errors. Match the function to the data: SUM on Amount, COUNT on IDs. I recommend testing the formula on one region before spilling the full report.
Worked example: regional monthly report
Your Sales table has 1,200 rows across West, East, North for Jan to Mar with an Amount column. You need a per-region total and a region-by-month grid, with no PivotTable to refresh.
Write =GROUPBY(Sales[Region],Sales[Amount],SUM). West returns 92,300, East 80,100, North 112,100. The spill is the whole monthly regional summary in one formula, and it updates the instant you paste April rows.
Write =PIVOTBY(Sales[Region],Sales[Month],Sales[Amount],SUM). The block shows three region rows and three month columns, each cell a summed Amount. Add a South region and the spill gains a row with no edit from you.
Wrap with =SORT(...,2,-1) to lead with North at 112,100. Because the inner function is a spill, the sort recalcs together. This is the clean version I send to a manager who wants biggest regions first.
Next steps
Replace one PivotTable in your workbook with a GROUPBY formula this week and watch it recalc without a refresh click. Convert the source to a Sales Table first so the spill grows. Then read our FILTER function guide to feed these reports with a filtered subset of rows.
I recommend naming the source Table clearly and referencing it by name in every GROUPBY and PIVOTBY. When a teammate edits the file, a name like Sales is far easier to trust than a bare range such as A2:A1200.
I recommend naming the source Table and referencing it by name in every GROUPBY and PIVOTBY, so a teammate editing the file trusts Sales over a bare range like A2:A1200. On the Sales table, =SORT(GROUPBY(Sales[Region],Sales[Amount],SUM),2,-1) leads with North at 112,100 with no manual sort. A common mistake is pointing the function at a fixed range, which stops growing when you paste April rows and the regional total silently misses new data. I convert the source to a Table before I write the first formula.
Combine GROUPBY with FILTER for a subset
Sometimes you want the report on only one region, not the whole table. Wrap the source in FILTER so GROUPBY sees a trimmed set. Write =GROUPBY(FILTER(Sales[Region],Sales[Region]='West'),FILTER(Sales[Amount],Sales[Region]='West'),SUM) and the spill shows just West's total.
This pattern keeps the full Sales Table intact while the report focuses. In my experience a regional lead only wants their number, and handing them a one-row spill beats a three-region grid they have to read past. The FILTER argument does the trimming inside the formula.
Build the FILTER first as its own test: =FILTER(Sales[Amount],Sales[Region]='West') should return the West amounts only. Once that works, nest it inside GROUPBY as the second argument. A common mistake is filtering the wrong column, which returns a region name where the function expects a number to sum.
If the filter matches nothing, GROUPBY spills an error. Add the if_empty argument to FILTER like 'None' so the report shows a label instead of #CALC. This will break a clean deck if a typo in the region name quietly empties the whole chart.
The same idea works for a date window. Filter Sales[Month] to 'Mar' and GROUPBY returns March only, no PivotTable refresh needed. Because everything is a formula, the regional and monthly views live on one sheet and recalc together when you paste April rows.
If your report needs both a region total and a running share, wrap the GROUPBY in a second formula that divides each row by the grand total. For North at 112,100 against a 284,500 total that returns about 39 percent, and the spill keeps the ratio correct as rows arrive each month.
