Why spreadsheets still earn their place

Plenty of accountants will tell you to move onto dedicated bookkeeping software from day one. For some businesses that is sound advice. But for a sole trader with twenty invoices a month, or a small limited company with a handful of regular clients, a well-built spreadsheet is often quicker, cheaper and far more flexible than paying a monthly subscription for a system you barely use.

The trade-off is that a spreadsheet does not enforce anything. Software will stop you posting a payment to the wrong invoice; a spreadsheet will cheerfully let you type rubbish into the wrong cell. That means the value you get out of it depends almost entirely on how carefully you set it up and how consistently you keep it up to date.

HMRC does not care whether your records live in a spreadsheet or in expensive software. It cares that they are accurate, complete and kept for at least five years after the 31 January submission deadline for self assessment, or six years from the end of the accounting period for a limited company. A tidy spreadsheet meets that requirement perfectly well.

Laying out your columns properly

Start with one row per transaction and one column per piece of information. Resist the temptation to merge cells, add decorative headers or put two dates in one cell. Consistency matters far more than appearance.

• Date — the date the transaction happened, not the date you got round to recording it.
• Description — who paid you or who you paid, plus what for.
• Category — keep a short, fixed list such as travel, materials, software, subcontractors, bank charges.
• Money in and Money out — two separate columns rather than one signed column, which makes the totals much easier to read.
• VAT — if you are registered, record the VAT amount separately so you can complete your return without unpicking old rows.
• Payment method — business bank account, personal card, cash. You will need this when you reconcile.
• Receipt reference — a simple code linking the row to a scanned receipt or PDF in a folder.
• Invoice number — the bridge between your income log and your sales ledger.

Freeze the header row, turn on data validation for your category column so only your approved list can be selected, and format the money columns as currency. Ten minutes of set-up saves hours later.

Recording income and expenses without the mess

Set aside a fixed time each week — Friday afternoon works well for most people — and enter everything from that week while it is still fresh. Do not leave it until the week before your filing deadline.

Log income when you raise the invoice and mark when it is actually paid. A single "income" column cannot tell you both things, which is why the invoice tab described below matters. For expenses, record the gross amount you paid and, if you are VAT registered, split out the VAT at the same time.

Watch out for the classic errors: recording a supplier invoice and its bank payment as two separate expenses, mixing personal spending into business columns without flagging it, and forgetting mileage. If you use the simplified expenses method for vehicles, keep a separate mileage log with dates, destinations and business purpose.

Keeping tabs on invoices and unpaid bills

A second tab, structured as a simple sales ledger, will save you a great deal of grief. Give it one row per invoice with columns for invoice number, client, date issued, due date, amount, amount received, date received, and a status column showing paid, part-paid or overdue.

Then use a formula to total everything still outstanding. Checking that single figure once a week is the quickest way to spot a client drifting past thirty or sixty days. Chasing promptly is one of the most profitable habits a small business can build, and it costs nothing.

Keep a matching tab for money you owe — subcontractors, wholesale suppliers, your accountant — so you always know what is going out in the next fortnight.

Backups, formulas and simple checks

A spreadsheet sitting only on one laptop is a business risk. Save it to a cloud drive so that version history is automatic, and once a month download a copy to your own storage as well. Rename monthly files clearly, for example with the tax year and month, so you can always open an earlier version if something goes wrong.

• Use SUMIF to total each category, so your profit and loss figures update themselves.
• Never overwrite an old row. Correct errors by adding a note in the description column, not by deleting history.
• Reconcile against your bank statement every month. Tick off each line and investigate anything unmatched.
• Password-protect the file if it contains client or personal data.

Once a quarter, look at your totals with fresh eyes. Do the numbers tell a story you recognise? If a category looks oddly high or low, there is usually a duplicate or a missed entry behind it.

Knowing when to move on

A spreadsheet becomes the wrong tool when you cross the VAT threshold, when you take on employees and need payroll, when you are issuing more than a few dozen invoices a month, or when you start dreading the bookkeeping rather than simply getting on with it. At that point, the hours you spend maintaining the file cost more than software would. Until then, a clear set of columns, a weekly habit and a reliable backup will serve you very well indeed.