Key takeaways
What this article covers, in order:
- Why most sole trader spreadsheets go wrong
- Before you start: two decisions
- Tab 1: Income
- Tab 2: Expenses
- Tab 3: Drawings
- Tab 4: Monthly summary
A sole trader bookkeeping spreadsheet UK small businesses can actually rely on needs four tabs: money in, money out, a monthly summary by category, and a bank check that proves the first three are right. Below is the exact layout to copy into Excel or Google Sheets, a full month worked through with real figures that add up, and the signs it's time to move to software.
Why most sole trader spreadsheets go wrong
Callum is a mobile dog groomer in Bristol. His first spreadsheet was one long list: dates, amounts, and a "notes" column that said things like "fuel?" and "Jen paid". By Mar 2026 it had 400 rows and no totals he trusted.
The problem wasn't the spreadsheet. It was that nothing in it checked itself. A sole trader income and expenses spreadsheet only works if it ties back to the bank every month. That's the tab most templates leave out, and it's the one that matters.
Before you start: two decisions
Use a separate business bank account if you can. It doesn't have to be a fancy one. It just means every line on the statement is business, and your spreadsheet can mirror it.
Record money when it moves. This layout logs income when it lands in the bank and expenses when they leave. It's the simplest way to keep a spreadsheet honest. Invoices that haven't been paid yet go on a separate little list (below), so you don't lose track of who owes you. Which basis your accounts are finally prepared on is a question for your accountant.
This layout also assumes you're not VAT registered. If you are, ask your accountant which extra columns they want.
Tab 1: Income
One row per payment received. Copy these headings into row 1.
| Date received | Customer | Invoice or ref | What for | Category | Amount (£) | On statement? |
|---|---|---|---|---|---|---|
| 05/09/2026 | Card takings | Week 1 | Grooming, walk-ins | Sales | 410.00 | ✓ |
Keep categories short. Most sole traders need two or three: "Sales", maybe a second stream, and "Other income" for bank interest and odd bits.
The "On statement?" column is your tick when you've found the payment on the bank statement. It sounds fussy. It's the column that catches mistakes.
Tab 2: Expenses
| Date paid | Supplier | What for | Category | Amount (£) | Paid by | Receipt kept? | On statement? |
|---|---|---|---|---|---|---|---|
| 03/09/2026 | Pet wholesaler | Shampoo, conditioner | Supplies | 142.75 | Debit card | ✓ | ✓ |
Pick your expense categories once and use a dropdown (Data validation in both Excel and Google Sheets) so you can't type "fuel" one week and "Petrol" the next. A starter set for most sole traders:
- Supplies and materials
- Motor fuel
- Motor repairs and insurance
- Phone and internet
- Software and subscriptions
- Repairs and equipment
- Bank and card fees
- Advertising
- Accountancy fees
- Other expenses (keep it small)
Tab 3: Drawings
Money you take out for yourself isn't an expense. Give it its own tab so it never sneaks into your costs.
| Date | What | Amount (£) | On statement? |
|---|---|---|---|
| 30/09/2026 | Transfer to personal account | 1,200.00 | ✓ |
Tab 4: Monthly summary
One row per category, one column per month. Use SUMIFS so it fills itself in. For fuel in September 2026, for example:
=SUMIFS(Expenses!E:E, Expenses!D:D, "Motor fuel", Expenses!A:A, ">="&DATE(2026,9,1), Expenses!A:A, "<="&DATE(2026,9,30))
Change the category name and dates for each cell, or point them at header cells so you can copy the formula across.
Tab 5: Bank check
This is the bit that turns a list into bookkeeping.
| Line | Formula |
|---|---|
| Opening bank balance | Type it from the statement |
| Add: income this month | Total from Tab 1 |
| Less: expenses paid from the bank | Total from Tab 2 |
| Less: drawings | Total from Tab 3 |
| Expected closing balance | Opening + income − expenses − drawings |
| Closing balance per statement | Type it from the statement |
| Difference | Should be 0.00 |
If the difference isn't zero, look for rows without a tick in "On statement?", then look for statement lines you haven't entered. It's nearly always one of those two.
A worked month: Callum, Sep 2026
Here's a full month so you can see it add up.
Income, Sep 2026
| Date | Customer | Ref | Category | £ |
|---|---|---|---|---|
| 05/09/2026 | Card takings | Week 1 | Sales | 410.00 |
| 12/09/2026 | Card takings | Week 2 | Sales | 385.00 |
| 19/09/2026 | Card takings | Week 3 | Sales | 452.00 |
| 26/09/2026 | Card takings | Week 4 | Sales | 398.00 |
| 29/09/2026 | Kennel business | Inv 0112 | Sales | 600.00 |
| Total | 2,245.00 |
His card provider pays takings in full and bills its fees once a month, so the fee appears as a separate expense. If yours deducts fees before paying out, record the gross takings as income and the fee as an expense, so both show.
Expenses, Sep 2026
| Date | Supplier | Category | £ |
|---|---|---|---|
| 01/09/2026 | Van insurer (direct debit) | Motor repairs and insurance | 68.40 |
| 03/09/2026 | Pet wholesaler | Supplies and materials | 142.75 |
| 08/09/2026 | Fuel station | Motor fuel | 74.20 |
| 15/09/2026 | Mobile network | Phone and internet | 22.00 |
| 17/09/2026 | Fuel station | Motor fuel | 69.85 |
| 20/09/2026 | Blade sharpening | Repairs and equipment | 35.00 |
| 24/09/2026 | Card provider | Bank and card fees | 31.60 |
| 27/09/2026 | Fuel station | Motor fuel | 71.30 |
| 28/09/2026 | Booking app | Software and subscriptions | 12.00 |
| Total | 527.10 |
Summary, Sep 2026
| Category | £ |
|---|---|
| Sales | 2,245.00 |
| Supplies and materials | 142.75 |
| Motor fuel | 215.35 |
| Motor repairs and insurance | 68.40 |
| Phone and internet | 22.00 |
| Repairs and equipment | 35.00 |
| Bank and card fees | 31.60 |
| Software and subscriptions | 12.00 |
| Total expenses | 527.10 |
| Profit for the month | 1,717.90 |
Fuel is 74.20 + 69.85 + 71.30 = 215.35. Expenses add to 527.10. Sales of 2,245.00 less 527.10 leaves 1,717.90.
Notice the £1,200.00 drawings aren't in there. Profit is what the business made. Drawings are what Callum took home out of it.
Bank check, Sep 2026
| Line | £ |
|---|---|
| Opening balance, 1 Sep 2026 | 1,840.25 |
| Add: income | 2,245.00 |
| Less: expenses | (527.10) |
| Less: drawings | (1,200.00) |
| Expected closing balance | 2,358.15 |
| Closing balance per statement, 30 Sep 2026 | 2,358.15 |
| Difference | 0.00 |
1,840.25 + 2,245.00 = 4,085.25. Less 527.10 is 3,558.15. Less 1,200.00 is 2,358.15. It matches, so Callum knows every penny on the statement is in the spreadsheet.
The unpaid invoices list
On 30 Sep 2026 Callum also invoiced a doggy daycare £280.00 (Inv 0113). It isn't income on Tab 1 yet, because the money hasn't arrived. It goes on a tiny side list instead:
| Invoice | Customer | Date sent | Due | Amount (£) | Paid on |
|---|---|---|---|---|---|
| 0113 | Doggy daycare | 30/09/2026 | 14/10/2026 | 280.00 |
When it's paid, fill in the date and add a row to Tab 1. Glance at this list every week. It's your debtors list, in miniature.
Habits that keep the spreadsheet honest
- Do it weekly, not yearly. Twenty minutes on a Friday beats a lost weekend just before your accountant's deadline.
- Keep receipts in a folder named by month. A photo on your phone in a "Sep 2026" folder is fine.
- Never edit last month's rows once the bank check is zero. Fix mistakes with a new row dated today.
- Back it up. A spreadsheet on one laptop is one spilt coffee from gone.
When to move from a spreadsheet to software
A spreadsheet is perfectly fine for plenty of sole traders. Callum's month had 15 bank lines. That's comfortable to type.
It starts to creak when:
- You've got more than about 40 or 50 transactions a month and typing them takes an evening.
- You run two accounts, a card and a payment app, and the bank check never quite balances.
- You're chasing invoices from a spreadsheet and keep forgetting who's paid.
- Your bookkeeper or accountant wants access and you're emailing file versions back and forth.
- You've broken a formula and only noticed three months later.
None of those are emergencies. They're just the point where the time you spend keeping the spreadsheet right costs more than it saves.
How HelloBooks helps
HelloBooks has a Free plan at £0, with no card and no expiry. It includes one live bank feed through Open Banking (most UK banks and cards) or CSV statement import, up to 200 transactions per year, invoices with unlimited email payment reminders, debtors and creditors ageing, and a P&L, Balance Sheet and Cash Flow report. Callum's 15 lines a month would come to around 180 a year, just under the limit; a busier sole trader would want Pro at £14.99/month, which adds unlimited bank connections and AI auto-categorisation.
Transactions land in a review list where you confirm or change the category, and the reconcile screen lines your statement up against your books, with an AI match suggestion on each line, so the bank check is mostly done for you. You can invite your accountant into the same books. See more on the freelancers page or compare plans.
FAQs
Is a spreadsheet good enough for a sole trader?
For many, yes, especially with a separate bank account and a monthly bank check. It's the bank check that makes it reliable.
What columns should a sole trader expenses spreadsheet have?
Date, supplier, what for, category, amount, how it was paid, whether you kept the receipt, and a tick for "found on statement".
Should I include drawings as an expense?
No. Money you take for yourself is drawings. Keep it on its own tab so your profit figure stays right.
Is there a free sole trader spreadsheet I can use?
You can build this one in Excel or Google Sheets in about half an hour from the headings above. Nothing in it needs paid software.
Do I record invoices when I send them or when I'm paid?
In this layout, income goes on Tab 1 when the money arrives, and unpaid invoices sit on a separate list. Ask your accountant which basis your year-end accounts use.
Copy the headings tonight, enter last month's statement, and see if your bank check lands on 0.00.
Start free, no card needed. Try HelloBooks Free
