Running totals have a long history of being more annoying than they should be.
The classic trick is =SUM($B$2:B2) in C2, dragged down the column. It works, until someone inserts a row in the middle, at which point the formula in C5 still says $B$2:B5 but now there's a new row below the rows it's meant to cover and the totals are off. Or you move the running total into Power Query, which means leaving the workbook to do it. Or you write an array formula with SUMPRODUCT and a clever offset, which works but reads like a riddle.
SCAN replaces all of this with a single formula. It's not only about running totals - it's about any cumulative calculation you'd otherwise have to drag, paste, or query.
The mental model
SCAN is a reducer that keeps its working.
A reducer takes an array and condenses it to one value. SUM is a reducer: feed it 1, 2, 3, 4 and you get 10. The intermediate steps - 1, 3, 6, 10 - are thrown away.
SCAN does the same accumulation, but instead of throwing the intermediates away, it returns them. Feed it 1, 2, 3, 4 with a "+ rule" and you get back the array {1, 3, 6, 10}. The final value is the same number SUM would have returned. The intermediates are the running total.
This is why "running total" is the most obvious application but not the only one. Any time you want to track how a value evolves across a sequence - balance over transactions, cumulative percentage, score over rounds, inventory level over days - SCAN is the right tool. The rule changes; the structure doesn't.
The syntax
SCAN(initial_value, array, lambda(accumulator, current_value))Three things:
- The initial value. What the accumulator starts at. For a running total this is 0. For a running product it's 1. For a balance it might be your opening balance.
- The array. What you're iterating over. A range, a table column, or a dynamic array.
- The LAMBDA. The rule for combining the running result so far (the accumulator) with the next value in the array. The LAMBDA always takes two arguments in that order.
A running total over A2:A100:
=SCAN(0, A2:A100, LAMBDA(acc, val, acc + val))A running product:
=SCAN(1, A2:A100, LAMBDA(acc, val, acc * val))A running maximum:
=SCAN(-1E+300, A2:A100, LAMBDA(acc, val, MAX(acc, val)))Same shape, different LAMBDA.
A real example: a bank balance
You've got a transaction log: column A is dates, column B is amounts (positive for credits, negative for debits). You want a running balance in column C, starting from an opening balance of $1,250.
=SCAN(1250, B2:B100, LAMBDA(acc, val, acc + val))C2 shows $1,250 + first transaction. C3 shows that plus the next. And so on, down the column.
If the source data is in a structured table named Transactions:
=SCAN(1250, Transactions[Amount], LAMBDA(acc, val, acc + val))Add a row to the table, and the SCAN result extends automatically. This is the bit that earns SCAN its keep: the column self-maintains.
What SCAN doesn't do
The spill is a single block of cells. It's not a per-row column inside the table. If you sort or filter the source table, the spilled SCAN doesn't reshuffle to match - because it's not part of the table. It's a separate spilled range that happens to sit next to the table.
If you need the running balance to behave as a true per-row table column (so sorting moves it, filtering hides it, and so on), SCAN isn't the right tool. You want a Power Query custom column with a List.Accumulate pattern, or a Power Pivot measure with the appropriate iterator. SCAN is for the worksheet, not the data model.
It also doesn't handle complex multi-input accumulations natively. If your accumulator needs to track multiple things at once - say, balance and days since last activity - you have to pack them into a single value (a delimited string, or a chosen-rank trick) and unpack inside the LAMBDA. At that point you're usually better off in Power Query.
Where it changes how you work
Three patterns become trivial once SCAN is in your toolkit.
- Cumulative percentages. Running total of a column, divided by the grand total, spilled down. One formula. No "drag this down to the bottom of your data."
- Threshold tracking. "Find the first row where the cumulative total exceeds X." A SCAN feeds into a MATCH, and the answer falls out.
- Order-dependent calculations. Anything where the answer at row N depends on the answer at row N-1. Reorder points, replenishment schedules, debt amortisation. The accumulator pattern fits naturally.
Once you've used SCAN for any of these, the muscle memory of "drag the formula down" feels archaic. The formula already covers the column. The column updates itself.
For a sub-three-minute version of this idea applied to one specific case, see Click Bait: 3-Minute Win (May 9). For an end-to-end build using SCAN with a structured table that extends itself, see Build It: Rolling 12-Month Balance Tracker (May 12). And for the bigger argument about why LAMBDA-based functions like SCAN justify more formula complexity than the old rules allowed, see Hot Take (May 13).
Frequently Asked Questions
What does SCAN do in Excel?
SCAN applies a function cumulatively across an array, returning every intermediate accumulation. Where a reducer like SUM returns only the final value, SCAN returns the full sequence of partial results, which is exactly what a running total is.
Is SCAN available in all versions of Excel?
SCAN is available in Excel for Microsoft 365 and Excel for the web. It's not available in Excel 2019, 2021, or earlier perpetual licences. Workbooks using SCAN won't recalculate properly in those versions.
What's the difference between SCAN and REDUCE?
REDUCE collapses an array to a single value, the accumulated result. SCAN does the same accumulation but keeps every intermediate step, returning an array the same length as the input. Use REDUCE when you only want the final answer; use SCAN when you want the running result at every step.
Can SCAN replace helper columns for running totals?
Yes. A single SCAN formula at the top of a column produces the entire running total as a spilled array, and if you point it at a structured table column it extends automatically as rows are added. The helper-column pattern is no longer necessary for cumulative calculations on the worksheet.
Does SCAN work with text or only numbers?
SCAN works with any data type the LAMBDA can handle. You can use it to concatenate text incrementally - LAMBDA(acc, val, acc & ", " & val) - or to combine values in any other way the LAMBDA defines. The accumulator type only needs to match what the LAMBDA returns.
