Bank reconciliation template (free Excel download)

What this bank reconciliation template does

A bank reconciliation template is a spreadsheet that proves the bank balance in your accounting records agrees with the balance on the bank statement at the same date, after allowing for timing differences and errors. This free Excel template lists both sides, finds the unmatched items, calculates an adjusted balance for each side and shows the difference, which must be zero.

The template is built for a month-end or weekly reconciliation of one bank account. It has no macros, no external links and no sheet passwords, and it works in Excel, LibreOffice Calc and Google Sheets. Yellow cells are inputs, grey cells are formulas, and green or red cells are checks that tell you whether the reconciliation is complete.

What is inside the workbook

The workbook has four sheets. Each one does a single job, so a reviewer can follow the reconciliation from the statement to the final difference without asking how a number was produced.

• How to use: the steps, the formulas in words, and a colour key.
• Reconciliation: the summary with the adjusted bank balance, the adjusted cash book balance, the difference, the status, an adjustments table and a sign-off block.
• Cash book: the opening balance and every receipt and payment recorded in your ledger for the period, each ticked Y or N for whether it appears on the statement.
• Bank statement: the statement opening balance and every statement line, each ticked Y or N for whether it is already in the cash book.
• Two completeness checks: the statement lines must add up to the printed closing balance, and the cash book lines must add up to the ledger balance.
• A count of unmatched items on each list, so you can see at a glance how much is outstanding.
• Room for 200 lines on each list and 10 adjustment lines, which covers most small and mid-sized accounts.

How to fill it in, step by step

Work through the steps in order. The template only gives a meaningful answer when both lists are complete, which is why the completeness checks come before the matching.

• Export the bank account's transactions for the period from your ledger and paste them into the Cash book sheet, including last month's outstanding items that cleared this month.
• Paste every line of the bank statement for the same period into the Bank statement sheet and enter its opening balance.
• Type the closing balance printed on the statement and the closing ledger balance on the Reconciliation sheet. Both checks must say OK.
• Tick off: put Y against each item that appears on both lists with the same amount. Leave N on everything else.
• Record errors, such as a cheque entered at the wrong amount, in the adjustments table with the side they belong to.
• Read the result. The difference must be 0.00 and the status must read RECONCILED.
• Post the cash book adjustments in your ledger, have a reviewer sign off, and file the statement with the reconciliation.

The worked example in the template

The example is a fictional trading company reconciling its EUR current account at 31 August 2026. Both the cash book and the statement opened the month at 12,400.00. The statement lines add up to a closing balance of 10,623.00, which agrees with the printed balance. The cash book closes at 10,285.00, which agrees with the general ledger.

On the bank side, a customer receipt of 2,300.00 banked on 31 August is a deposit in transit, and two cheques of 1,275.00 and 620.00 are unpresented, 1,895.00 in total. The adjusted bank balance is 10,623.00 plus 2,300.00 minus 1,895.00, which is 11,028.00.

On the cash book side, the statement shows two receipts the cash book has not yet recorded, interest of 18.00 and a customer's direct credit of 860.00, and two payments it has not yet recorded, charges of 35.00 and a 190.00 insurance direct debit. Cheque 1043 was entered in the cash book at 540.00 but cleared at 450.00, a transposition error that understates the cash book by 90.00. The adjusted cash book balance is 10,285.00 plus 878.00 minus 225.00 plus 90.00, which is also 11,028.00. The difference is 0.00 and the status reads RECONCILED.

The formulas behind the reconciliation

Every total on the Reconciliation sheet comes from the two lists, so nothing is typed twice. Deposits in transit are the cash book receipts not ticked Y, calculated with SUMIFS on the tick column. Unpresented payments are the cash book payments not ticked Y. The same logic on the statement sheet gives the money in and money out that the cash book has not yet recorded.

The adjusted bank balance is the statement balance plus deposits in transit, minus unpresented payments, plus or minus bank errors. The adjusted cash book balance is the cash book balance plus unrecorded receipts, minus unrecorded payments, plus or minus cash book errors. The difference is rounded to two decimals, and the status only turns green when the difference is zero and both completeness checks pass.

Every formula in the template was recalculated by two independent spreadsheet formula engines, and the example was checked against a separate calculation. Tests also confirm that unticking a matched receipt of 4,750.00 produces a difference of 4,750.00, and that removing the 90.00 correction or mistyping the printed balance turns the checks red.

