How to Do a Bank Reconciliation in Excel (Step by Step, With a Free Layout)

A bank reconciliation proves that the cash in your books matches the cash in the bank. Accounting software automates much of it, but a lot of small businesses, sole traders and bookkeepers still reconcile in Excel, especially for clean-up work, old periods or accounts that are not connected to a bank feed. This guide walks through a reliable method that works in Excel or Google Sheets.

What you need

• The bank statement for the period, in a spreadsheet.
• Your cash book or ledger for the same account and period: every receipt and payment you recorded.
• Last period's reconciliation, so you know which items were outstanding at the start.

If your bank only gives you PDFs, convert the statement first with the bank statement converter, which exports to Excel (.XLSX) and CSV. For receipts and invoices, the extractor workspace can pull tables from PDFs into a spreadsheet.

Step 1: Set up two tabs

Put the bank statement on one tab and the cash book on another. Use the same columns on both:

Use a single Amount column with money in as positive and money out as negative. It makes totals and matching far simpler than separate debit and credit columns.

Step 2: Check the statement is complete

Before matching anything, prove the bank tab is right. Take the statement's opening balance, add the total of the Amount column, and compare it with the closing balance printed on the statement. In Excel that is simply =Opening + SUM(D:D). If it does not agree, a row is missing, duplicated or has the wrong sign. Fix that first.

Step 3: Match transactions

Work through the bank tab and mark each line that appears in the cash book with the same amount. Useful techniques:

• Sort both tabs by amount. Identical amounts line up and are easy to tick off.
• Use a lookup to flag likely matches, for example =COUNTIF(CashBook!D:D, D2) to see whether an amount exists on the other tab. Treat it as a hint and confirm the date and description.
• Match references such as cheque numbers where you have them.
• Mark both sides with "Y" in the Matched column so nothing is matched twice.

Step 4: Deal with what is left

Unmatched items fall into two groups.

On the bank statement but not in your books

• Bank charges and fees
• Interest received
• Direct debits or standing orders you have not recorded
• Customer payments received directly into the account

These usually mean your books are incomplete. Record them in the cash book, then mark them as matched.

In your books but not on the statement yet

• Outstanding (unpresented) cheques: you wrote them, but they have not cleared.
• Deposits in transit: you banked them, but they arrived after the statement date.

These are timing differences. They stay on your list of outstanding items and should clear on the next statement.

Step 5: Prove the reconciliation

Build a short summary on a third tab:

The difference should be zero. Save the file with the period in the name and keep it with your records.

If the difference is not zero

1. Divide the difference by 9. If it divides evenly, a transposed number (for example 54 entered as 45) is a likely cause.
2. Divide the difference by 2. If the result matches a transaction, that item may have been entered with the wrong sign.
3. Look for an amount equal to the difference: a missed or duplicated entry.
4. Check last period's outstanding items have cleared or are still listed.
5. Check the date range on both tabs is the same.

Tips for faster month-end

• Reconcile every month, not once a year. Small differences are easy to find when they are fresh.
• Keep the same layout every month so formulas carry over.
• Write a short note against any adjustment so you or your accountant understand it later.
• If the account is in accounting software, import the converted statement instead; see importing a PDF statement into Xero or converting to QuickBooks QBO.

Frequently Asked Questions