Accounting in Excel: how far a spreadsheet goes, and the wall it hits
Using Excel as accounting software is not a shortcut or a sign that you are doing it wrong. For a sole trader or a small business on a cash basis, a well-built spreadsheet is a legitimate set of books and has been for decades.
It also has a hard ceiling, and unlike most spreadsheet limits this one is not about size or speed. It is structural, it arrives suddenly, and no amount of formula work gets you past it.
This page covers a cash book worth copying, the exact point where Excel stops being viable, and the three ways out.
Is Excel accounting software, or a template pretending to be one?
Search for free accounting software in Excel and you get two different things wearing the same label, and the difference decides whether it will hold up.
A template is a structured workbook — sheets, named ranges, formulas, maybe a pivot for reports. It is transparent, you can see every calculation, and you can change anything. It is also entirely dependent on the discipline of whoever uses it.
A macro-driven workbook adds VBA to automate posting, generate invoice PDFs, or produce reports at the press of a button. These are marketed as accounting software in Excel, and some are genuinely capable. They also carry real costs: macros are blocked by default in most modern setups and increasingly restricted by IT policy; they frequently break between Excel versions and rarely work in the web or Mac versions; and you cannot see what the code does, which is an odd property for something holding your books.
If you are choosing a free Excel accounting template, three checks are worth more than the feature list:
- Are the reports formulas or values? Open a report cell. If it contains a number rather than a formula, somebody is typing results in and the report can disagree with the data.
- Is there a single transactions table, or one sheet per month? Twelve monthly sheets is the most common design and the worst one — every annual report becomes a formula spanning twelve ranges, and it breaks the first time somebody inserts a row.
- Can you add a category without editing formulas? If categories are hard-coded into SUMIF arguments rather than driven from a settings sheet, the template will fight you by month three.
The structure below avoids all three problems and takes about twenty minutes to build.
A cash book that works
Most free accounting Excel templates are one sheet with income above and expenses below. That works until the first question anyone asks about it.
Use four sheets instead.
Settings — the things every other sheet references. Your categories, tax rates, and the accounts money moves through (business current account, cash tin, card processor). Keeping these in one place is what stops the same category being spelled three ways by March.
Transactions — one row per movement of money, and the only sheet you type into daily:
| Date | Description | Category | Account | Money in | Money out | Tax | Ref |
|---|---|---|---|---|---|---|---|
| 2026-04-02 | Invoice 1041 — Harper Ltd | Sales | Current | 1,450.00 | 290.00 | INV-1041 | |
| 2026-04-03 | Toolstation — consumables | Materials | Card | 86.40 | 14.40 | R-2291 | |
| 2026-04-05 | Fuel | Travel | Card | 62.00 | 10.33 | R-2295 |
Invoices — one row per invoice issued, with its own status: raised, sent, paid, overdue. This is separate from Transactions on purpose, because an invoice is a promise and a payment is an event, and conflating them is the single most common spreadsheet bookkeeping error.
Reports — derived only. Never type here.
Income this quarter =SUMIFS(Transactions[Money in], Transactions[Date],">="&StartDate,
Transactions[Date],"<="&EndDate)
Outstanding invoices =SUMIFS(Invoices[Total], Invoices[Status],"<>Paid")
Balance per account =SUMIFS(Transactions[Money in], Transactions[Account],[@Account])
-SUMIFS(Transactions[Money out], Transactions[Account],[@Account])
That gives you a profit figure, a cash position per account, an aged debtors list and a tax total. For a lot of small businesses, that genuinely is enough.
The wall: single entry versus double entry
Here is the thing that is not a size limit and cannot be solved with a better template.
What you built above is single-entry bookkeeping. Each transaction is one row: money came in, or money went out. It answers "how much did I make" and "how much is in the bank".
Double-entry records every transaction twice — as a movement between two accounts. A sale is not just money in; it is revenue up and a debtor created, then later the debtor cleared and the bank up. Every entry has two sides and the sides must balance.
You cannot produce a balance sheet without it. Not "it is difficult in Excel" — the information is simply not there. A single-entry cash book has no record of what you own and owe, only what moved. The same goes for anything derived from a balance sheet: your true asset position, liabilities, retained earnings, and equity.
This matters at specific, predictable moments:
- Your accountant asks for a trial balance and you have nothing to give them, so they rebuild your year from source documents and bill you for it.
- You incorporate, and a limited company generally must file accounts that include a balance sheet.
- You seek finance. No lender or investor works from a cash book.
- You carry stock, and stock is an asset that a cash book cannot represent — so your profit figure is wrong all year and only corrects at a stocktake.
- You accrue anything — deferred revenue, prepayments, work in progress. Cash accounting shows the money and hides the obligation.
The uncomfortable version: if any of those apply, your spreadsheet has been giving you a number that feels like profit and is not.
The other four failures
Beyond the structural one, Excel accounting fails in four practical ways.
Bank reconciliation is manual and therefore skipped. Reconciling means proving your books agree with the bank, line by line, every month. In a spreadsheet this is a person comparing two lists. Because it is tedious it gets deferred, and errors compound silently until year end.
Nothing is immutable. Anyone can edit a posted transaction from eight months ago, and the change leaves no trace. Real accounting systems lock periods for exactly this reason. If you are ever audited or investigated, "the file could have been edited by anyone at any time" is not a strong position.
Tax gets harder, structurally. Digital tax filing regimes increasingly require submission from compatible software with a digital audit trail from record to return. Retyping totals from a spreadsheet into a portal is the thing these rules are designed to eliminate.
It does not survive other people. Two-person editing has the same conflict problem as any shared sheet — except here the silently-lost row is a transaction, and you will not notice until the bank does not reconcile.
Tax and VAT: where the spreadsheet gets expensive
The tax column in the transactions table above is doing more work than it looks, and it is worth being precise about what it can and cannot support.
It handles a simple return adequately. If you are on a flat scheme or a straightforward cash basis, summing a tax column by period gives you the figure you need, and thousands of businesses file correctly from exactly that.
It stops handling three things well, all at once, and usually the same year:
- Mixed rates and partial recovery. Once some purchases are partly recoverable, or you have zero-rated alongside standard-rated sales, a single tax column stops being sufficient and people start keeping a second sheet — which is the point at which the numbers begin to disagree.
- The cash-versus-accrual switch. These calculate tax at different moments. A spreadsheet built around payments cannot be re-cut onto an invoice basis without re-entering the year.
- The digital audit trail. Modern filing rules increasingly want an unbroken digital link from the underlying record to the submitted return. Copying a total from a spreadsheet cell into a web form is precisely the manual step those rules exist to remove.
The practical signal: if you find yourself keeping a second workbook to work out the first one's tax figure, the spreadsheet has already ended and nobody has said so out loud.
What real accounting tools actually implement
We maintain a hand-counted inventory of 37 real accounting and invoicing products. Ranked by how often each capability appears:
| Capability | Found in |
|---|---|
| Income and expense ledger — works without invoices, categorised | 31 of 37 (84%) |
| Organisation setup — currency, fiscal year, tax basis, numbering | 30 of 37 (81%) |
| Financial reports — P&L, cash flow, AR/AP aging (balance sheet needs double entry) | 30 of 37 (81%) |
| Invoice builder — line items, taxes, discounts, PDF, reminders | 29 of 37 (78%) |
| Customers and clients | 28 of 37 (76%) |
| Payments received and allocation — including partial payments | 28 of 37 (76%) |
| Document numbering and PDF templates — durable, concurrency-safe | 27 of 37 (73%) |
| Bank and cash accounts | 26 of 37 (70%) |
| Dashboard — charts that drill to source records | 25 of 37 (68%) |
Two details worth pulling out.
The ledger outranks the invoice builder. Most people think of accounting software as invoicing with extras. The market disagrees: the income and expense ledger is the most implemented capability, and it has to work for businesses that never raise an invoice at all.
Document numbering appears in nearly three quarters and is the capability nobody thinks to ask about. Invoice numbers must be durable, sequential and safe under concurrent use — a duplicated or skipped invoice number is a real problem at audit, and it is the single most common defect in self-built and cheap systems alike.
Your three options
Stay on the spreadsheet
Correct if you are a sole trader on a cash basis, VAT-unregistered or filing simply, with no stock and no accruals. Restructure into the four sheets above, back it up somewhere versioned, and get on with your work. This is a real answer, not a consolation prize.
Move to accounting software
The right answer for most businesses past that point, and the entry-level products are inexpensive relative to what an accountant charges to fix a year of cash book. You get double entry, bank feeds, reconciliation, locked periods and a compliant filing path.
The cost is fit: you adopt their chart of accounts, their invoice model, their idea of a project or a job. For most businesses that is fine and the standardisation is a benefit.
Two trades where it reliably is not: contractors, whose jobs need cost categories, retentions and committed cost that no general ledger carries — construction accounting software — and nonprofits, where money arrives with conditions attached and the balance that matters is per fund rather than per account — nonprofit accounting software.
Build it
Two years ago this was a bad idea for anyone without a finance-software budget. A coding agent can now produce a working bookkeeping system in a couple of days, which changes the arithmetic for businesses whose operation genuinely does not fit the products — unusual revenue recognition, job-costing across trades, multi-entity structures, industry-specific tax handling.
One warning, and it is the important part of this page. Left to guess, a coding agent builds exactly the system described at the top of this article: a transactions table with money in and money out. Single entry, with a nice interface. It will produce a P&L and it will not be able to produce a balance sheet, and by the time anybody notices, every transaction in the system is in the wrong shape.
Double entry cannot be retrofitted. It is not a feature you add later; it is the shape of the data. If you build accounting software, the ledger is the first decision and the one that has to be right before anything else is written.
The order that follows from the counted inventory: the double-entry ledger first, then organisation and fiscal settings, then reports derived from the ledger rather than from parallel totals, then invoicing as a branch on top — not the other way round. Durable, concurrency-safe document numbering belongs in the first pass, because retrofitting sequence guarantees to a system already issuing invoices is genuinely unpleasant.
If you are building, the Accounting & Invoicing planner is the specification we hand our own coding agents — the ledger shape, the fiscal settings, the numbering guarantees and the scope boundaries, written so an agent cannot quietly ship you a cash book with a dashboard on it.