# Bank Statement Excel Template for Converted Statements

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

> A bank statement Excel template for converted statements: the columns, a running balance, a summary block, and how to paste new months in.
A good bank statement Excel template for converted statements has three parts: a transaction table with a fixed set of columns, a running balance column that compares itself with the balances the bank printed, and a summary block that totals each month and shows whether it ties. New months are pasted below the last row, never between rows.

## Columns in a bank statement Excel template

The template needs columns for a sequence number, the statement month, the date, the description, a signed amount, the printed balance, your running balance, a check, a category and notes. Keep them in that order and in the same place every month, so formulas and filters never need to move.

### The column list

The layout below is a suggestion. Column letters matter only because the formulas in this post refer to them.

| Column | Name | Format | Filled by |
| --- | --- | --- | --- |
| A | Seq | Whole number | Typed or filled down: 1, 2, 3 in statement order |
| B | Month | Text, such as 2026-09 | Typed once per pasted month |
| C | Date | Date, yyyy-mm-dd | Converted statement |
| D | Description | Text | Converted statement, unchanged |
| E | Amount | Number, 2 decimals | Converted statement, money out negative |
| F | Printed balance | Number, 2 decimals | Converted statement, blank where the bank printed none |
| G | Running balance | Number, 2 decimals | Formula |
| H | Check | Number, 2 decimals | Formula |
| I | Category | Text | You, or a lookup |
| J | Notes | Text | You |

### Why a sequence column

Seq records the order the lines appeared on the statement. A running balance depends on that order. If you sort by description or amount to look for something, the balance column will look broken until you sort back by Seq. With the column there, getting back is one click. Without it, lines that share a date can come back in a different order, and the balance check fails for no real reason.

### Why a month column

The Month column lets you stack every statement in one table and still total or check one statement at a time. It is also the column the summary block uses to pick out each month's rows.

### Formats to set before the first paste

Set the Date column to a date format and the Amount, balance and check columns to numbers with two decimals. Set Description, Category and Notes to text. Doing this first stops Excel from guessing, which is when long reference numbers turn into scientific notation and day-first dates turn into the wrong month.

## Running balance formula for the bank statement template

