How to Do a Bank Reconciliation in Excel (Free Template)
A bank reconciliation proves that your books and your bank statement agree. Put the statement and your ledger side by side, tick off what matches, then explain every leftover line as a timing difference or a missing entry until the difference is zero. The free template below does the arithmetic.
Download the free template
Download the bank reconciliation template (.xlsx)
It has three sheets:
- Bank vs ledger: paste the statement on the left and your ledger on the right. Each line is marked matched or flagged in red when the same amount isn’t on the other side.
- Reconciliation: enter the two balances and list the unmatched items. It calculates the adjusted balances and the difference.
- How to use: the steps below, in short.
It works in Excel, and in Google Sheets or LibreOffice if you open the file there. No macros.
What a bank reconciliation is
Your bank statement shows what has cleared the bank. Your books (a cash book, a spreadsheet, or accounting software) show what you’ve recorded. They rarely match on any given day, for three ordinary reasons:
- Deposits in transit: money you’ve recorded as received that hasn’t reached the bank yet.
- Outstanding payments: cheques or payments you’ve recorded that haven’t cleared.
- Bank-only items: fees, interest or direct debits on the statement that you haven’t entered yet.
Reconciling means adjusting each side for these items. If anything is left over after that, it’s a mistake to find: a typo, a duplicate, or something missing.
Step 1: Get the bank statement into Excel
Download the statement period as CSV or Excel from online banking. If you only have a PDF statement, convert it with PDF bank statement to Excel: it reads the PDF in your browser and gives you dates and amounts as real numbers, not text. A scanned statement needs PDF table to Excel, which can read scans with OCR.
For the matching sheet, amounts need to be in one column, with money in positive and money out negative. If your bank splits them into debit and credit columns, see how to combine debit and credit columns.
Step 2: Match the statement to your books
Paste the statement into the left side of Bank vs ledger and your ledger for the same period into the right side. The formula counts how often each amount appears on both sides, so two payments of 50.00 need two ledger entries of 50.00 to match.
Anything flagged red is what you need to explain. Sorting both sides by amount makes it easy to spot near-misses such as 1,250.00 against 1,205.00.
Step 3: List the differences and check the result
On the Reconciliation sheet, enter the statement end date, the ending balance on the statement, and the balance in your books on the same date. Then put each red item in the right table:
- In your books but not on the statement, money in → Deposits in transit.
- In your books but not on the statement, money out → Outstanding payments (as a positive number).
- On the statement but not in your books → Items on the statement (fees negative, interest positive).
The sheet works out:
- Adjusted bank balance = statement balance + deposits in transit − outstanding payments
- Adjusted book balance = book balance ± items on the statement
- Difference = adjusted bank balance − adjusted book balance, which should be 0.00
A worked example
Made-up numbers for a month-end reconciliation:
| Item | Amount |
|---|---|
| Ending balance on the statement | 4,820.00 |
| + Deposit in transit (customer payment recorded on the 31st) | 600.00 |
| − Outstanding payment (supplier cheque not yet cashed) | 350.00 |
| Adjusted bank balance | 5,070.00 |
| Balance in your books | 5,082.50 |
| − Monthly bank fee, not yet recorded | 12.50 |
| Adjusted book balance | 5,070.00 |
| Difference | 0.00 |
The last step is to record the 12.50 fee in your books. Next month, the deposit and the cheque should appear on the statement and drop off the list.
If the difference isn’t zero
- Divisible by 9? A difference like 45.00 or 540.00 often means two digits were swapped when typing (54 entered as 45).
- Exactly double an amount? Something was entered with the wrong sign: a payment recorded as money in.
- Equal to one amount? Look for a transaction entered twice, or not at all. Duplicates are common after an import overlaps a bank feed: see how to fix duplicate bank transactions.
- Opening balance wrong? Check the starting balance against last month’s reconciled balance before hunting through this month.
- Wrong date? An item dated the 1st of next month in your books, but the 30th on the statement (or the other way round).
Reconciling in accounting software instead
QuickBooks and Xero have reconciliation built in, using the same logic. If you use them, see reconciling in QuickBooks Online, QuickBooks Desktop or Xero. A spreadsheet is still useful for an account that isn’t in your software, or to find a difference the software can’t explain.
Frequently asked questions
What is a bank reconciliation?
It is a check that the balance in your books matches the balance on your bank statement, after allowing for items that have been recorded on one side but not yet the other, such as deposits in transit, outstanding payments and bank fees.
How often should I reconcile?
At least monthly, when each statement arrives. Busy accounts are easier to reconcile weekly, because there are fewer items to match each time.
Can I reconcile a PDF bank statement in Excel?
Yes. Convert the PDF to Excel first so the dates and amounts are real numbers, then paste them into the template’s matching sheet.
Does the template work in Google Sheets?
Yes. Open the .xlsx file in Google Sheets (File, then Open, then Upload). It uses only standard formulas and no macros.