Thornbury Freight runs palletised freight out of six depots — Avonmouth, Grangemouth, Immingham, Teesport, Felixstowe and Dagenham — and pays each depot manager a monthly bonus of 4% of that depot's gross margin. It is the simplest incentive scheme in the business: one percentage, one number per depot, paid in the following month's run.
The number comes off a sheet called Summary, and Summary is one formula in A2:
=GROUPBY(Freight[Depot],Freight[Gross Margin],HSTACK(SUM,PERCENTOF),0,1,-2)
Depot down the side, gross margin summed, each depot's share of the total beside it, and — because it is a league table and a league table is read from the top — sorted descending by margin. That is what the -2 does. The result spills into A2:C8: six depots, then a grand total row.
The Payroll sheet has the six depot names typed into column A, in the order they came out in July, and column B reads the margin across:
B2: =Summary!B2 Avonmouth
B3: =Summary!B3 Grangemouth
B4: =Summary!B4 Immingham
...
C2: =ROUND(0.04*B2,2)
In July that was true. In August, Immingham won the Sunderland contract off Grangemouth, and the three middle rows of the league table changed places.
| July margin | July rank | August margin | August rank | |
|---|---|---|---|---|
| Avonmouth | £184,500.00 | 1 | £171,900.00 | 1 |
| Grangemouth | £152,300.00 | 2 | £96,450.00 | 4 |
| Immingham | £121,750.00 | 3 | £158,600.00 | 2 |
| Teesport | £98,400.00 | 4 | £104,300.00 | 3 |
| Felixstowe | £76,200.00 | 5 | £81,050.00 | 5 |
| Dagenham | £54,850.00 | 6 | £49,700.00 | 6 |
| Total | £688,000.00 | £662,000.00 |
Nobody edited the payroll sheet, because nothing about the payroll sheet had changed. B3 still said =Summary!B3. Summary!B3 still held a number. The number was Immingham's.
What Went Out
| Paid to | Bonus paid | Bonus earned | Difference |
|---|---|---|---|
| Avonmouth | £6,876.00 | £6,876.00 | £0.00 |
| Grangemouth | £6,344.00 | £3,858.00 | +£2,486.00 |
| Immingham | £4,172.00 | £6,344.00 | −£2,172.00 |
| Teesport | £3,858.00 | £4,172.00 | −£314.00 |
| Felixstowe | £3,242.00 | £3,242.00 | £0.00 |
| Dagenham | £1,988.00 | £1,988.00 | £0.00 |
| Total | £26,480.00 | £26,480.00 | £0.00 |
Three of six people were paid the wrong amount and the payroll run was right to the penny, because reordering six numbers does not change what they add to. £26,480.00 is 4% of £662,000.00 whichever manager's name each line carries.
It came out on 8 September, when Immingham's manager asked why his bonus had fallen 14.33% in the month his depot's margin rose 30.27%. The obvious counterpart was never asked: Grangemouth's bonus rose 4.14% in a month its margin fell 36.67%, and nobody queries a payment that is too large.
The £2,486.00 overpayment was not recovered. The two underpayments were put right in the September run. The expensive part was neither: the same Summary sheet fed the Q3 capacity review, where Grangemouth appeared second at £158,600.00 and Immingham third at £104,300.00, and the extra night trunk and two agency drivers went to the depot that had just lost the contract, for a quarter.
1) One Payroll Run, Right in Total, Wrong on Half Its Lines
One £26,480.00 Payroll Run, Right in Total and Wrong on Half Its Lines
The Freight table holds one row per consignment for August 2026: date, depot, customer, service, revenue, cost and gross margin. Gross margin totals £662,000.00 across six depots, which is 3.78% down on July's £688,000.00. The Summary sheet turns that into a depot league table with one formula in A2, sorted best first. The Payroll sheet lists the six depots in column A — typed once, in July, in July's order — and reads the margin out of Summary by cell reference. Every figure in the 'August bonus paid' column is what a manager was actually paid; every figure next to it is 4% of what their depot actually earned. The two columns add to the same number and disagree on three of six rows, and that is the whole article: a positional reference into a sorted summary is a bet that the sort has not changed its mind.
fxCells with formulas are highlighted in green
Hover over formula cells to see the formula and highlight referenced cells
Read the last two rows together. The control total and the by-name rebuild produce the same £26,480.00, and only one of them pays the right people. A total is a property of the set of numbers; a bonus is a property of the pairing between numbers and names, and the two have nothing to say about each other.
🎯 Scenario: Find a cell in your own workbook that reads a spilled summary by address — =Summary!B3, =INDEX(A2#,3,2), anything with a row number in it. Ask one question: what makes row 3 that row? If the answer is "it was row 3 when I wrote this", the formula is a bet on next month's data.
2) What GROUPBY Returns, and Where Its Rows Live
GROUPBY takes the rows of a range, puts them into groups by one or more fields, and returns a summary as a dynamic array — one spilled block, written by one formula in one cell.
=GROUPBY(row_fields, values, function, [field_headers], [total_depth], [sort_order], [filter_array], [field_relationship])
row_fields— the column (or columns) to group by.values— the column (or columns) to aggregate.function— the aggregation, written as a bare function name:SUM,AVERAGE,COUNT,COUNTA,MAX,MIN,MEDIAN,PRODUCT,STDEV.S,STDEV.P,CONCAT,ARRAYTOTEXT,PERCENTOF, or aLAMBDAof your own. No brackets after it: it is being handed over as a function, not called.HSTACK(SUM,PERCENTOF)asks for two columns of answers.field_headers— whether the input has headers and whether the output shows them:0no and no,1yes but hide,2no but show,3yes and show.total_depth—0no totals,1grand total at the bottom (the default),2grand total and subtotals,-1grand total at the top,-2totals at the top.sort_order— which output column to sort on, negative for descending.-2is "descending by the second column of the result".filter_array— a column of TRUE/FALSE the same height asrow_fields, deciding which source rows take part.field_relationship—0hierarchy (the default) or1table, which only matters with more than one row field.
The important sentence is not in that list. The shape of the result is data. The number of rows is the number of distinct values in row_fields; their order is whatever sort_order makes of the current numbers. Both are recalculated from the source on every edit, like any other formula result — which is the point of the function and the whole of its danger.
🎯 Scenario: Put =ROWS(Summary!A2#) in a spare cell. That is the height of your summary today, totals included. Anything downstream that assumes a different height is already wrong; anything that assumes this height is wrong the first time a group appears.
3) Why the Rows Moved
Thornbury's summary moved because it was explicitly sorted by value, and value is the thing that changes. That is not a misuse — "best depot first" is the correct design for a report a human reads — but it guarantees that the row a depot sits on is a function of that month's trading.
Turning the sort off does not make the problem go away, it makes it quieter:
- With
sort_orderomitted, the groups come back sorted by the row field itself, ascending — alphabetical for text. Avonmouth, Dagenham, Felixstowe, Grangemouth, Immingham, Teesport. Stable from month to month, right up until a depot opens, closes, is renamed, or trades nothing at all in a month and vanishes from the list. - A group with no rows is not in the result.
GROUPBYlists the groups it found, not the groups you have. Dagenham shuts for two weeks in August, bills nothing, and there is no Dagenham row — every depot below it moves up one, and the last positional reference falls on the total row.
So the ordering rule to carry around is short: the only stable thing about a GROUPBY row is its label. Not its position, not its distance from the top, not the number of rows above it.
🎯 Scenario: Take the summary you depend on and sort the source table differently — by date descending, say — then recalculate. If anything downstream changes, you have found a positional read. Nothing about the data changed; only the order Excel met it in.
4) The Check That Could Not See It
Thornbury ran one check on the bonus file, and it was a sensible one: the sum of the bonus column against 4% of total margin. £26,480.00 against £26,480.00. It ties every month, and it will tie every month for ever, including the months when every single line is paid to the wrong person.
That is worth stating as a rule, because it is not specific to this workbook: a control total is blind to a permutation. Any check that adds the column up before comparing it has thrown away the pairing of name to number, and the pairing is the thing that broke.
Three checks that can see it:
=SUMPRODUCT(--(B2:B7<>XLOOKUP(A2:A7,CHOOSECOLS(Summary!$A$2#,1),CHOOSECOLS(Summary!$A$2#,2))))
→ 3 rows where the positional read and the name read disagree
=TEXTJOIN(", ",1,CHOOSECOLS(DROP(Summary!A2#,-1),1))
→ "Avonmouth, Immingham, Teesport, Grangemouth, Felixstowe, Dagenham"
=SUMPRODUCT(--(A2:A7<>CHOOSECOLS(DROP(Summary!$A$2#,-1),1)))
→ 3 rows where the payroll order and the summary order disagree
The first is the general form and the one to keep: read the same quantity twice, by two different routes, and count the disagreements. The second is the cheapest: one cell holding the current running order as text, so a changed order is visible at a glance and shows up in a file comparison.
🎯 Scenario: Add the first check to any sheet that reads a summary positionally, park it next to the output, and format it so a non-zero is red. It costs one cell. On Thornbury's August file it would have read 3 before the payroll run went out.
5) Reading a Dynamic Summary Safely
There are two correct answers and the second one is usually better.
Read it by name. The label column is the stable part, so look the label up:
=ROUND(0.04*XLOOKUP($A2,CHOOSECOLS(Summary!$A$2#,1),CHOOSECOLS(Summary!$A$2#,2)),2)
Summary!A2# is the whole spilled block — the spill operator returns every column, not just column A — so CHOOSECOLS picks the label column and the value column out of it. The lookup then follows Grangemouth to row 5, or row 2, or wherever next month puts it. A depot that is missing from the summary returns #N/A, and an #N/A in a bonus column stops a payroll run, which is the correct behaviour for a missing person's pay.
Or do not read it at all. The summary is a rendering of the source; the source is still there:
=ROUND(0.04*SUMIFS(Freight[Gross Margin],Freight[Depot],$A2),2)
This is the version Thornbury shipped. It never touches Summary, so re-sorting the report, adding a column to it, or deleting it outright changes nothing. The rule underneath it is the one to take away:
A GROUPBY is a report for a person. A SUMIFS is a value for a formula. The moment a second formula reads your summary by address, you have made a layout into an interface.
One difference between the two is worth knowing before you pick. For a depot that does not exist in the data, XLOOKUP returns #N/A and SUMIFS returns 0.00. When the output is money paid to a named person, an error is better than a zero: nobody chases a bonus of £0.00 until the month after.
🎯 Scenario: Count the formulas in your workbook that point into a spilled range with a row number. Replace them with XLOOKUP against the label column, or with SUMIFS against the source. Both edits are mechanical; neither changes a single number today, which is exactly why they are easy to postpone.
6) The Total Row Is Inside the Spill
total_depth defaults to 1, so unless you say otherwise your summary ends in a grand total row — and that row is part of the spilled array, not a line Excel drew underneath it.
=SUM(CHOOSECOLS(Summary!A2#,2)) → 1,324,000.00
=SUM(Freight[Gross Margin]) → 662,000.00
Exactly twice, because the body and its own total are both in the block. The same trap catches =COUNTA(A2#) (seven depots, not six), =MAX(CHOOSECOLS(A2#,2)) (which returns the company, not Avonmouth) and =AVERAGE(...) (which comes back about twice the true mean on six rows).
Three ways out, in order of preference:
total_depthof0when a formula will read the block. Put the total somewhere else, in its own cell, where nothing can read it by accident.DROP(A2#,-1)to strip the last row where you need the body, andTAKE(A2#,-1)where you want the total on purpose.-1to move the total to the top, if what you are protecting is a human reader rather than a formula. It does not make the block safe to sum; it makes the total the thing a positional reference hits first.
Note that 2 and -2 add subtotal rows as well, one per outer group, so a two-field GROUPBY with total_depth of 2 contains three different kinds of row. Summing that column gives you the body, plus every subtotal, plus the grand total.
🎯 Scenario: =SUM(CHOOSECOLS(A2#,2))/SUM(source). If it comes back 2.00, your sum is eating the total row. If it comes back 3.00 or 4.00, you have subtotals in there too.
7) When Groups Appear and Disappear
A GROUPBY block grows and shrinks on its own, and two things happen when it grows.
It spills into whatever is below it. If anything is there — a note, a second table, last year's figures — the whole formula returns #SPILL! and the summary disappears. Not the new row: all of it. A report that was right yesterday is a single error cell today, which at least is loud.
Everything addressed below it is now addressing something else. This is the quiet one. A seventh depot opens, the block runs one row further, and every reference underneath it — including the cell where somebody parked the total, or a note, or a second summary — is now reading the summary's own output.
Two defences, both cheap:
=ROWS(Summary!A2#)-1 → 6 groups in the summary today
=COUNTA(UNIQUE(Freight[Depot])) → 6 distinct depots in the source
Keep those two side by side with a = between them. They disagree when a depot appears, when one stops trading, and when a key problem splits one depot into two — "Teesport" and "Teesport " are two groups, because grouping compares the text it is given and a trailing space is text. Leave a clear column and several clear rows under any spilled summary; the space costs nothing and #SPILL! costs a morning.
🎯 Scenario: Type a value in the cell directly under your summary's last row and watch the whole thing collapse to #SPILL!. Undo. That is how much room your report has: none.
8) PIVOTBY, and the Same Fault One Axis Over
PIVOTBY is GROUPBY with a second dimension — groups down the side, groups across the top:
=PIVOTBY(Freight[Depot],Freight[Month],Freight[Gross Margin],SUM,0,1,-2,1,1)
Row fields, column fields, values, function, then field_headers, row_total_depth, row_sort_order, col_total_depth, col_sort_order, and optionally filter_array and relative_to.
Everything in this article applies to it twice. The depots move down the side as margins change; the months move across the top as the year goes on, and a formula reading Summary!D2 is reading whichever month is third today. A new month inserts a column, a quiet month removes one, and the column total sits inside the block the same way the row total does.
The month axis adds a trap of its own. If the column field is text — "Jan", "Feb", "Mar" — the columns come back in alphabetical order: Apr, Aug, Dec, Feb, Jan, Jul, Jun, Mar, May, Nov, Oct, Sep. It looks like a bug in Excel and it is a property of your data: sorting text sorts text. Build the column field off a real date — a month-start column, =EOMONTH([@Date],-1)+1, formatted mmm-yy — and the columns come back in time order because they are now dates and dates have an order.
For reading a PIVOTBY from elsewhere, the advice gets simpler rather than more complicated: do not. A two-way read into a block that moves on both axes is a lookup against a header row and a label column, in one formula, re-evaluated every month. =SUMIFS(Freight[Gross Margin],Freight[Depot],$A2,Freight[Month],B$1) says the same thing with both keys written down, and nothing about it depends on the report's layout.
🎯 Scenario: Add a month to your source and look at what your PIVOTBY did to the columns to the right of it. Then look at anything that referenced those columns. That is your January.
9) What GROUPBY Does Not Do
Four behaviours that surprise people, and all four follow from the same fact: it is a formula over a range, not a view of a sheet.
- It ignores filters and hidden rows. Filter the source to one customer and the summary does not move. This is the opposite of
SUBTOTALandAGGREGATE, which exist precisely to follow the filter. If you want the summary filtered, say so in the formula — that is whatfilter_arrayis:=GROUPBY(Freight[Depot],Freight[Gross Margin],SUM,0,1,-2,(Freight[Service]="Next Day")). The array must be exactly as tall asrow_fields, or you get#VALUE!, and if it excludes everything you get#CALC!. - It does not group dates by month. A PivotTable offers to group dates into months, quarters and years;
GROUPBYgroups whatever values you hand it, so 31 different dates are 31 rows. Give it a month-start column from the source instead. - There is nothing to click. No drill-down to the rows behind a cell, no slicers, no Show Values As, no field list, no Refresh — because there is nothing to refresh. It recalculates like a formula, which is a genuine advantage over a stale PivotTable and no help at all when someone wants to double-click £96,450.00 and see the consignments.
- It is Microsoft 365 only.
GROUPBYandPIVOTBYarrived in 2024 for Microsoft 365 and Excel for the web. Open the workbook in Excel 2024, 2021 or 2019 and the formula is read as_xlfn.GROUPBYand evaluates to#NAME?— so the report is not merely stale on a colleague's machine, it is gone, and anything reading it is an error too. Paste-values a copy before it leaves the building, or send the PivotTable version.
🎯 Scenario: Filter your source table to a single customer with the summary visible on screen. If the numbers do not move, you have just proved the summary is reading the whole table — which is what you want, as long as the person reading the screen knows it.
10) GROUPBY, a PivotTable, or SUMIFS
| GROUPBY / PIVOTBY | PivotTable | SUMIFS / COUNTIFS | |
|---|---|---|---|
| Updates when data changes | Automatically | Only on Refresh | Automatically |
| Lives where you put it | One cell, spills | An object on a sheet | One cell per answer |
| Safe for another formula to read | No — rows move | No — layout moves | Yes — you wrote the keys |
| Drill-down, slicers, Show Values As | No | Yes | No |
| Groups dates into months | No, needs a helper column | Yes, built in | No, needs a helper column |
| Respects a filtered view | No, use filter_array | No, use a slicer or filter | No |
| Works outside Microsoft 365 | No — #NAME? | Yes, since the 1990s | Yes |
| Best at | A live summary on a dashboard | Exploring, and anything clickable | Feeding other formulas |
The honest summary is that GROUPBY replaces the PivotTable you rebuild every month and does not replace the PivotTable you explore with — and it never replaces SUMIFS for anything another formula consumes.
🎯 Scenario: For each summary in your main workbook, write down which column of this table you are actually in. The ones where "something else reads this" is true are the ones to change, whichever tool built them.
11) Six One-Cell Checks
=ROWS(Summary!A2#)-1 → 6 groups, excluding the total row
=COUNTA(UNIQUE(Freight[Depot])) → 6 distinct keys in the source
=SUM(Freight[Gross Margin])-SUM(CHOOSECOLS(DROP(Summary!A2#,-1),2))
→ 0.00 the body reconciles to the source
=SUM(CHOOSECOLS(Summary!A2#,2))/SUM(Freight[Gross Margin])
→ 2.00 means the total row is inside your sum
=SUMPRODUCT(--(A2:A7<>CHOOSECOLS(DROP(Summary!$A$2#,-1),1)))
→ 3 the order the sheet assumes is not the order it has
=TEXTJOIN(", ",1,CHOOSECOLS(DROP(Summary!A2#,-1),1)) → the running order, in one cell, visible in a diff
The third and fifth are the pair to keep. The third proves the arithmetic; the fifth proves the alignment; and Thornbury's August file passed the third and would have failed the fifth.
🎯 Scenario: Put all six in a block on the summary sheet with a label beside each, once, today. Every one of them is a single cell, and together they cover every fault in this article except the #NAME?.
12) Twelve Traps
- Reading a sorted summary by cell reference. The entire article. A position is not a key.
- Assuming "unsorted" means "stable". With no
sort_orderthe rows come back ascending by the group label, which still moves when a group is added, renamed or absent. - A group with no rows is simply missing. The summary lists what it found. Everything below the gap moves up.
- Summing a block that contains its own grand total. Exactly double, which looks like a units error rather than a structural one.
- Subtotals as well.
total_depthof2adds a subtotal row per outer group, and they are in the block too. #SPILL!from one cell in the way. The report does not grow by a row; it vanishes and leaves an error.- Growth overwriting what you put below it. Leave several clear rows under any spilled summary, and never park a total directly beneath one.
- Expecting it to follow a filter. It does not.
SUBTOTALandAGGREGATEdo;GROUPBYneedsfilter_array, and that array must match the height ofrow_fieldsexactly. - Text months. Apr, Aug, Dec. Group on a month-start date and format it, rather than grouping on the name of the month.
- Keys that differ by a space or a stray character.
"Teesport "is its own group, with its own row, taking its own margin with it. - Calling the function instead of naming it.
SUMis the aggregation;SUM()is a syntax error. Several at once go inHSTACK(SUM,PERCENTOF). - Sending the file outside Microsoft 365.
_xlfn.GROUPBYand#NAME?on Excel 2024, 2021 and 2019 — the summary and everything reading it, gone in one open.
What to Take Away
GROUPBY and PIVOTBY are the best thing to happen to routine reporting in Excel in years. One formula, no refresh, no object to maintain, a summary that is simply always current. The temptation that comes with them is the same one dynamic arrays brought with FILTER and SORT: the output looks like a table, it sits in cells with addresses, and addressing it is one click away.
It is not a table. It is a picture of the data taken from a particular angle, redrawn from scratch every time the data moves, and the angle is part of what you asked for. Thornbury asked for best-depot-first, got it, and then read row 3 as though "row 3" were a place rather than a ranking.
Three habits cover all of it. Keep summaries and feeds apart — GROUPBY for the eyes, SUMIFS for anything a formula will consume. When you must read a summary from elsewhere, read it by label, with XLOOKUP against CHOOSECOLS of the spill, so the lookup follows the row. And check the alignment, not just the total — one SUMPRODUCT counting the rows where two routes to the same number disagree, parked next to the output, would have read 3 on the eighth of September and saved everything that followed.