Small Business Bookkeeping Excel - Free Template

1. Open the Transactions sheet and enter each sale or expense on a separate row. Use one line per invoice, receipt or bank item so the totals stay reliable.
2. Choose the correct category and type. Income should be marked as Income and costs as Expense, with the VAT rate entered where needed.
3. Check the amounts. Net amount, VAT amount and gross amount should tie back to the receipt or invoice, for example £1,200 net, £240 VAT and £1,440 gross at 20%.
4. Mark payment status. Use the Paid? column to show what has cleared, especially if you are chasing 14-day or 30-day invoices.
5. Review the dashboard each week or month. Use it to compare sales, expenses and outstanding amounts before your VAT return or month-end close.
6. Update the category list only if your business needs extra headings. Keep the list short so coding stays consistent and reports stay meaningful.

This workbook suits the sole trader who is doing their own books on a Sunday evening, the office manager at a trades firm, and the bookkeeper at a small Ltd that wants a simple working file before importing to accounting software. It is also useful when you have 40 supplier bills, 25 customer invoices or a bank feed to review before month-end.

Image 1 shows the Transactions sheet laid out as a proper input table, not a loose list. With fields for reference, category, VAT rate and paid status, you can work through a week’s figures in order, then use the dashboard to see whether sales of £8,000 and costs of £2,350 are moving in the right direction.

Typical UK use cases

A builder with 4 employees may enter materials, fuel and subbie costs every Friday after the payroll run. An online shop doing 300 orders a month can use the sheet to separate sales, refunds and postage so the month-end numbers do not drift.

Why the structure matters

When your bookkeeping is split across bank statements, receipts and memory, errors creep in. A fixed spreadsheet with columns for net amount, VAT and gross amount gives you a clean working record before you move to HMRC returns or year-end accounts.

For HMRC, you need records that support the figures you put on Self Assessment, VAT returns and company accounts. The practical rule is simple: keep business records for 6 years if you run a company, and keep Self Assessment records for at least 5 years after the 31 January filing deadline for the tax year they relate to.

This template helps because it captures the basic evidence points: date, reference, description, customer or supplier, category and payment method. If you issue a £1,440 invoice at 20% VAT, the sheet stores the £1,200 net figure and the £240 VAT separately, which is exactly what you need when checking quarterly MTD returns.

VAT and tax-year pressure points

The UK tax year runs from 6 April to 5 April, and quarterly VAT returns under Making Tax Digital follow fixed filing dates. If your taxable turnover reaches the VAT registration threshold of £90,000 in a 12-month period, you need to register, so tidy records stop being optional and start being the difference between an accurate return and a scramble.

Invoice detail that supports the file

A proper invoice should show your name and address, a unique sequential invoice number, the invoice date, the customer details, and the VAT amount separately if you are VAT-registered. This spreadsheet gives you the working trail behind those invoices, which is what you need when HMRC asks how a £2,400 quarter was built up.

That same trail also makes it easier to separate allowable costs from the rest, so self assessment expenses records stay ready when the return needs each figure backed up.

The most expensive mistake is not the spreadsheet itself but the way people use it. A missing VAT rate, a sales line coded as an expense, or a payment marked as cleared before the money is in the bank can distort cash flow and leave you under-declaring or over-claiming VAT.

Where the numbers go wrong

If you enter £850 as gross when it was actually £850 net at 20% VAT, you have a £170 error on one line. Do that on 12 invoices in a quarter and you are off by £2,040, which is enough to ruin a VAT return and waste hours correcting the figures.

Operational mistakes that waste time

Another common problem is duplicate entries when the same receipt is entered from the bank feed and again from the paperwork. On a small business with 180 transactions a month, ten duplicated lines can mean an afternoon of unpicking the ledger, plus a month-end dashboard that no longer matches the bank.

Paid status causes trouble too. If you leave customer invoices marked unpaid when they were settled by bank transfer, your debtor list looks worse than it is and you spend time chasing money you already have. That is poor credit control, and it can hide the real issue: a customer that owes you £3,200 genuinely overdue.

That distinction matters when the ledger needs a payment tracking sheet to show which invoices are genuinely overdue and which have already been settled by bank transfer.

The file works best when you tie it to a fixed habit, not an occasional tidy-up. For many small businesses the easiest routine is to update the Transactions sheet on Friday afternoon, then check the dashboard just before the payroll run or VAT deadline.

Simple habits that keep it alive

• Copy the previous month’s working sheet and clear the old entries only if you need a fresh tab structure.
• Use the same category names every time, so £45.60 for stationery does not become office, admin and supplies in three different rows.
• Review unpaid items once a week, especially if you issue 14-day invoices and rely on prompt payment.
• Keep the Instructions sheet open for new staff or volunteers so entries stay consistent.

When to move on

If you are past 500 rows a month, have multi-user access needs, or need full bank feeds and automated reconciliation, a spreadsheet will start to feel tight. At that point, use it as a bridge to proper bookkeeping software rather than forcing it to do everything.