Running total with an expanding SUM range

How it works, step by step

1. 01In C2, $B$2:B2 covers one cell, so the result equals B2.
2. 02$B$2 is absolute and stays fixed when you fill down. B2 is relative and moves with each row.
3. 03In C3 the range becomes $B$2:B3, and in C100 it becomes $B$2:B100, so each row adds everything above it plus itself.
4. 04If B2:B4 hold 500, -120, and 75, column C shows 500, 380, and 455.

The same logic in four tools

=SUM($B$2:B2)

=SUM($B$2:B2)

SELECT txn_date,
       amount,
       SUM(amount) OVER (
         ORDER BY txn_date, txn_id
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_balance
FROM cash
ORDER BY txn_date, txn_id;

import pandas as pd

cash = cash.sort_values(["txn_date", "txn_id"])
cash["running_balance"] = cash["amount"].cumsum()

Mistakes that break it

• !Inserting a new row above row 2 shifts the anchor to $B$3, so the new first row is left out of every total below it.
• !Each row re-adds everything above it, so 50,000 rows means over a billion cell reads and slow recalculation. On large sheets, use =C2+B3 in C3 and fill down, or =SCAN(0,B2:B50001,LAMBDA(total,x,total+x)) in Excel 365 or Google Sheets.
• !In SQL, ORDER BY txn_date alone uses the default RANGE frame, which gives every row on the same date the same total. Add a tiebreaker like txn_id and ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW for a row-by-row balance.
• !pandas cumsum() leaves NaN in the output for a missing amount and keeps adding from the next row. Fill missing amounts with 0 first if every row needs a balance.

Common questions

How do I make a running total in Excel?

Put =SUM($B$2:B2) in C2 and fill it down. The $ signs lock the start of the range while the end moves with each row.

What is the SQL equivalent of a running total?

A window function: SUM(amount) OVER (ORDER BY txn_date, txn_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). Add PARTITION BY account_id to restart the total for each account.

How do I calculate a cumulative sum in pandas?

Sort the DataFrame, then use df["amount"].cumsum(). For a total that restarts for each group, use df.groupby("account")["amount"].cumsum().