The running balance starts from the opening balance of the first statement and adds each row's amount to the balance on the row above. Microsoft's guide to [calculating a running balance in Excel](https://support.microsoft.com/en-US/excel/calculate-a-running-balance) uses the same pattern: each balance cell adds the previous balance to that row's deposits minus its withdrawals.

### The formulas

Put the opening balance of your first statement in cell M2 of the summary block, described below. Then:

1. In G2: `=$M$2+E2`
2. In G3: `=G2+E3`, filled down for every later row.
3. In H2: `=IF(F2="","",ROUND(G2-F2,2))`, filled down.

The check column stays blank where the bank printed no balance and shows 0.00 where your running figure and the bank's agree. Any other number is the size of a gap.

### Why one signed amount

Microsoft's example keeps deposits and withdrawals in two columns. The template uses one signed Amount column instead, since that is how most converters deliver rows and it keeps the balance formula to one addition. If your converted statement has two columns, add a helper that subtracts the withdrawal from the deposit, then copy its values into Amount.

### Across months

If your statements are consecutive, the closing balance of one month is the opening balance of the next, so the running balance simply carries on. A gap of a month, or a statement converted out of order, shows up as a jump in the check column on the first row of the new month. That is the template telling you a statement is missing.

## What goes in the template's summary block?

The summary block holds, for one chosen month, the opening balance, money in, money out, a computed closing balance, the printed closing balance and the difference between the two. Put it beside the table, in columns L and M, so it is visible while you work. A difference of zero means the month is complete.

### The cells

The example figures below are made up. Type the month you want to check in M1.

| Cell | Label in L | Formula or value in M | Example |
| --- | --- | --- | --- |
| M1 | Month | Typed | 2026-09 |
| M2 | Opening balance | Typed from the statement | 2,750.00 |
| M3 | Money in | `=SUMIFS(E:E,B:B,M1,E:E,">0")` | 1,940.00 |
| M4 | Money out | `=SUMIFS(E:E,B:B,M1,E:E,"<0")` | -1,612.35 |
| M5 | Computed closing | `=M2+M3+M4` | 3,077.65 |
| M6 | Printed closing | Typed from the statement | 3,077.65 |
| M7 | Difference | `=ROUND(M5-M6,2)` | 0.00 |
| M8 | Rows this month | `=COUNTIFS(B:B,M1)` | 42 |

M2 holds the first month's opening balance for the running balance formula. When you check a later month, type that month's opening balance in a separate cell, such as M10, and point M5 at it instead, so the running balance keeps its starting point.

### The row count

The row count in M8 is a cheap second check. Count the lines on the PDF for one page and compare. A converted statement with the right totals but a different number of rows usually has two lines merged into one, or one line split in two with amounts that happen to cancel.

### Conditional formatting

Give M7 a rule that turns it red when it is not zero. Give column H the same rule. You will then see a problem the moment you paste a month, without reading a single number.

## Pasting a new month into the bank statement template

Each month, paste the converted rows below the last row of the table, fill in the Month and Seq columns, extend the formulas, and read the summary block for the new month. The whole job takes a few minutes once the template exists, and most of it is checking rather than typing.

### The steps

1. **Convert the statement.** Get the statement PDF from your bank and convert it to rows with date, description, amount and balance. If you need statements from years ago, [downloading old bank statements](/blog/download-old-bank-statements) explains how far back banks keep them and how to request older copies.
2. **Paste values only.** Paste below the last row with Paste Special, Values, so the template's formats survive.
3. **Fill Month.** Type the month in the first new row of column B and fill it down the new block.
4. **Continue Seq.** Fill column A on from the last number.
5. **Extend formulas.** Select the last filled cells of G and H and double-click the fill handle.
6. **Read the summary.** Type the new month in M1 and its opening and closing balances, and look at M7.
7. **Categorise.** Fill the Category column last, once you know the rows are right.

FastBankConvert is being built to turn a statement PDF into rows with date, description, amount and balance, with a warning before download if the balances do not add up. It is getting ready to open, and you can [ask to be told when it opens on the get started page](/get-started).

### Pasting from an online history download

If you sometimes copy recent activity from online banking instead of converting a statement, that list has no printed balances and may hold pending items. [Converting a bank transaction history PDF to Excel](/blog/bank-transaction-history-pdf-to-excel) explains how a history printout differs from a statement and how to keep the two apart in one workbook.

## Why does the template's balance break after pasting?

The balance usually breaks for one of five reasons: rows pasted in the middle of the table, a sort that changed the row order, amounts pasted as text, a month pasted twice, or a statement skipped. Each leaves a different sign in the check column, which makes them quick to tell apart.

### Reading the signs

- **Inserted in the middle.** The check is fine above the paste and wrong everywhere below it. Cut the block and paste it at the bottom.
- **Sorted.** The check shows scattered non-zero values across many months. Sort by Seq, smallest to largest.
- **Text amounts.** The running balance stops changing for some rows, and those amounts sit on the left of their cells. Convert them to numbers.
- **Pasted twice.** M8 shows double the row count for the month, and the difference in M7 equals the month's net change. Delete the second copy.
- **Skipped month.** The first row of a month shows a gap equal to the missing month's net change. Find and paste the missing statement.

### Hidden content in the PDF

Rarely, a statement PDF holds content you cannot see, such as a hidden text layer or a watermark, that a converter reads as extra rows. [Hidden PDF layers that end up in Excel](/blog/pdf-layers-to-excel) explains how to spot that and clean the file before converting.

## When a bank statement template is the wrong tool

A single workbook is the right tool for one person or one small business tracking a few accounts. It is the wrong tool when several people edit the same file, when you have many accounts, or when the data must feed accounting software. In those cases a shared sheet, separate files, or the accounting software's own import serves better.

### Shared editing

Excel workbooks shared by email fork into copies. If two people need to add months, a shared online sheet avoids that. [PDF to spreadsheet in Google Sheets or Excel](/blog/pdf-to-spreadsheet-google-sheets) covers the import steps and locale settings for Google Sheets, and the same columns work there.

### Many accounts

With more than three or four accounts, one table becomes hard to check. Use one copy of the template per account, with the same columns, and build any combined view from those copies.

### Accounting software

If the rows will end up in bookkeeping software, import there instead of keeping a parallel workbook. The software will have its own checks, and two records of the same money drift apart over time.