SCAN in Excel: Running Balances and Step-by-Step Results
Calculate running balances and ordered intermediate results with Excel SCAN, including starting balances, custom floors and text accumulation.
Verification status: See the evidence and test scope described in this guide. How formula examples are checked
Quick answer: SCAN returns every intermediate accumulator result, making it useful for running totals, balances and other calculations where each step depends on the previous step.
Availability and version history
- First introduced: Preview announced July 26, 2021, for Windows Beta 2108 (build 14312.20008) and Mac 16.52 (build 21072100). These are announced preview minima. Official introduction.
- Current documented status: the dedicated help lists Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel 2024, Excel 2024 for Mac. Availability depends on the installed build.
- Evidence checked: October 14, 2026. Official SCAN help. Examples below were independently calculated, not executed in Excel.
Syntax: a starting point, an input range and an update rule
=SCAN(initial_value,array,LAMBDA(accumulator,value,calculation))
The first argument is optional in the official syntax; the examples specify it deliberately. The LAMBDA has two named inputs followed by the expression that returns the next accumulated value. Microsoft documents an incorrect-parameters #VALUE! error for an invalid signature and recommends "" as the starting value for text. SCAN reference
For the one-column input used below, the output is a one-column spill with one result per source entry. The initial value itself is not an extra first output row.
Prepare an ordered transaction list
Enter this table in A1:B4:
| Event | Change |
|---|---|
| Receipt | 20 |
| Payment | -15 |
| Receipt | 30 |
Put an opening balance of 50 in D1. The order in B2:B4 represents transaction order; do not sort it by amount if the running balances need to reflect that sequence.
Example 1: calculate each closing balance
In D2 enter:
=SCAN($D$1,B2:B4,LAMBDA(balance,change,balance+change))
Expected output in D2:D4:
| Result cell | Closing balance |
|---|---|
| D2 | 70 |
| D3 | 55 |
| D4 | 85 |
The checks are 50+20=70, 70−15=55 and 55+30=85. The final balance also equals the opening balance plus the total change: 50+35=85.
The table labels above explain the output; SCAN itself returns only the three numeric cells. Leave D3:D4 free before entering the formula in D2.
Example 2: start from zero instead
=SCAN(0,B2:B4,LAMBDA(total,change,total+change))
Expected results are 20, 5, 35 down one column. These are cumulative changes, not account balances including the opening 50. A report should name the column accordingly.
By comparison, a reduction using the same addition rule would return only the final value of 35. Choose SCAN when users need to inspect how the final result was reached.
Advanced example: apply a floor after every event
On a separate sheet, put changes 40, -100 and 20 in B2:B4. With an opening value of 50, apply a zero floor at each step:
=SCAN(50,B2:B4,LAMBDA(balance,change,MAX(0,balance+change)))
Expected results: 90, 0, 20. The second event would take the balance below zero, so the rule resets that step to zero. The third event then starts from zero and ends at 20.
This is an illustrative rule for a counter or allowance, not a standard financial-accounting treatment. Applying a floor only once at the end gives MAX(0,50+40-100+20) = 10, a different result. The distinction matters whenever each step changes the state available to the next one.
Text example: see a code take shape
On another sheet, put A, B and C in A2:A4:
=SCAN("",A2:A4,LAMBDA(code,part,code&part))
Expected results are A, AB and ABC. This is a sequence of partial codes, not just one concatenated string. Using empty text as the explicit start makes the intended data type clear.
Keep the sequence reproducible
For these examples, use a one-dimensional range and a fixed order. If a live dataset has dates and times, establish a deterministic order before scanning it, including a tie-breaking rule when timestamps repeat.
If the spill is blocked, clear or move the obstructing cells rather than copying the formula into every output row. A source error may flow into later accumulator results; inspect the earliest failing step instead of replacing every error with zero.
Verification: Inputs are fictional. Expected values and array shapes were independently calculated from these inputs; no native Excel execution or screenshot is claimed. See the editorial testing standards.