Excel Running Balance Formula: Step-by-Step Guide

Formulas & functions · Updated November 22, 2026

The simplest Excel formula for a running balance is =SUM($B$2:B2) for a cumulative total of deposits/withdrawals, or =C2+D2-E2 for a starting balance plus income minus expenses. The exact formula depends on your layout, but both are entered in the first data row and copied down.

Why a Running Balance Matters

A running balance (also called a cumulative or running total) shows the balance after each transaction, unlike a simple sum that gives one total. This is essential for checkbooks, bank reconciliations, inventory tracking, and project budgets because it lets you see the balance at any point in time. Excel makes this easy with relative and absolute references, but the wrong formula can produce errors when rows are inserted or filtered.

Basic Running Balance Formula (Cumulative Sum)

If you have a single column of numbers (e.g., deposits positive, withdrawals negative) and you want a running total, use the SUM function with a mixed reference. Assume your data starts in row 2, with amounts in column B (B2:B10). In cell C2, enter:

=SUM($B$2:B2)

Then copy this formula down to C10. The dollar sign locks the first cell, so as you copy down, the range expands: C2 sums B2, C3 sums B2:B3, C4 sums B2:B4, and so on. For example, if B2=100, B3=-25, B4=50, then C2=100, C3=75, C4=125. This works in Excel 365, Excel 2021, and older versions.

Running Balance with Starting Balance, Deposits, and Withdrawals

For a more realistic checkbook, you have a starting balance and separate columns for deposits and withdrawals. Assume:

  • A2 = starting balance (e.g., 1000)
  • B2 = deposit amount
  • C2 = withdrawal amount
  • D2 = running balance

In D2, enter:

=A2+B2-C2

In D3, enter:

=D2+B3-C3

Copy D3 down. The result: if A2=1000, B2=200, C2=50, D2=1150; then if B3=0, C3=100, D3=1050. This uses the previous balance, so it’s intuitive and easy to audit.

How to Choose the Right Formula

Pick the cumulative SUM approach when you have one column of signed numbers (positive for income, negative for expense). Use the previous-balance approach when you have separate columns for additions and subtractions, or when you have a starting balance that isn’t in the same column. The previous-balance method is easier to understand but fails if you filter rows, because the previous visible row may not be the previous balance. The SUM method is more robust for filtering, but it requires all amounts to be in one column.

Common Problems and Fixes

Problem: Formula shows 0 or wrong total after inserting a row

If you insert a row in the middle of your data, the formulas below may not extend automatically. To fix, select the new row and copy the formula from the row above, or use Excel Tables (Insert > Table) so formulas auto-fill. In a Table, the running balance formula in the first data row will automatically copy to new rows.

Problem: Filtering hides rows and breaks the running total

If you filter your data, the previous-balance formula (e.g., =D2+B3-C3) will still reference the hidden row, giving a wrong balance. The cumulative SUM formula =SUM($B$2:B3) also includes hidden rows. To sum only visible rows, use the SUBTOTAL function, but that gives a total, not a running balance. A true filtered running balance requires a more complex array formula or a pivot table.

Problem: Negative numbers for withdrawals

If you enter withdrawals as negative numbers (e.g., -50) and deposits as positive, then your running balance is simply =SUM($B$2:B2) where column B has all signed amounts. Don’t subtract a negative number again, or you’ll double-count.

Using Excel Tables for Automatic Running Balance

Convert your data range to an Excel Table (select the range, then Ctrl+T). Then in the first data row of a new column, enter a formula like =[@Amount]+SUM($B$2:[@Amount]) if you have a single Amount column, or use structured references. For a two-column deposit/withdrawal table, in the first row of the Balance column enter:

=[@Deposit]-[@Withdrawal]

In the second row, enter:

=OFFSET([@Balance],-1,0)+[@Deposit]-[@Withdrawal]

But OFFSET can be volatile; a simpler way is to use the previous cell reference: =D2+[@Deposit]-[@Withdrawal] (assuming D2 is the first balance cell). In a Table, this will auto-fill down. Note: In Excel for the web, Tables work the same, but keyboard shortcuts differ slightly (Ctrl+T works on Windows and Mac).

Running Balance with SUMIF for Categorized Totals

Sometimes you need a running balance for a specific category, like only “Groceries”. Use SUMIF with a mixed range. Assume column A has categories, column B has amounts, and you want a running total for “Groceries” in column C. In C2, enter:

=SUMIF($A$2:A2,"Groceries",$B$2:B2)

Copy down. This sums only the amounts where the category matches, expanding the range as you go. For example, if A2=Groceries, B2=50; A3=Gas, B3=30; A4=Groceries, B4=20, then C2=50, C3=50, C4=70.

Frequently Asked Questions

How do I make a running balance in Google Sheets?

Google Sheets uses the same formulas: =SUM($B$2:B2) or =D2+B3-C3. The main difference is that in Google Sheets, you can use array formulas like =ARRAYFORMULA(SUMIF(ROW(B2:B),"<="&ROW(B2:B),B2:B)) to fill the whole column at once, but the simple copy-down method works identically.

Why does my running balance not update when I change a value?

Excel recalculates automatically by default, but if calculation is set to Manual (Formulas > Calculation Options > Manual), you need to press F9 to recalculate. Also, if you entered the formula as text (starting with an apostrophe), it won’t calculate.

Can I use a running balance for a bank statement with dates?

Yes, just include the date column and sort by date. The formula doesn’t care about dates; it uses row order. If you sort after entering formulas, the running balance will recalculate based on the new order, which is usually what you want.

How do I handle a running balance with multiple currencies?

Excel doesn’t automatically convert currencies. You’ll need a separate column for the exchange rate and multiply the amount by the rate before adding to the balance. For example, if you have USD amounts in B and EUR rates in C, the running balance in EUR would be =SUM($B$2:B2*$C$2:C2) entered as an array formula (Ctrl+Shift+Enter in older versions, or just Enter in Excel 365).

What is the difference between a running total and a cumulative sum?

They are the same thing. A running total (or running balance) is a cumulative sum that updates with each new row. The terms are used interchangeably.

Does the formula work in Excel for Mac?

Yes, the formulas are identical. The only difference is keyboard shortcuts: to copy down, use Cmd+D on Mac instead of Ctrl+D on Windows. The SUM and SUMIF functions work the same.

Safety and Accuracy Tips

Always double-check your sign convention: decide whether withdrawals are negative or positive, and stick to it. If you mix signs, your balance will be wrong. Also, when copying formulas, ensure the absolute reference ($B$2) is correct; a missing dollar sign will cause the range to shift and produce incorrect totals. Finally, if you’re using this for financial records, verify a few rows manually to catch errors early.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.