Build a running total down a column
Fill one SUM formula down a column and have each row total everything above it, using an anchored starting cell instead of rewriting the range.
Last updated
Fill in the blanks
Assembled in your browser — nothing you type is stored or sent anywhere.
Your formula
Works in both Excel and Google Sheets unless the fine print below says otherwise.
Why it's built this way
- →The $ signs on start_cell lock both the column and row of that reference, so it never moves when the formula is filled down; current_cell has no $ signs, so it updates to match whatever row the formula lands on.
- →That mix — one end absolute, one end relative — is the entire trick: row 5's formula sums $B$2:B5, row 6's sums $B$2:B6, and each one automatically includes everything above it plus itself.
- →It's a single formula written once and filled down, rather than a different manually typed range on every row.
The fine print
- →Forgetting the $ signs on start_cell is the classic mistake: both ends of the range then shift together when filled down, so every row sums a fixed-size window instead of an expanding one — a running total that silently isn't.
- →Inserting or deleting a row above the anchored cell shifts what $B$2 points to, along with every formula built on it — worth a spot-check after any structural edit to the sheet.
- →Works identically in Excel and Google Sheets, $ anchoring included.
Part by part
- =SUM(
- An ordinary sum. The running behaviour comes entirely from how the range is anchored, not from any special function.
- {{start_cell}}
- The first row of the column, written with dollar signs so it stays fixed as the formula is filled down. That anchoring is the whole mechanism.
- :{{current_cell}}
- The end of the range, left relative so it moves with each row. Fixed start plus moving end is what makes the range grow.
Common mistakes
=SUM(B2:B2)Without dollar signs on the first reference, filling down produces SUM(B3:B3), SUM(B4:B4) and so on — each row shows its own value rather than a cumulative one, which looks like the formula did nothing.
=SUM($B$2:B2)=SUM($B$2:$B$2)Anchoring both ends freezes the range entirely, so every row returns the first value. The end reference must stay relative for the range to extend.
=C1+B2Adding the previous total works and breaks silently if a row is deleted or the column is sorted, because the chain of references is broken at that point. The SUM form recomputes from the start every time.
Questions
Why does it slow down on large sheets?
Each row sums a progressively longer range, so the work grows quadratically with row count. Past a few thousand rows the previous-total form is dramatically faster, at the cost of being fragile to sorting.
How do I restart the total for each group?
Use SUMIFS with the group as a criterion and the same anchored-range trick on the date or row column. That keeps each group independent without needing separate blocks of formulas.
Is there a modern alternative?
SCAN with LAMBDA in current Excel produces the whole column as one spilled array, which is both faster and easier to audit. Sheets can do the same with a single ARRAYFORMULA over SUMIF.
What happens if a cell in the range is text?
SUM ignores it rather than erroring, so a stray label mid-column silently reduces the total. That is convenient and it is also why a running total can quietly disagree with a manual check.