The pattern is two years past its expiry date
The Excel running-total pattern most analysts learned - one helper column for the previous row's total, one for the current value, one to hold the sum - has been obsolete since SCAN reached general availability in late 2023. If your workbook still leans on it, you're maintaining a workaround for a problem Microsoft already fixed.
Why people still reach for helper columns
Helper columns are visible. You can click into row 47 and see exactly what's being added to what. That's a real virtue, especially in shared workbooks where someone else might need to debug your logic at 4pm on a Friday. The pattern is also what most Excel courses still teach, because the curricula are five years behind the product. So when someone asks "how do I calculate a running total", the muscle-memory answer is still =B2+C1, dragged down.
That's the charitable explanation. The uncharitable one is that helper columns feel like work, and work feels like value, and a single dynamic-array formula in one cell doesn't feel like enough.
Where the fragility actually lives
Three things go wrong every time:
- Insert a row, break the chain.
=B2+C1assumes the row immediately above contains the previous total. Insert a missing entry and you've torn the link. The new row shows the wrong cumulative figure, every row below is now off by the same delta, and nobody notices until someone reconciles against the source. - Sort the data, destroy the model. Running totals depend on row order. The moment someone sorts by amount, by date in reverse, by anything other than the order the formula was written for, every cumulative value becomes nonsense. The numbers still calculate. They just calculate the wrong thing.
- Three columns to maintain one figure. Two of them exist only to feed the third. They sit there forever, taking up space, getting copy-pasted into pivot sources where they don't belong, being referenced by other formulas that then also need patching every time the structure shifts.
None of these are edge cases. They are what happens every week in workbooks that have helper-column running totals in them.
The modern equivalent
One cell. One formula.
=SCAN(0, B2:B100, LAMBDA(a,b, a+b))
That returns the entire running total as a spilled array. Insert a row inside the range and the array expands. Sort the source and you can re-anchor the formula in seconds instead of rewiring three columns.
The mental model takes about ten minutes to settle. SCAN walks the array, carries a running accumulator, and returns every intermediate state - which is exactly what a running total is. Once that lands, the helper-column version starts looking like the long way around.
Frequently Asked Questions
Does SCAN work in older versions of Excel?
SCAN requires Microsoft 365 or Excel 2024. If your audience is still on Excel 2019, the helper-column pattern is what you have. Everyone else has had the better option for two years.
Is SCAN slower than helper columns on large datasets?
Usually faster. Dynamic-array functions evaluate once and spill; helper columns recalculate per cell. The gap widens as the range grows.
What about running totals that need to reset by group, like a per-customer cumulative balance?
SCAN handles that too, with a small adjustment to the LAMBDA. The Build It post on 12 May walks through exactly that case.
Should I rewrite every existing helper-column running total?
No. Rewrite them when you touch them. Leaving working models alone is fine. The point is to stop creating new ones.
Helper columns aren't wrong. They're just expensive insurance against a problem Excel stopped having two years ago.