# Bank Statement Running Balance Excel Formula, Explained

- Author: FastBankConvert (https://fastbankconvert.com)
- Published: 2026-10-17
- Category: Bank statements to Excel and CSV
- Canonical: https://fastbankconvert.com/blog/bank-statement-running-balance-excel

> Build a bank statement running balance in Excel: the formula, signed or split amounts, and how it shows the exact row where a conversion went wrong.
A bank statement running balance in Excel starts with the opening balance, then each row adds its own amount to the balance above: `=E1+C2` in the first transaction row, copied down. Put the statement's printed balance beside it and subtract. The first row where the difference stops being zero is where your converted data went wrong.

## The running balance formula for a signed amount column

A signed amount column holds deposits as positive numbers and withdrawals as negative ones. With that layout, the running balance needs one formula: the balance from the row above plus this row's amount. Put the opening balance in the row above the first transaction so the formula has a starting point.

### Setting up the sheet

Use this layout, with the statement's opening balance typed into row 1:

| Column | Holds |
| --- | --- |
| A | Date |
| B | Description |
| C | Amount, signed |
| D | Printed balance, copied from the statement |
| E | Computed running balance |
| F | Difference |

In E1 type the opening balance. In E2 type:

```
=E1+C2
```

Fill it down to the last row. Each cell now reads "the balance before this row, plus this row". In F2 type `=E2-D2` and fill down. A healthy sheet shows a column of zeros.

### Why not SUM from the top?

You can also write `=$E$1+SUM($C$2:C2)` in E2 and fill down. It gives the same result and has one advantage: deleting a row does not break the chain with a reference error. The drawback is speed on very long sheets, since every cell re-adds everything above it. For a year of personal statements, either form is fine. On tens of thousands of rows, the first form recalculates noticeably faster.

### Rounding

Money stored in Excel can carry tiny floating-point remainders, so a difference column may show 0.0000000001 instead of zero. Wrap the difference in ROUND, as `=ROUND(E2-D2,2)`, so only real gaps of a cent or more show up.

## Running balance with separate debit and credit columns

When the statement splits money in and money out into two columns, the running balance adds the credit and subtracts the debit on each row. Blank cells count as zero, so a row with only a withdrawal still works. The formula becomes the balance above, plus credit, minus debit.

With Debit in column C and Credit in column D, and the computed balance moved to column F, type this in F2:

```
=F1+D2-C2
```

Many statements label these columns differently. A UK statement may say Paid in and Paid out, and an Indian statement may say Cr and Dr. The guides to [UK bank statements in Excel](/blog/uk-bank-statement-to-excel) and [Indian bank statements with Dr and Cr columns](/blog/indian-bank-statement-to-excel) cover those labels and the number formats that come with them.

### When to merge the two columns first

If you plan to pivot, chart or import the data, merge the columns into one signed amount first with `=D2-C2`. One column is easier to sum and filter, and every running balance formula after that stays a simple addition.

## Why does my running balance drift from the statement?

A running balance drifts when a row in your sheet is missing, duplicated, misread or carries the wrong sign. Because each cell depends on the one above, the difference column jumps at the exact row where the problem starts and then stays at that new value. The size of the jump tells you what kind of mistake it is.

### A worked example

The six rows below are example data, invented for illustration. The opening balance is 800.00, and the conversion dropped one row.

| Row | Date | Description | Amount | Printed balance | Computed balance | Difference |
| --- | --- | --- | --- | --- | --- | --- |
| 1 | | Opening balance | | 800.00 | 800.00 | 0.00 |
| 2 | 06/01 | EXAMPLE SALARY | 1,950.00 | 2,750.00 | 2,750.00 | 0.00 |
| 3 | 06/02 | RENT PAYMENT | -1,100.00 | 1,650.00 | 1,650.00 | 0.00 |
| 4 | 06/04 | GROCERY | -73.80 | 1,543.70 | 1,576.20 | 32.50 |
| 5 | 06/05 | PHONE BILL | -45.00 | 1,498.70 | 1,531.20 | 32.50 |
| 6 | 06/07 | REFUND | 20.00 | 1,518.70 | 1,551.20 | 32.50 |

The difference first appears on row 4 and stays at 32.50. The computed balance is higher than the bank's, so the sheet is missing 32.50 of money out between rows 3 and 4. Looking at the statement around 06/03, you would find a 32.50 charge that was printed at the bottom of a page and lost in conversion.

### Reading the size of the gap

The pattern in the difference column usually points to one of four causes:

- **A steady gap equal to a row's amount**: a row is missing or duplicated. Higher than the bank means money out is missing or money in is doubled.
- **A steady gap of twice a row's amount**: that row has the wrong sign.
- **A gap that appears on one row and vanishes on the next**: the printed balance for that row was misread, not the amount.
- **A gap of 9, 90 or a similar multiple**: two digits were transposed, such as 54.00 read as 45.00. Differences divisible by nine are a classic sign of transposition.

The method is the same one a [bank statement converter applies when it checks balances](/blog/bank-statement-converter#does-a-bank-statement-converter-check-balances), done in the open where you can see each step.

## Same-day ordering and the running balance

Banks do not always print same-day transactions in the order they happened, and some apply deposits before withdrawals within a day. If your sheet sorts a day's rows differently from the statement, the running balance can fail on individual rows inside that day even though the day's closing figure matches.

### How to tell an ordering problem from a real error

An ordering problem has a recognizable shape. The difference column shows non-zero values only within one date, and returns to zero on the last row of that date. A real error carries forward to every later row. When you see a mid-day gap that heals by the end of the day, nothing is missing.

### Keeping the bank's order

Add a Sequence column numbered 1, 2, 3 in the order the rows appear on the statement, before you sort or filter anything. Sorting by Sequence restores the bank's order at any time. If the statement only prints one balance per day, compare at the last row of each date and ignore the rows in between.

## Running balance on text amounts, blanks and CR markers

The running balance formula needs every amount to be a real number. Converted statements often hold amounts as text, with currency symbols, thousands separators, trailing minus signs, CR and DR suffixes or brackets. Excel treats those cells as text, and the formula either ignores them or returns a #VALUE! error.

### Spotting text amounts

Select the amount column and look at the status bar. Right-click it to show both Count and Numerical Count. If Count is higher than Numerical Count, some cells in the column are text. Text values usually sit left-aligned while numbers sit right-aligned, which makes them easy to see once you look.

### Fixing them

A helper column can clean most formats. For an amount like `1,250.00CR` in C2, this returns 1250 as a positive number, and a `DR` suffix as a negative one:

```
=IF(RIGHT(C2,2)="DR",-1,1)*VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2,"CR",""),"DR",""),",",""))
```

Brackets such as `(45.00)` need one more SUBSTITUTE for each bracket and a test for the opening bracket. Once the helper column holds numbers, point the running balance at it instead of the raw column.

### Blank rows

A blank row between transactions does no harm to `=E1+C2`, since a blank counts as zero. A row that holds only a wrapped description, with no amount, is also harmless to the arithmetic but should still be joined to the row above so the sheet has one row per transaction.

## Is a running balance enough proof the conversion is complete?

A running balance that ties on every row proves that the amounts and their order are consistent with the bank's figures. It cannot prove that dates and descriptions were read correctly, and it says nothing when the statement prints no running balance at all, as most card statements do.

### What still slips past

Two errors survive a perfect running balance. A misread date leaves the money right but files the row in the wrong day or month. A misread description leaves the money right but can send the row to the wrong category. Spot-check a handful of dates against the PDF, and sort the description column to catch garbled text.

### Statements without a printed balance

When the statement gives only an opening and closing figure, you can still run the formula, but only the last row can be compared. A gap tells you something is wrong without saying where. Narrow it down by comparing your totals for each section, or each page, with any subtotals the statement prints.

### Working across devices

If you build the sheet on a phone and finish on a computer, check that the formulas survived the move. The guide on [sending an Excel file from a phone to a computer](/blog/send-excel-file-from-phone-to-computer) covers the routes that keep the file intact. FastBankConvert, which is getting ready to open, is built to tell you before you download if a statement's balances do not add up; [Get started](/get-started) to be told when it opens.