Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to calculate a running balance in Excel and Google Sheets
Topic:Finance basicsExcel & Google Sheets
=IF(ROW()=2,C2,D1+C2)Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Set up columns: A = Date, B = Description, C = Amount (positive for credits, negative for debits), D = Balance.
- 2In D2 enter =C2 (first row starts at the first transaction amount or an opening balance).
- 3In D3 enter =D2+C3 and copy down — each row adds the current transaction to the previous balance.
- 4To handle both debits and credits separately, use columns for each and subtract: =D1+C2-B2.
Tips
- Do not use =SUM($C$2:C2) for large ledgers — it recalculates the entire sum on every row. Referencing the cell above (=D1+C2) is much faster.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
How do I start with an opening balance?
Put the opening balance in D2 directly as a value, then in D3 use =D2+C3 and copy down.
Can I have the running balance update automatically when I add a new row?
In an Excel Table (Ctrl+T), the formula in the Balance column will auto-fill to new rows. In Sheets, use ARRAYFORMULA to extend automatically.
More on Finance basics
See all →How-to
How to calculate compound interest
Calculate a compounded closing balance from principal, annual nominal rate, compounding frequency and years, then separate the interest earned.
How-toHow to calculate the effective annual interest rate
Convert a positive nominal annual rate and a whole-number compounding frequency into an effective annual rate with EFFECT.
How-toHow to calculate the break-even point in units
Divide fixed costs by unit contribution margin and round up to whole units, with an explicit message when the margin is nonpositive.
How-toHow to calculate tiered commission
Apply different commission rates by sales tier using IFS. Works in Excel 2019/365 and Google Sheets.
How-toHow to calculate net pay
Calculate take-home (net) pay by subtracting every deduction from gross salary, with worked examples for fixed amounts, percentage tax, and slab-based tax. Excel and Google Sheets.
How-toHow to calculate gross margin
Compute gross margin percentage as (price − cost) / price. Identical in Excel and Google Sheets.