I used SUM to solve this problem for years until I found out about SCAN.

Here’s the official definition of SCAN:

I use a running total column in my most popular spreadsheet, which is a personal finance app.

You need a running balance that updates automatically as new rows appear. I've always just had a balance column with a formula that I copy all the way down.

It works, but it's not very elegant. And there's a better way: to write one formula in the top cell. A little help from ARRAYFORMULA and you don’t have to copy anything else down.

SCAN walks through an array and accumulates a result, step by step, like a running total on autopilot. It takes three arguments: a starting value, an array to process, and a LAMBDA that defines what happens at each step.

Don't be afraid of LAMBDA. I was for a long time, but it's very powerful and easy to use once you wrap your head around it.

It's basically just a way to define a formula inside of a formula.

Here's the formula that replaces an entire column of manual calculations:

Here’s how it works 👇

IF(Table1[amount], ..., "")
The outer wrapper keeps empty rows clean. No amount? No balance displayed.

IF(Table1[category]="income", Table1[amount], -Table1[amount])
This is your transformation layer. Income stays positive. Everything else flips negative. You're converting a raw transaction log into a signed array before SCAN ever touches it.

SCAN(G1, ..., LAMBDA(a, x, a+x))
G1 is your starting balance (maybe zero, maybe last month's closing balance). SCAN then walks through each signed amount, adding it to the accumulator. The a is the running total so far. The x is the current value.

start + SCAN(...)
Offsets everything by your starting value, so your running balance begins exactly where you need it.

One formula, infinite rows. Because it's wrapped in ARRAYFORMULA, it spills results down automatically. New transaction? The balance updates. No dragging required.

Nothing to break. Delete a row and the formula recalculates. Insert a row and it adapts. There's no chain of cell references waiting to snap.

Readable logic. I've been switching to ARRAYFORMULA in all of my spreadsheets lately so that I can have single points of failure instead of tons of different formulas littered throughout each sheet.

Running balances are the obvious use case, but SCAN is quietly powerful anywhere you need cumulative logic:

The pattern is always the same: starting value → array → LAMBDA that defines the accumulation rule.