Key takeaways
What this article covers, in order:
- The spreadsheet that worked until it didn't
- What a good spreadsheet reconciliation looks like
- Where spreadsheets genuinely break
- Where spreadsheets are still fine
- Side by side
- Signs it's time to switch
A spreadsheet can reconcile a bank account perfectly well when you have a few dozen transactions a month, one account and one person doing the books. It starts to break when volume grows, when invoices and bills need tracking, or when more than one person touches the file. Here's how to tell which side of that line you're on, and what a good bank reconciliation spreadsheet looks like if you're staying put for now.
The spreadsheet that worked until it didn't
Tom runs a two-person landscaping business in Cairns (he's illustrative). For three years his books were one spreadsheet: a tab per month, bank transactions pasted in from a CSV, a category column, and a SUM at the bottom that he checked against the statement.
Honestly? It worked. Then three things happened in the same year. He took on a commercial maintenance contract with monthly invoices. He added a second business card for his offsider. And his accountant asked for an aged receivables list at the end of the year, which his spreadsheet had no way of producing.
None of those are spreadsheet failures exactly. They're the point where the spreadsheet stopped being the right tool.
What a good spreadsheet reconciliation looks like
If you're staying with a spreadsheet for now, do it properly. A sound setup has:
- A raw data tab where you paste the bank CSV exactly as downloaded. Never edit this tab.
- A working tab that references the raw data and adds your category, a description and a "matched" flag.
- A reconciliation block at the top:
| Line | Formula idea |
|---|---|
| Opening balance per statement | typed from the statement |
| Add: total deposits | SUM of the deposits column |
| Less: total withdrawals | SUM of the withdrawals column |
| Calculated closing balance | opening + deposits − withdrawals |
| Closing balance per statement | typed from the statement |
| Difference | calculated minus statement (should be 0.00) |
- An outstanding items list for anything in your records that hasn't cleared the bank, and anything in the bank you haven't explained.
- A locked copy saved at month-end (a PDF or a read-only file), so last month can't quietly change.
Here's a quick check with Tom's Sep 2026 figures. Opening balance $6,412.75, deposits $18,940.00, withdrawals $17,226.35. Calculated closing balance: $6,412.75 + $18,940.00 − $17,226.35 = $8,126.40. The statement says $8,126.40. Difference: $0.00.
That proves the CSV he pasted is complete. It doesn't prove his categories are right, and it doesn't tell him which customers still owe him money. That's the limit of the method.
Where spreadsheets genuinely break
Not "spreadsheets are bad". Just the specific places they struggle for bookkeeping.
- Invoices and payments live somewhere else. Your sales are in an invoicing tool or a Word template; your bank is in the spreadsheet. Matching a $4,400 deposit to the right invoice (or three invoices) is manual every time.
- No trail of changes. If a category or amount changes in a closed month, there's usually no record of who changed it or why.
- Copy-paste errors are silent. Paste a CSV one row short and your totals are still totals. Only the reconciliation block catches it, and only if you look.
- Formulas drift. A SUM range that stopped at row 140 when you now have 152 rows looks perfectly normal.
- One file, one person. Two people editing a copy each is how you end up with "Books FINAL v3 (Tom edits).xlsx".
- Reports have to be built by hand. A P&L, balance sheet or aged receivables report is a project, not a click.
Where spreadsheets are still fine
Being fair to the humble spreadsheet:
- Very low volume. Ten to thirty transactions a month, one account, no invoicing to speak of.
- A side business or hobby business that's just starting.
- One-off analysis. Even with accounting software, exporting to a spreadsheet to slice data or test a forecast is completely normal.
- When you understand it and use it consistently. A simple spreadsheet you actually update beats software you never open.
Side by side
| Spreadsheet | Accounting software | |
|---|---|---|
| Getting bank transactions in | Download CSV, paste | Connect your bank, or import CSV |
| Matching deposits to invoices | Manual | Suggested matches you confirm |
| Locking a finished month | Save a copy and hope | Lock the period; changes are controlled |
| P&L, balance sheet, receivables | Build them yourself | Standard reports |
| Two or more people | Version chaos | Shared books with user access |
| Cost | Usually already paid for | Free tiers exist; paid plans cost monthly |
| Learning curve | You already know it | An afternoon or two to set up |
Signs it's time to switch
You don't need all of these. Two or three is usually enough.
- You have more than about 50 bank transactions a month, or more than one account or card.
- You send invoices and need to know who owes you what.
- More than one person needs to see or edit the books.
- Month-end takes you longer each month, not shorter.
- Your accountant keeps asking for reports you can't produce.
- You've found a formula error that had been wrong for months.
Moving from a spreadsheet without losing history
- Pick a start date, ideally the first day of a month or quarter, such as 1 Jan 2027.
- Reconcile the spreadsheet up to the day before, so the closing bank balance is proven.
- Enter opening balances in the new system: bank balance, money owed to you, money you owe. Your accountant can help with the balance sheet side if you're unsure.
- Enter open invoices and bills individually, so payments can match against them.
- Keep the spreadsheet as your history. You don't need to re-enter old years.
- Reconcile the first month in the new system and check the opening balance matches the spreadsheet's closing balance exactly.
How HelloBooks helps
HelloBooks gets bank transactions in two ways: connect your bank account (most Australian banks and cards are supported) or import a CSV statement, which is handy if you're used to downloading CSVs already. Transactions land in a review list where you confirm or change the category.
The reconcile screen lines your statement up against your ledger with an AI match suggestion on each line, a confidence score and the reason for the match. You only deal with the exceptions. Unmatch in one click, export a reconciliation report as PDF or CSV, and lock a period once it's reconciled and signed off, with any reopening logged. Invoices, bills, AR and AP ageing, the P&L, Balance Sheet and Cash Flow are all on the Free plan (A$0, no card, no expiry, up to 200 transactions a year and 1 live bank feed). Excel export comes with Pro (A$30 a month), if you still like a spreadsheet for analysis.
Read about bank reconciliation software or AI bookkeeping.
FAQs
Can I legally keep my business books in a spreadsheet in Australia?
Yes. There's no requirement to use accounting software. What matters is that your records are accurate, complete and kept for the required period. Your accountant can tell you what they need.
How do I reconcile a bank account in Excel?
Paste the bank CSV into a raw tab, total deposits and withdrawals, add them to the opening balance and compare the result to the statement's closing balance. Then list any outstanding items.
Is accounting software worth it for a sole trader?
If you invoice clients, have more than a handful of transactions a month or want reports without building them, usually yes. A free plan lets you test that without spending anything.
Will I lose my old spreadsheet history if I switch?
No. Keep the spreadsheet as your record for earlier periods and start the software from a clean opening balance.
What's the biggest spreadsheet reconciliation mistake?
Not checking that the pasted CSV is complete. Always compare the calculated closing balance to the statement before categorising anything.
Whichever tool you pick, the reconciliation block is the habit that matters most.
Start free, no card needed. Try HelloBooks Free
