Running Balance Formula in Excel for Bank Statements

TL;DR The simplest running balance in Excel is the opening balance in the first row and =previous balance + this amount in every row after it. In an Excel table, use a formula that sums from the first row to the current one so it survives sorting and inserted rows, or use SCAN in Excel 365 for a single spill formula. Compare your running balance with the balances printed on the statement: the first row where they differ is where a transaction is missing, duplicated or has the wrong sign.

A running balance is the most useful column you can add to converted bank statement data. It turns a list of transactions into something you can check against the statement line by line, it shows when an account dipped low, and it makes errors jump out. This guide covers several ways to build one in Excel, explains when each is appropriate, and shows how to use the result to track down problems.

Everything here also works in Google Sheets with minor changes, which we note where relevant.

Start with clean data

Running balances are only as good as the data underneath. Before adding one, make sure:

• Each transaction is one row. No wrapped descriptions spread over two rows, no subtotal lines.
• Dates are real dates, not text. If they won't sort properly, see our guide to fixing bank statement dates in Excel.
• Amounts are numbers with a single sign convention: money in positive, money out negative.
• The rows are in the order the statement lists them. That's usually date order, but within one day the statement's order matters if you want to compare against its printed balances.

If your statement has separate money-in and money-out columns, combine them into one signed column first:

=N(D2) - N(E2)

Our guide to separating debit and credit columns in Excel covers this in both directions.

If you're starting from a PDF, a converter like SheetStatement gives you this shape directly, with one row per transaction, ISO dates and a signed amount.

Method 1: the classic previous-plus-amount formula

Suppose your data looks like this, with the opening balance from the statement summary in F1:

In D2:

=$F$1 + C2

In D3, then filled down:

=D2 + C3

This is quick and easy to understand. Its weakness is fragility. If you sort the data, insert a row or delete one, the references can break or point to the wrong row, and you won't always notice.

Use it for a one-off check on a single statement.

Method 2: sum from the top

A sturdier version sums every amount from the first row down to the current one:

=$F$1 + SUM($C$2:C2)

Filled down, the range grows by one row each time: $C$2:C3, $C$2:C4, and so on. Inserting or deleting rows inside the range doesn't break it, because each row recalculates from the top.

The trade-off is speed. On a few thousand rows it's fine. On tens of thousands, the repeated sums can slow a workbook down noticeably.

Method 3: inside an Excel table

If your data is formatted as an Excel table (Insert, Table), you get automatic fill-down and structured references. A running balance that survives sorting the table by date:

=Opening + SUM(INDEX([Amount], 1):[@Amount])

INDEX([Amount], 1) is the first cell of the Amount column, and [@Amount] is the current row, so the range runs from the top to here. Opening is a named cell holding the opening balance.

Tables are the best choice for a workbook you'll add to every month. When you paste the next statement's rows at the bottom, the formula extends automatically.

One caution: if you sort the table by something other than the statement order, such as by amount, the running balance will be recalculated in that order and stop meaning anything. Sort back by date (and by a sequence column, below) before reading it.

Method 4: SCAN in Excel 365

Excel 365 has a function designed for exactly this. One formula produces the entire column:

=SCAN(Opening, C2:C200, LAMBDA(bal, amt, bal + amt))

SCAN starts with the opening balance and walks down the amounts, returning the accumulated value at each step. The results spill down the column.

Advantages: one formula to maintain, fast, and nothing to fill down. Disadvantages: it needs a current version of Excel, and the spill range can't overlap other data. If you point it at a table column, such as Tx[Amount], it grows with the table.

Google Sheets also has SCAN with the same pattern, so this formula carries over almost unchanged.

Keep the statement's order with a sequence column

Statements often list several transactions on the same day, and the printed balance after each one depends on the order. If you sort by date only, same-day rows can shuffle and your running balance won't match the printed one even though the totals are right.

Add a Seq column before doing anything else:

=ROW() - 1