Journal entries after the reconciliation

Only the cash book side creates journal entries. Deposits in transit and unpresented cheques are timing differences that clear on their own when the bank processes them. The unrecorded statement items and the cash book error, 743.00 net in the example, must be posted so that the ledger shows the true balance of 11,028.00.

• Interest credited: Dr Bank 18.00 / Cr Interest income 18.00
• Customer E paid by direct credit: Dr Bank 860.00 / Cr Trade receivables 860.00
• Bank charges: Dr Bank charges 35.00 / Cr Bank 35.00
• Insurance direct debit: Dr Insurance expense 190.00 / Cr Bank 190.00
• Cheque 1043 corrected from 540.00 to 450.00: Dr Bank 90.00 / Cr Trade payables 90.00
• Net effect on the bank account: 18.00 + 860.00 - 35.00 - 190.00 + 90.00 = 743.00, taking the ledger from 10,285.00 to 11,028.00

Common mistakes with bank reconciliation spreadsheets

Most reconciliation spreadsheets fail in the same few ways, and nearly all of them hide a difference instead of explaining it. The template's checks are designed to catch the ones a formula can catch; the rest need discipline.

• Typing the adjusted balance by hand instead of calculating it, so the reconciliation always appears to balance.
• A balancing figure labelled 'unreconciled difference' carried forward month after month.
• Ticking a pair as matched when the amounts differ, without recording the error.
• Leaving out last month's outstanding items, so they vanish instead of clearing.
• Reconciling to an intra-month statement date that does not match the ledger cut-off.
• Outstanding items that never clear: a cheque unpresented for months may be lost, cancelled or stale.
• No reviewer: the person who can make payments also reconciles the account and signs it off.

Controls and review

A bank reconciliation is one of the most important controls a finance team has, because it is the point where the books meet an independent third-party record. The template supports three controls: completeness, through the two balance checks; accuracy, through the zero-difference test; and review, through the prepared by and reviewed by sign-off with dates.

Keep the preparer and the reviewer separate, and make sure neither is the only person who can approve payments. The reviewer should look at the age of each outstanding item, not only at the final zero. An unpresented cheque older than a couple of months, a deposit in transit that has not cleared within a few days, or an adjustment without a document behind it are the items worth questioning.

Save one file per account per month, named consistently, for example bank-rec-current-2026-08.xlsx. Together with the bank statement it forms the audit evidence for the cash balance on the balance sheet.

Adapting the template

For several bank accounts, use one copy of the workbook per account; mixing accounts in one list makes matching harder and hides which account has the problem. For a busy account, reconcile weekly and carry the unmatched items forward each time. For a foreign-currency bank account, reconcile in the currency of the account, because the bank statement is in that currency; translation into your reporting currency is a separate step.

The same layout works for card and payment-provider clearing accounts: treat the provider's settlement report as the statement and the sales receipts in your ledger as the cash book. Fees deducted by the provider appear as unrecorded payments, exactly like bank charges.

When a spreadsheet is no longer enough

A spreadsheet reconciliation works well for a few hundred lines a month. Beyond that, the time goes into exporting, pasting and ticking rather than into investigating differences. The signals that you have outgrown it are familiar: matching takes more than a day, several people edit the same file, the export from the ledger and the statement cover slightly different dates, or last month's outstanding items have to be copied forward by hand.

At that point, reconciliation belongs inside the accounting system, where the ledger side is always complete, statement lines are imported rather than pasted, and matched items are remembered from one period to the next.

Doing this in Skyline Nexus ERP

In Skyline Nexus ERP, bank reconciliation lives in the Treasury module under Bank Reconciliation. You start a New Reconciliation by selecting the account, the from and to dates, the statement date, the statement reference and the Statement Ending Balance; the side panel shows the Book Balance and the last reconciliation. Statement files can be imported in CSV, TXT, XLSX or XLS format, and an Auto-match screen pairs statement lines with recorded transactions.

You then match items, mark items as outstanding and add adjustment lines until the variance is zero, and the finished reconciliation can be printed for the file. Treasury bank accounts are synced into the chart of accounts, so the book balance you reconcile is the same account that appears in the Fiscal Authority trial balance. The screen follows the same logic as the template: explain every difference until the variance is zero.