Bank reconciliation in Excel sounds like something only accountants do, but it’s really just checking that your records and your bank statement agree. If they do, you know nothing’s been missed. If they don’t, you know there’s something to find. It’s one of the simplest habits that makes your bookkeeping trustworthy.
Why bank reconciliation in Excel matters
Without reconciling, it’s surprisingly easy to:
- Miss an expense you paid by card and forgot to record (which means paying more tax than you need to).
- Record the same invoice twice.
- Type £1,250 as £1,520 and never notice.
With Making Tax Digital meaning quarterly updates to HMRC, it’s well worth knowing your figures are right before they go anywhere.
4 simple steps
1. Get your bank statement into Excel. Most banks let you download a CSV file. Open it and turn it into a Table (Ctrl+T) so your formulas pick up every row.
2. Check your balances match. Your opening balance plus money in, less money out, should equal the closing balance on your statement:
=B1+SUM(Bank[In])-SUM(Bank[Out])
If that doesn’t match the closing balance the bank shows, something is missing from the download itself.
If the balances match, then you shouldn’t need to check individual transactions.
3. Match each bank line to your records. This formula checks whether an amount on the bank statement appears in your bookkeeping list:
=IF(COUNTIF(Records[Amount],[@Amount])>0,”Matched”,”Check”)
Anything showing “Check” either hasn’t been recorded yet, or has been recorded with a different amount. Pair it with Conditional Formatting to turn those cells amber, and they jump straight out at you.
4. Find the difference. Finally, compare your bank balance with what your records say it should be:
=SUM(Bank[Amount])-SUM(Records[Amount])
When that’s zero, you’re reconciled. ACE!
Top Tip: if the difference divides exactly by 9, look for two digits typed the wrong way round (transposed), it’s a classic old bookkeeping trick and it works surprisingly often.
How often should you reconcile?
Monthly is ideal for most sole traders and landlords. It takes minutes when it’s only a month’s worth of transactions, and it means each quarterly update is based on figures you already trust (and should also be fairly familiar).
Keep your bank statements alongside your records too, HMRC’s guide to business records if you’re self employed explains what you need to keep and for how long.
Start with my free ACE Bank Template
If you want somewhere simple to keep track of your bank transactions, my free ACE Bank Template is a great starting point. It’s a spreadsheet that lets you keep track of your bank transactions, personal or business, and it’s completely FREE to download. If you’re planning ahead too, my free ACE Forecasting Template gives you a simple 12 month forecast.
When you want reconciliation done for you
The free template is brilliant for keeping track, but if you want proper bookkeeping with reconciliation built in, that’s exactly what ACE Professional does. It includes everything in ACE Essentials, income and expenses, invoices, monthly profit and loss, estimated tax and quarterly figures, PLUS automated bank reconciliation for up to three accounts and a stronger audit trail.
It’s what the landlord in my ACE Professional walkthrough uses to make sure every bit of rent and every expense has been captured. And it’s a one off cost of £449, no monthly subscription.
Not sure if you need reconciliation at all? My post on which ACE product actually fits you goes through all three options.
Questions I get asked about reconciling
Do sole traders have to reconcile? It isn’t a legal requirement, but it’s the easiest way to know your figures are right before they go to HMRC.
What about cash payments? Record them in your bookkeeping as normal. They won’t appear on the bank statement, so just keep them clearly marked as cash.
I’ve got more than one bank account, is that a problem? Not at all, ACE Professional handles up to three.
If you’d like to see how ACE Professional reconciles your bank for you, book a FREE Bookkeeping Demo and I’ll show you.