then convert it to values (copy, paste values) so it doesn't change. Now you can always restore the original order by sorting on Date, then Seq.

Running balances for several accounts in one table

If you combine checking, savings and a credit card into one table with an Account column, a single running balance across all of them is meaningless. You want one per account.

In a table with Account, Amount and Seq columns, and opening balances in a small lookup table:

=XLOOKUP([@Account], Openings[Account], Openings[Balance])
 + SUMIFS([Amount], [Account], [@Account], [Seq], "<=" & [@Seq])

Each row's balance is that account's opening balance plus all of that account's amounts up to and including this row. It works whatever order the table is sorted in, as long as Seq reflects the original statement order within each account.

On large tables SUMIFS with a growing condition gets slow. If that becomes a problem, use a separate sheet per account with Method 3 or 4.

Credit card running balances

On a credit card, the balance is what you owe. If your data uses the statement's view, where purchases are positive and payments negative, the running balance works exactly the same way, starting from the previous balance:

=SCAN(PreviousBalance, Card[Amount], LAMBDA(bal, amt, bal + amt))

The last value should equal the statement's new balance. If you've flipped card signs for a budget view (spending negative everywhere), flip the running balance logic too, or keep a separate statement-view amount column for checking.

Using the running balance to find errors

This is where a running balance really pays off. Most checking statements print either a balance after each transaction or a daily balance summary. Compare yours with the bank's.

Row-by-row comparison

If the statement prints a balance on every line, bring it into a Stmt balance column during conversion and add:

=ROUND([@Balance] - [@[Stmt balance]], 2)

Filter that column for non-zero values. The first non-zero row is where the problem starts. Everything after it is off by the same amount, which tells you the size of the error.

Daily balance comparison

If the statement only prints end-of-day balances, find your last running balance for each date:

=XLOOKUP(G2, Tx[Date], Tx[Balance], , 0, -1)

The final -1 searches from the bottom, so you get the last row for that date. Put the statement's daily balances next to these and compare. The first date that doesn't match contains the problem.

Reading the difference

Our guide to finding bank reconciliation discrepancies has more on reading differences, and the free balance checker gives you a quick yes or no on the totals.

A worked example

Someone converts a May checking statement and the ending balance is off by 84.12.

1. Add a Seq column and convert it to values.
2. Add a running balance with SCAN, starting from the opening balance.
3. Bring in the daily balances from the statement's summary.
4. Compare by date. Everything matches through 1 May. On 2 May the running balance is 84.12 higher than the statement.
5. Look at 2 May. The statement lists two grocery purchases that day, 84.12 and 23.40. The converted data only has the 23.40. The 84.12 row had wrapped onto two lines in a manual paste and been deleted as a "blank" row.
6. Add it back. The difference drops to zero on every day through month-end.

Without a running balance, finding one missing grocery purchase in a month of transactions would mean checking every row by hand.

Get the opening balance right

Every method above depends on one number: the balance you start from. Get it wrong and every row after it is wrong by the same amount, even though the transactions are perfect.

Where to find it. On a checking or savings statement it's usually labeled beginning balance, opening balance, previous balance or balance brought forward, typically in the summary block on the first page. On a credit card statement it's the previous balance.

Store it once. Put it in a single named cell, such as Opening, and refer to that everywhere. Typing it into several formulas invites one copy being out of date.

Check continuity across months. When you combine several statements, each month's opening balance should equal the previous month's closing balance. A small control table catches gaps:

The Continuous column is just =ROUND(C2 - D2, 2) = 0. A "No" means a statement is missing, the periods overlap, or one of the balances was copied incorrectly. Our guide to an opening balance that doesn't match the statement works through each case.

Multi-month running balance. If the continuity table is clean, you only need the very first opening balance. A running balance across a whole year starting from January's opening should land exactly on each month's closing balance at the last row of that month. That's a powerful check: twelve statements verified with one column.

Mistakes that break running balances

