SCAN walks down a list one value at a time, carrying a running result with it, so a single formula can produce a whole running-balance column. Give it your opening balance, your column of signed amounts and a small LAMBDA that adds each amount to the balance so far. SCAN needs Microsoft 365; Excel 2021 and older do not have it.
What does the usual running balance look like?
Say dates are in A2:A500 and amounts are in B2:B500, with credits as positive numbers and debits as negative ones. Your opening balance sits in E1. The traditional approach puts this in C2 and fills it down:
=$E$1+SUM($B$2:B2)
That adds the opening balance to the sum of everything from row 2 down to the current row. It works in every version of Excel. The weakness is that it is a formula in every row. Insert a row in the middle and the new row has no formula until you copy one in, so the balance column has a gap or a wrong value.
The SCAN version
SCAN(initial_value, array, lambda) starts from the initial value and applies the LAMBDA to each item in turn. The LAMBDA gets two inputs: the running total so far and the next item. Type this once in C2:
=SCAN($E$1,$B$2:$B$500,LAMBDA(bal,amt,bal+amt))
The result spills down from C2 for 499 rows. Each cell is the balance after that row.
A worked example
Take an opening balance of ₹25,000 and four hypothetical rows.
| Row | Amount | Balance after |
|---|---|---|
| Opening | - | ₹25,000 |
| Groceries | -1,200 | ₹23,800 |
| Auto fare | -450 | ₹23,350 |
| Salary | +60,000 | ₹83,350 |
| Rent | -18,000 | ₹65,350 |
Check each step: 25,000 minus 1,200 is 23,800; minus 450 is 23,350; plus 60,000 is 83,350; minus 18,000 is 65,350. SCAN produces exactly the same column, from one formula.
What happens when I insert a row?
This is the practical gain. If you insert a row inside the range, between rows 2 and 500, Excel widens the references to include it, and the spilled result recalculates with the new row in its right place. There is no formula to remember to copy. The limit is at the edges: a row added below row 500 is outside the range, so extend it when your data grows, or convert the data to an Excel Table and refer to its columns.
How do I hide the empty rows?
Because the range runs to row 500, rows with no data would repeat the last balance. Wrap it so blank rows show nothing:
=LET(amt,$B$2:$B$500,run,SCAN($E$1,amt,LAMBDA(bal,x,bal+x)),IF(amt="","",run))
The IF checks each amount; where it is empty, the cell shows nothing, and where it has a value, it shows the running balance.
What if I am on an older Excel?
- Excel 2021 and earlier cannot calculate SCAN and show #NAME?.
- Use the fill-down formula above, which works in every version.
- If you share the file, send a copy with the balance column pasted as values.
Where does this approach fall short?
A running balance is a faithful record of your statement only when the data is complete and in date order. Sort the sheet by date first. Be careful with transfers between your own accounts, which will appear as a debit in one and a credit in another. If you keep several accounts in one sheet, you need one SCAN per account, each with its own opening balance.
When you are keeping this up across many accounts and months, and chasing missing rows each time, a tool built for importing statements can reduce the effort. Until then, SCAN gives you a tidy way to see money move through a single account.
A small checklist
- Enter an opening balance that matches the first statement.
- Use positive numbers for money in and negative for money out, consistently.
- Sort by date, oldest first.
- Type the SCAN formula once and leave the cells below it empty.
- Compare the final balance with the bank's closing balance.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


