You do not need expensive accounting software to keep your small business finances in order. For many sole traders, freelancers, and small businesses, Microsoft Excel provides everything needed to track income and expenses, manage cash flow, produce basic financial reports, and prepare for VAT returns and tax self-assessment. This beginner’s guide walks you through setting up a practical Excel-based accounting system from scratch — no prior accounting knowledge required.
Why Excel Works for Small Business Accounting
Dedicated accounting software like Xero, QuickBooks, or Sage is excellent — but it comes with monthly subscription costs, learning curves, and features that many small businesses simply do not need. Excel, particularly as part of Microsoft Office 2021 Professional Plus or Office 2024 Professional Plus, gives you a fully flexible accounting environment that you control entirely — and you only pay for it once.
Excel accounting is particularly well-suited to businesses with straightforward finances: service-based businesses, freelancers, tradespeople, and online retailers whose transactions are relatively uncomplicated. If your business handles payroll for multiple employees, manages stock for thousands of SKUs, or requires complex job costing, dedicated software may be worth the investment. For everyone else, Excel is more than capable.
Understanding the Basics: Income, Expenses, and Cash Flow
Before building any spreadsheets, it helps to understand three foundational concepts:
Income (Revenue): Money coming into your business from sales of goods or services. Record every invoice paid and any other income source.
Expenses (Outgoings): Money leaving your business: purchases, subscriptions, travel, equipment, utilities, professional fees. Every expense should be recorded with a date, description, category, and amount.
Cash flow: The difference between money in and money out at any given time. A profitable business can still fail if it runs out of cash — for example, if clients pay late while bills fall due. A cash flow spreadsheet tracks this balance over time and helps you anticipate shortfalls before they become crises.
Setting Up Your Workbook
Create a new Excel workbook and save it as “BusinessAccounts_[Year].xlsx”. Inside the workbook, create the following sheets:
- Income: A log of all income received
- Expenses: A log of all expenditure
- VAT Tracker: A running calculation of VAT owed (if you are VAT registered)
- Profit and Loss: A monthly summary of income versus expenses
- Cash Flow: A running balance of cash in and out
- Settings: Central reference data (tax year dates, VAT rate, business name)
Building the Income Log
On the Income sheet, create the following columns in row 1: Date, Invoice Number, Client Name, Description, Net Amount (£), VAT (£) (if applicable), Gross Amount (£), Payment Method, and Payment Received (Yes/No).
Format the date column as dd/mm/yyyy — the standard UK date format — and format the amount columns as currency. In the Gross Amount column, enter a formula to add the Net Amount and VAT columns automatically. If you are not VAT registered, omit the VAT column and simply record the net amount.
Format the entire range as an Excel Table (Ctrl+T). This makes it easy to sort, filter, and reference in other sheets. Give the table a descriptive name like “IncomeLog” in the Table Design tab.
Building the Expenses Log
On the Expenses sheet, create similar columns: Date, Supplier/Payee, Description, Category, Net Amount (£), VAT (£) (if VAT registered and reclaiming input VAT), Gross Amount (£), Payment Method, and Receipt Saved? (Yes/No).
The Category column is crucial for tax reporting. Use a consistent set of expense categories that align with HMRC’s self-assessment categories. Common categories include: Office Costs, Travel and Subsistence, Professional Fees, Marketing and Advertising, IT and Software, Equipment, Bank Charges, Training, and Insurance. Create a dropdown list for the Category column using Data > Data Validation > List. This ensures consistent categorisation and enables accurate reporting.
Building the VAT Tracker
If your business is VAT registered, you need to track output VAT (VAT charged to customers) and input VAT (VAT you can reclaim on purchases). On the VAT Tracker sheet, set up a quarterly summary that uses SUMIFS formulas to pull totals from the Income and Expenses logs filtered by date range.
For each VAT quarter, calculate: total net income, output VAT charged, input VAT reclaimable, and the net VAT payable to HMRC (output minus input). Define the quarter start and end dates as named cells on the Settings sheet, making it easy to update each quarter without modifying formulas throughout the workbook.
Building the Profit and Loss Summary
The Profit and Loss sheet summarises your financial performance by month. Set up rows for each income and expense category, and columns for each month (January through December, plus a Year Total column).
Use SUMIFS formulas to pull data from the Income and Expenses logs filtered by date range and category. At the bottom of each column, calculate Gross Profit (Income minus Cost of Sales) and Net Profit (Gross Profit minus Overheads). Add a conditional formatting rule to highlight months where the net profit is negative — a red fill with white text makes problem months immediately visible without having to scan the numbers manually.
Building the Cash Flow Tracker
Cash flow tracking is different from profit and loss — it captures the actual timing of money moving in and out, regardless of when it was earned or incurred. A simple cash flow tracker has this structure for each month:
- Opening balance (cash in the bank at the start of the month)
- Total income received (actual payments received, not invoices raised)
- Total expenses paid (actual payments out)
- Net cash flow (income minus expenses)
- Closing balance (Opening balance + Net cash flow)
The Closing Balance of one month becomes the Opening Balance of the next. Update this sheet weekly to maintain an accurate picture of your cash position. Adding a projected section alongside the actual figures — using expected invoice payment dates and planned expenses — allows you to forecast your bank balance several weeks ahead and anticipate potential shortfalls before they arise.
Useful Formulas for Small Business Accounting
SUMIFS: Sums values that meet multiple criteria — the backbone of accounting reports in Excel. Essential for totalling by category, date range, or client.
COUNTIFS: Counts records meeting multiple criteria. Useful for counting outstanding invoices or the number of transactions in a category.
IFERROR: Wraps formulas to return a friendly message instead of an error code if something goes wrong.
TEXT: Converts numbers or dates to text in a specific format — useful for building report headings that auto-populate with the correct month and year.
EOMONTH: Returns the last day of a month. Useful for defining date ranges in SUMIFS formulas without hard-coding specific dates.
Good Accounting Habits
The best accounting spreadsheet in the world is useless if you do not keep it up to date. These habits will help:
- Enter transactions weekly: Set aside 20 to 30 minutes each week to record all income and expenses. Do not leave it to pile up at month-end.
- Keep receipts: HMRC requires you to keep records for five years as a self-employed person, or six years as a limited company. Store digital copies using a scanning app and a named folder structure by year and month.
- Reconcile monthly: At the end of each month, compare your expense log total against your bank statement to ensure every transaction is accounted for. This is called bank reconciliation and catches errors or fraud.
- Back up the workbook: Store the workbook on OneDrive for automatic backup, and keep an additional copy on an external drive quarterly.
When to Move to Dedicated Accounting Software
Excel accounting works well up to a point. Consider moving to dedicated software when: you employ staff and need payroll processing, your transaction volume exceeds 200 to 300 entries per month, you need automated bank feeds, or your accountant requests data in a specific format. Until then, a well-maintained Excel workbook is an efficient, cost-effective solution.
Get Started with the Right Tools
Everything described in this guide works in Microsoft Excel as part of Office 2024 Professional Plus or Office 2021 Professional Plus — both available for £29.99 from GetRenewedTech. If budget is a priority, Office 2019 Professional Plus at £22.99 provides the full Excel feature set needed for a complete small business accounting system. A one-time investment in the right software, combined with a well-built spreadsheet, keeps your finances organised and your accountant happy — year after year.
