How to Reconcile Your Bank Statement in Excel

Bank reconciliation sounds like an accountant’s chore, but it is really just a careful comparison: does your record of the money match what the bank says? Doing it regularly catches missed transactions, bank charges you forgot, and the occasional error before it grows into a headache. In this guide I will show you how to reconcile your bank statement in Excel, step by step, in a way that takes the mystery out of it.

I think of reconciliation as the monthly health check for your books. Skip it, and small discrepancies pile up unnoticed until a year-end clean-up becomes a major job. Do it regularly, and each one is tiny and easy to explain while you still remember the transaction. It is also your best defence against fraud and error: a payment you never authorised, or a duplicate that slipped through, shows up the instant your two columns refuse to agree.

What reconciliation actually proves

You are checking that the closing balance in your books equals the closing balance on the bank statement, once you account for timing differences such as a cheque that has not cleared. When they agree, you can trust your figures. When they do not, the gap points you straight to what is missing. That last point is the part people miss: a reconciliation that does not balance is not a failure, it is information. The size and sign of the difference usually tell you exactly what to look for.

There are two balances in play, and keeping them straight is half the battle. Your “book balance” is what your own records say you have. The “bank balance” is what the statement says. They differ for perfectly innocent reasons all the time, mostly timing, and reconciliation is the structured way of explaining every penny of that difference until nothing is left unaccounted for.

Step 1: Put your records and the bank side by side

Create two columns of transactions: your own records on the left, the bank statement on the right. Add a “matched” column you can tick off as you go.

The bank charge above is the classic find: the bank took it, but you never recorded it. Reconciliation is what surfaces things like this.

Step 2: Match transactions automatically

You can let Excel flag matches instead of eyeballing them. If your figure is in column C and the bank figure in column D, put =IF(C2=D2,"Match","CHECK") in a helper column. Anything saying “CHECK” needs a look. For larger lists, a lookup helps: =IFERROR(IF(VLOOKUP(C2,Bank!A:A,1,FALSE)=C2,"Found",""),"Missing") tells you whether each of your entries appears on the bank side at all.

For a busy account, matching purely on amount can throw up false pairs, because two different £50 payments look identical to a formula. The fix is to match on a combination, such as amount and date together, or amount and a reference. You can build a helper key in each list with something like =C2&"|"&TEXT(A2,"dd/mm/yyyy") and then look that combined key up on the other side. It feels fiddly the first time, but it turns reconciliation from a guessing game into a reliable, repeatable check that you can trust month after month.

Step 3: Total each side and find the gap

Sum your records with =SUM(C2:C200) and the bank column with =SUM(D2:D200). The difference is simply =SUM(D2:D200)-SUM(C2:C200). If that difference is zero, you are reconciled. If it is not, the amount itself is a clue: £8.50 in our example matches the unrecorded bank charge exactly.

The usual suspects behind a difference

When the two sides will not agree, the cause is nearly always one of a small handful of things, so it pays to know the list. Bank charges, card fees and interest are the most common, because the bank applies them without telling you in advance, so they appear on the statement but not in your records. Next come transposed figures, where £45.10 has been typed as £41.50; tellingly, the size of a transposition error is always divisible by nine, which is a neat way to spot one. Then there are duplicates, where the same payment has been entered twice, and timing differences, where a transaction is genuinely on one side but has not yet reached the other. Work down that list in order and you will resolve the vast majority of discrepancies in minutes rather than hunting blindly through hundreds of rows.

Step 4: Deal with the differences

Differences fall into two camps. Timing differences, such as a payment you have recorded that the bank has not yet processed, are fine and will clear next time. Genuine omissions, such as that bank charge or a card fee, need adding to your records. Once you have entered the real ones, re-run the totals and the difference should fall to zero.

Step 5: Reconcile to the statement balance

Finally, confirm your adjusted records balance equals the bank’s closing balance. A clean check is =IF(ROUND(YourBalance-BankBalance,2)=0,"Reconciled","Difference"). The ROUND guards against tiny floating-point pennies that can otherwise make two equal numbers look different. Do this monthly and your small business bookkeeping template will always tie back to reality. I like to keep a dated reconciliation tab for each month, so there is a permanent record that the books agreed with the bank on that date, which is exactly what an accountant or lender wants to see.

To make the routine genuinely quick, download your bank transactions as a CSV each month and paste them straight into the bank column rather than typing them. Most UK banks offer this from their online portal. With the data pasted in, your match formulas light up the differences immediately, and a job that once took an evening shrinks to a coffee break. The habit, not the heroics, is what keeps your books reliable; a steady monthly reconciliation beats a frantic annual one every single time.

Common mistakes

• Skipping bank charges and fees. These are the most commonly missed entries. Reconciliation exists largely to catch them.
• Confusing timing differences with errors. An uncleared payment is not a mistake; it will clear. Do not “fix” it by adding it twice.
• Comparing the wrong dates. Reconcile to the statement’s closing date, not today, or in-flight transactions will skew the comparison.
• Ignoring small differences. A few pence usually signals a rounding or data-entry issue worth finding, not ignoring.

Frequently asked questions

How often should I reconcile my bank account?

Monthly is the standard for most small businesses, matching each bank statement as it arrives. If your volume is high, weekly keeps the job small and the surprises smaller.

What is a timing difference?

It is a transaction you have recorded that the bank has not processed yet, or vice versa, such as a cheque that has not cleared. It explains a difference without being an error.

Why will my bank reconciliation not balance?

The usual reasons are a missing bank charge, a duplicated entry, a transposed figure, or comparing to the wrong date. The size of the difference often points to the cause.

Can I reconcile in Excel or do I need software?

Excel is perfectly capable for most small businesses. Accounting software automates matching, but the logic is identical, and doing it in Excel is a great way to understand what the software is doing.