Including subtotal or summary rows. Lines like "Total deposits" or "Balance forward" aren't transactions. If they're in the Amount column, the running balance jumps by their value. Filter them out before adding the formula.

Mixing sign conventions. If some rows have withdrawals as positive numbers, perhaps because two statements were converted differently, the balance drifts. Check that every withdrawal is negative before combining.

Pending transactions from a download. Online downloads can include pending items that never appear on the statement, or appear later with a different amount. Remove them when comparing against statement balances.

Hidden rows. Filtered or hidden rows are still included in SUM and SCAN. That's usually what you want, but be aware of it if you've hidden rows to exclude them; delete them or flag them instead.

Rounding. Amounts that came from a calculation, such as currency conversion, can carry tiny fractions. Wrap comparisons in ROUND(..., 2) so 0.0000001 differences don't show as errors.

Beyond error-checking

Once the running balance matches, it's useful for more than reconciliation.

Lowest balance in a month. For cash flow planning or a loan application:

=MINIFS(Tx[Balance], Tx[Month], "2026-05")

Days below a threshold. If you want to know how often the account dropped under a buffer amount:

=COUNTIFS(Daily[Balance], "<500")

on a sheet with one row per day.

A balance chart. A line chart of Date against Balance shows the pattern of the month: the payday jump, the rent drop, and how close you got to zero. It's often more persuasive than a table when planning a budget.

Average daily balance. Some lenders and some account terms refer to average balances. Build a one-row-per-day sheet with the end-of-day balance, filling forward on days without transactions, and take the average. Treat any calculation like this as a working estimate; the bank's figure is the official one.

Our guide to building a cash flow statement from bank statements takes these ideas further.

Google Sheets notes

• Methods 1 and 2 work unchanged.
• Sheets doesn't have Excel tables in the same form, so use Method 2 or SCAN.
• SCAN and LAMBDA work as in Excel 365.
• XLOOKUP and MINIFS exist in Sheets with the same arguments.

See our guide to reconciling a bank statement in Google Sheets for a full Sheets template.

Get clean data to start with

Upload a statement to SheetStatement and you'll get one row per transaction, real dates and signed amounts, with a balance check already done. The free plan covers 3 pages a month, and paid plans start at $9 a month.

FAQ

What's the simplest running balance formula in Excel?

Put the opening balance plus the first amount in the first row, then use =previous balance + this amount in every row below. It's fine for one-off checks.

Which method should I use for a workbook I update monthly?

An Excel table with =Opening + SUM(INDEX([Amount],1):[@Amount]), or SCAN if you have Excel 365. Both extend automatically when you add rows.

Why doesn't my running balance match the statement even though the total does?

Usually because same-day transactions are in a different order. Add a sequence column that preserves the statement's order and sort by Date, then Seq.

How do I keep separate running balances for several accounts?

Use SUMIFS on the Account column with a sequence condition, plus each account's opening balance, or keep each account on its own sheet.

Does this work for credit cards?

Yes. Start from the previous balance with purchases positive and payments negative. The last value should equal the new balance.

My running balance breaks when I sort. Why?

The simple previous-row formula depends on row order. Use the table or SCAN method, and always sort back by Date and Seq before reading the balance.

Can I do this in Google Sheets?

Yes. The simple formulas and SCAN both work in Sheets.

Where do I get the opening balance?

From the statement's summary block: the beginning, opening or previous balance for the period. Store it in one named cell and refer to it from your formula.

Can one running balance check a whole year of statements?

Yes, if each month's opening balance equals the previous month's closing balance. Start from January's opening and the balance at the last row of each month should equal that month's closing balance.

Why does my balance jump by a large amount on one row?

Usually a subtotal or summary line, such as total deposits, was included as if it were a transaction. Remove it and the jump disappears.

What if the amounts are text?

Convert them first with NUMBERVALUE or by multiplying by 1, otherwise the running balance will ignore them or return errors.