Key takeaways
What this article covers, in order:
- Why most spreadsheets drift within a few months
- The four tabs
- Tab 1: Invoices
- Tab 2: Money In
- Tab 3: Money Out
- Tab 4: Bank Check
A good sole trader bookkeeping spreadsheet needs four tabs: Invoices, Money In, Money Out and a monthly Bank Check that proves the sheet agrees with your bank statement. Below is the exact layout to copy, a worked month for an Aussie freelancer that adds up to the cent, and an honest list of signs it's time to move to software.
Why most spreadsheets drift within a few months
Ethan's a freelance graphic designer in Hobart (he's illustrative). He started FY2026-27 with a fresh spreadsheet, colour-coded and everything. By Christmas it had three tabs nobody remembered the purpose of, two rows for the same invoice, and a total that was $312 off the bank with no way to tell why.
The spreadsheet wasn't the problem. The missing piece was a check. A sheet that never gets compared to the bank will drift, quietly, every month. So the layout below is built around one question you answer at month end: does my sheet's closing balance equal the bank's?
The four tabs
| Tab | What it holds | When you update it |
|---|---|---|
| Invoices | Every invoice you send, paid or not | When you invoice and when you're paid |
| Money In | Every deposit into the business account | Weekly |
| Money Out | Every payment out of the business account | Weekly |
| Bank Check | One row per month tying the sheet to the statement | Month end |
Use one bank account for the business only. That single decision saves more spreadsheet pain than any formula.
Tab 1: Invoices
| Inv no. | Date issued | Client | Description | Amount | Due date | Date paid | Status |
|---|---|---|---|---|---|---|---|
| 041 | 3 Oct 2026 | Client A | Logo refresh | $1,650.00 | 17 Oct 2026 | 3 Oct 2026 | Paid |
Number invoices in order and never reuse a number. Status is Paid, Part paid or Unpaid. Sort by Status and you've got a rough debtors list.
Tab 2: Money In
| Date | Description | Inv no. | Category | GST code | Amount |
|---|
Categories for a sole trader can be short: Sales, Interest, Other income, Transfer in. Money you move in from your personal account is a transfer, not income.
Tab 3: Money Out
| Date | Payee | Description | Category | GST code | Amount | Receipt kept? |
|---|
Suggested categories: Software, Phone and internet, Equipment, Insurance, Workspace, Bank fees, Professional fees, Advertising, Travel, Drawings, Transfer out. Drawings (money you pay yourself) and transfers aren't expenses, so give them their own categories and leave them out of your expense total.
The GST code column is just a label, like "GST", "GST-free" or "No GST". Code it as you go; your BAS agent or accountant works out the figures and lodges.
Tab 4: Bank Check
| Month | Opening balance | Add: Money In | Less: Money Out | Calculated closing | Statement closing | Difference |
|---|
The formulas are simple:
- Calculated closing = Opening + Money In − Money Out
- Difference = Statement closing − Calculated closing
- Next month's Opening = this month's Statement closing
Difference should be $0.00. If it isn't, don't move on until you know why.
A worked month: October 2026
Ethan's business account opened October with $6,240.00.
Money In, Oct 2026
| Date | Description | Inv no. | Category | GST code | Amount |
|---|---|---|---|---|---|
| 3 Oct 2026 | Client A | 041 | Sales | GST | $1,650.00 |
| 10 Oct 2026 | Client B | 042 | Sales | GST | $2,200.00 |
| 17 Oct 2026 | Client C | 043 | Sales | GST | $880.00 |
| 28 Oct 2026 | Client B | 044 | Sales | GST | $1,320.00 |
| 31 Oct 2026 | Bank | Interest | No GST | $4.12 | |
| Total | $6,054.12 |
Money Out, Oct 2026
| Date | Payee | Category | GST code | Amount |
|---|---|---|---|---|
| 2 Oct 2026 | Design software subscription | Software | GST | $87.99 |
| 5 Oct 2026 | Mobile phone | Phone and internet | GST | $65.00 |
| 9 Oct 2026 | Internet | Phone and internet | GST | $79.00 |
| 14 Oct 2026 | Stock image licence | Software | GST | $45.00 |
| 15 Oct 2026 | Ethan (personal account) | Drawings | $2,000.00 | |
| 20 Oct 2026 | Co-working day passes | Workspace | GST | $132.00 |
| 24 Oct 2026 | Bank | Bank fees | No GST | $10.00 |
| 27 Oct 2026 | Insurer | Insurance | GST | $48.50 |
| 30 Oct 2026 | Ethan (personal account) | Drawings | $2,000.00 | |
| 31 Oct 2026 | Tax savings account | Transfer out | $1,200.00 | |
| Total | $5,667.49 |
Expenses only (leaving out drawings and the transfer): $87.99 + $65.00 + $79.00 + $45.00 + $132.00 + $10.00 + $48.50 = $467.49. Add $4,000.00 drawings and the $1,200.00 transfer and you get $5,667.49 of Money Out.
Bank Check, Oct 2026
| Month | Opening | Add: Money In | Less: Money Out | Calculated closing | Statement closing | Difference |
|---|---|---|---|---|---|---|
| Oct 2026 | $6,240.00 | $6,054.12 | $5,667.49 | $6,626.63 | $6,626.63 | $0.00 |
Check: $6,240.00 + $6,054.12 = $12,294.12, less $5,667.49 = $6,626.63. It ties.
What the month tells Ethan
- Income $6,054.12, less expenses $467.49, gives a profit of $5,586.63 for October on a cash basis.
- He paid himself $4,000.00 and moved $1,200.00 into a separate savings account for tax. How much to set aside is a question for his accountant.
- Invoice 045 for $990.00, raised on 30 Oct 2026, is still unpaid. It sits in the Invoices tab as Unpaid and doesn't touch Money In until the money arrives.
When the Bank Check won't balance
Work through these in order:
- Is a transaction missing? Tick each statement line against the sheet.
- Is one entered twice? Sort by amount and look for pairs.
- Did two digits swap? If the difference divides evenly by 9, a transposition like $54 entered as $45 is a good bet.
- Is a sign wrong? A refund entered as money out doubles the gap.
- Did last month's closing carry forward correctly?
Little rules that keep the sheet honest
- One row per bank line. Don't combine.
- Never type over a formula cell. Lock them if your spreadsheet lets you.
- Keep receipts in a folder named by month, with the file named by date and payee.
- Save a copy of the file at each month end, named by month and year.
- Don't delete mistakes. Add a correcting row with a note.
When to move from a spreadsheet to software
A spreadsheet is fine while you're small and disciplined. It's time to move when:
- You're past about 50 to 60 bank lines a month and data entry eats your evenings
- You have a second account or a credit card to reconcile
- Clients pay part invoices and the Invoices tab can't keep up
- Your bookkeeper or BAS agent wants to work in your books, not in a file you email
- You've missed a Bank Check two months running
Software with a bank connection removes the typing, and reconciliation becomes matching rather than ticking. The habits from this sheet carry straight over.
How HelloBooks helps
When you outgrow the sheet, HelloBooks Free costs A$0, with no card and no expiry. It includes 2 users, 1 live bank feed (most Australian banks and cards) or CSV statement import, up to 200 transactions a year, invoices and bills, AP/AR ageing, and the P&L, Balance Sheet and Cash Flow reports. Bank transactions land in a review list where you confirm or change categories.
The reconcile screen does the Bank Check for you: your statement lines up against your ledger with an AI match suggestion and confidence score on each line, and you produce a reconciliation report as PDF or CSV. Pro at A$30 a month adds AI auto-categorisation, unlimited bank connections and Excel export, if you still like a spreadsheet at the end. More on invoicing and automated bookkeeping.
FAQs
Is a spreadsheet good enough for a sole trader in Australia?
For a simple business with one account and modest volume, yes, as long as you check it against the bank every month and keep your receipts.
Should my spreadsheet calculate GST?
Code each line (GST, GST-free, No GST) and let your BAS agent or accountant work out the figures and lodge.
Are drawings an expense?
No. Drawings are money you take out for yourself. Record them in their own category and keep them out of your expense total.
Do I need separate tabs for each month?
No. One Money In and one Money Out tab with a date column is easier to filter. The Bank Check tab gets one row per month.
Can I import my spreadsheet into accounting software later?
Usually you'd start fresh from an opening balance and bring in bank history by connecting your bank or importing a CSV statement. Keep the spreadsheet as your record of the earlier months.
Copy the four tabs, fill in one month, and make the Difference column say $0.00. That's the whole job.
Start free, no card needed. Try HelloBooks Free
