Excel has no DO WHILE function. But if you’re copying down formulas that depend on prior-row results — like running totals, cumulative flags, or inventory adjustments — you’re probably wrestling with circular references or bloated VBA.
The Problem
You’ve got a list of transactions in column A (dates), B (amounts), and C (running balance). You want cell C2 to start at $10,000, then C3 = C2 + B3, C4 = C3 + B4, and so on. Simple — until you try to write it as a single dynamic array formula. That’s where the 'Do While' fantasy crashes hard.
| Date | Amount | Balance (Broken) |
|---|---|---|
| 2024-02-01 | $1,200 | #REF! (C1+C2) |
| 2024-02-03 | −$450 | #VALUE! (circular) |
| 2024-02-05 | $2,100 | #SPILL! (array mismatch) |
| 2024-02-07 | −$890 | #N/A (OFFSET not dynamic) |
| 2024-02-10 | $3,400 | #CALC! (SCAN misused) |
We’ve all done this: pasted C2 = 10000, then C3 = C2+B3, dragged down — only to find it breaks when new rows are inserted above row 2. Or worse: enabled iterative calculation (File > Options > Formulas > 'Enable iterative calculation') and watched Excel silently return wrong numbers after 100+ iterations. Trust me, I learned this the hard way debugging a payroll sheet where one employee’s bonus was off by $14,722 because Excel stopped calculating after 100 passes — and nobody noticed for three months.
The Solution
Use SCAN() with an initial value. It’s Excel’s closest functional equivalent to a 'Do While' loop — but without loops. It processes arrays left-to-right, carrying forward a result. No VBA. No iteration settings. Just clean, predictable math.
- In cell C2, type:
=SCAN(10000,B2:B11,LAMBDA(a,b,a+b)) - Press Enter. Excel spills the full running balance down column C.
- To make it responsive to new rows, convert your data range to a table (Ctrl+T), then update the formula to
=SCAN(10000,Table1[Amount],LAMBDA(a,b,a+b)). - If your starting balance is in cell D1, reference it directly:
=SCAN(D1,B2:B11,LAMBDA(a,b,a+b)).
This works because SCAN() doesn’t ‘loop’ — it recursively applies the LAMBDA to each element, using the prior output as the next input. Think of it as folding a list, not looping over it.
| Date | Amount | Balance (Fixed) |
|---|---|---|
| 2024-02-01 | $1,200 | $11,200 |
| 2024-02-03 | −$450 | $10,750 |
| 2024-02-05 | $2,100 | $12,850 |
| 2024-02-07 | −$890 | $11,960 |
| 2024-02-10 | $3,400 | $15,360 |
| 2024-02-12 | $0 | $15,360 |
| 2024-02-14 | −$1,950 | $13,410 |
Pro tip: If you need conditional logic — say, 'add amount only if date is after 2024-02-05' — wrap the second argument in a FILTER: =SCAN(10000,FILTER(B2:B11,A2:A11>"2024-02-05"),LAMBDA(a,b,a+b)). That’s safer than nesting IF inside SCAN, which can break spill behavior.
Going Further
You can simulate more complex 'Do While'-like logic using combinations:
- Inventory roll-forward: Use
REDUCE()to process batches where each step depends on both prior state AND external conditions (e.g., stock levels, reorder thresholds). - Flag first occurrence per group:
=SCAN(0,Table1[Product],LAMBDA(a,b,IF(b=OFFSET(b,-1,0),a,a+1)))— though OFFSET is volatile; better useINDEX(Table1[Product],ROW()-ROW(Table1[#Headers]))for static referencing. - Dynamic text accumulation:
=SCAN("",A2:A10,LAMBDA(a,b,IF(b="",a,a&", "&b)))builds comma-separated lists without CONCATENATE() drag-down hell. - For true iteration (e.g., Newton-Raphson root finding), use
LET()with recursive naming — but limit depth to avoid #NUM! errors. Example:=LET(x,0.5,fx,LAMBDA(y,SIN(y)-y/2),next,LAMBDA(z,z-(fx(z))/(COS(z)-0.5)),next(next(next(x)))).
Surprising tip: SCAN() ignores blank cells *in the array being scanned* — but not blanks in the accumulator logic. So if column B has empty cells, use FILTER(B2:B11,B2:B11<>"") first. Otherwise, you’ll get repeated values.
When NOT to Use This
Don’t reach for SCAN when:
- You need real-time, cell-by-cell recalculation (e.g., live sensor feeds updating every second) — Excel formulas aren’t event-driven.
- Your dataset exceeds ~50,000 rows — SCAN performance degrades sharply beyond that, especially with nested LAMBDAs.
- You’re supporting users on Excel 365 versions before Build 16.0.14326 — SCAN and REDUCE were added mid-2021 and aren’t in LTSC or older perpetual licenses.
- The logic requires breaking out of iteration based on a condition (e.g., “stop when balance < $5,000”) — SCAN always processes the full array. For that, you’ll need VBA or Power Query.
Also: never combine SCAN with volatile functions like NOW(), RAND(), or INDIRECT() inside the LAMBDA — it forces full recalculation on every edit, even unrelated ones.
Keyboard Shortcuts
| Action | Shortcut | Notes |
|---|---|---|
| Open Name Manager | Ctrl + F3 | Useful for checking defined LAMBDA names |
| Toggle Formula View | Ctrl + ` | See all formulas at once — critical for debugging SCAN chains |
| Edit Active Cell | F2 | Essential when adjusting LAMBDA parameters inline |
| Evaluate Formula Step-by-Step | Alt + M + V | Shows how SCAN accumulates — press Enter to advance each step |
