Free bank reconciliation template for Excel

A bank reconciliation compares your bank statement with your own records and explains every difference between them. This template lays out the standard format, totals each list for you, and shows the difference that remains. It also tests every running balance on the statement, so a mistyped amount shows up before you start matching.

Download the template (.xlsx)Free, no sign-up. Works in Excel, Google Sheets and LibreOffice.

What's in the workbook

Reconciliation: the summary. Bank side, book side and the difference, with a Reconciled / Not reconciled status line and sign-off cells.
Bank statement: paste the statement here. A Check column tests each row's running balance against the one before it.
Cash book: your own records. Each amount is looked up on the statement and marked Yes or Not found.
Deposits in transit and Outstanding payments: timing differences, totalled into the bank side.
Not in books: fees, interest and direct debits to post afterwards, totalled into the book side.
How to use: the steps below, inside the file.

A worked example

The statement shows 2,246.20. A 500.00 deposit and a 300.00 cheque haven't cleared yet, and the bank added 1.20 interest and took a 5.00 fee that the cash book doesn't have.

Closing balance on the bank statement	2,246.20
Add: deposits in transit	500.00
Less: outstanding payments	300.00
Adjusted bank balance	2,446.20
Closing balance in your cash book	2,450.00
Add: interest not yet in your books	1.20
Less: bank fee not yet in your books	5.00
Adjusted book balance	2,446.20
Difference	0.00

How to reconcile a bank statement in Excel

Paste the bank statement's transactions into the Bank statement tab and enter the opening balance. Rows marked 'Check this row' have a running balance that doesn't add up.
Enter your own records for the same period in the Cash book tab. Amounts marked 'Not found' aren't on the statement yet.
Copy unmatched receipts to Deposits in transit and unmatched payments to Outstanding payments.
List statement items you haven't recorded yet, such as bank fees, interest and direct debits, on the Not in books tab.
On the Reconciliation tab, enter the statement's closing balance and your book balance. When the difference shows 0.00, the account is reconciled.

Statement only as a PDF or a scan?

Typing a statement into the Bank statement tab is where most reconciliation errors start. StatementFlow converts PDFs, scans and photos into these columns and checks every running balance while it converts. The free plan covers 25 pages a month.

Convert a statement

Questions

Bank reconciliation questions

Straight answers. Still stuck? Contact us.

Is the template free?

Yes. Download it, change it and share it. No sign-up or email address needed.

Does it work in Google Sheets, LibreOffice or Numbers?

Yes. It uses only standard formulas (SUM, IF, ROUND, COUNTIF and TEXT). In Google Sheets, choose File, then Import, and upload the .xlsx file.

What does a bank reconciliation statement show?

Two adjusted balances side by side. The bank side starts from the statement's closing balance, adds deposits in transit and subtracts outstanding payments. The book side starts from your cash book balance, adds money the bank received that you haven't recorded and subtracts fees or debits you haven't recorded. The two adjusted balances should match.

Why won't my reconciliation balance?

Check three things first. A difference divisible by 9 often means two digits were swapped. A difference equal to one transaction suggests it was recorded twice or missed. A difference equal to twice a transaction usually means it was entered as money in instead of money out.

How often should I reconcile?

Monthly, when each statement arrives, is the usual habit. It keeps the list of unmatched items short, and errors are easier to trace while the month is fresh.

Can charities and parish councils use it?

Yes. The summary tab follows the standard bank-side and book-side format, which suits a treasurer's year-end reconciliation or a council's reconciliation to the annual return (AGAR). Use one copy per bank account.

My statement is a PDF. How do I get the transactions into the template?

Convert the PDF to Excel or CSV first, then paste the rows into the Bank statement tab. StatementFlow does this for PDFs, scans and photos, and checks each running balance as it converts.