A Guide to Excel Accounting and Bookkeeping (and When to Stop Using It)

Spreadsheets get a bad reputation in accounting circles, usually from people selling accounting software. The truth is more nuanced. For a business in its first year, with one bank account and a few dozen transactions a month, a well-built Excel or Google Sheets ledger is not a compromise — it is often clearer, faster and more educational than software, because you can see every calculation instead of trusting a black box.

The failure mode is not the spreadsheet. It is the unstructured spreadsheet: one tab, thirty columns, formulas overwritten with typed numbers, no reconciliation, and a founder who cannot answer "what was your profit in April?" without an hour of scrolling. This guide shows how to build the structured version, run it properly, and recognise honestly when you have outgrown it.

Two Concepts to Settle Before You Type Anything

Cash basis versus accrual basis. Cash basis records income when money lands and expenses when money leaves. Accrual records income when it is earned and expenses when they are incurred, regardless of payment timing. Cash basis is simpler, legal for most small businesses, and works well in a spreadsheet. Accrual gives a truer picture when you invoice on terms or hold inventory, and it becomes mandatory above certain size thresholds. Start on cash basis unless you have a reason not to, and be consistent — mixing the two produces numbers that mean nothing.

Single-entry versus double-entry. Single-entry is a running list of transactions, like a chequebook. Double-entry records every transaction twice, as a debit and a credit, so the books self-check. Spreadsheets do single-entry naturally and double-entry awkwardly. For a small service business, single-entry with disciplined reconciliation is entirely adequate.

The Six-Tab Structure

Almost every workable spreadsheet ledger has the same skeleton. Build these six tabs and resist the urge to add more.

Tab 1 — Transactions. The heart of the system, and the only tab where you type raw data. Columns: Date, Description, Category, Type (income or expense), Amount, Payment Method, Reference/Invoice Number, Receipt Link, and Notes. One row per transaction, no blank rows, no merged cells, no subtotals in the middle. This is a database, and treating it like one is what makes everything downstream possible.

Tab 2 — Chart of Accounts. A simple two-column list of the categories you allow: Revenue, Cost of Goods Sold, Contractors, Software, Advertising, Professional Fees, Bank Charges, Travel, Home Office, and so on. Keep it under twenty-five lines. Then use Data Validation on the Category column in Tab 1 to pick only from this list. Free-typing categories is the single most common reason spreadsheet books become unusable — "Software", "software", "SaaS" and "Subscriptions" become four categories that should have been one.

Tab 3 — Monthly Summary. No typing here, only formulas. Rows are your categories, columns are the twelve months, and each cell is a SUMIFS that pulls from Transactions. This is where your profit and loss statement lives.

Tab 4 — Invoices. Invoice number, client, issue date, due date, amount, amount paid, status, and a formula for days overdue. This is your accounts receivable, and it is the tab that gets you paid. If you would rather generate the documents themselves properly, our free invoice generator produces numbered, compliant invoices you can log here.

Tab 5 — Reconciliation. Month, closing balance per your bank statement, closing balance per your ledger, and the difference. The difference should be zero. When it is not, you find out this month, not next April.

Tab 6 — Tax Set-Aside. Monthly profit, your reserve percentage, the amount to transfer, and a running balance of tax money held. Simple, and it prevents the most common cash flow disaster in small business.

The Formulas That Do Ninety Percent of the Work

You do not need to be an Excel power user. Five functions carry the whole system.

SUMIFS totals amounts matching multiple conditions — the backbone of your monthly summary. To total software expenses in March: sum the Amount column where Category equals "Software" and Date falls within March. Build it once, drag it across.

SUMPRODUCT handles the date-range logic elegantly when you want month totals without adding helper columns, though a helper column with =TEXT(date,"YYYY-MM") is often simpler and easier to debug.

XLOOKUP (or VLOOKUP in older versions) pulls client details, tax rates or category groupings from reference tables so you type them once rather than repeatedly.

IFERROR wraps anything that might break, so a missing value shows a clean zero rather than a wall of #N/A that makes the sheet look broken.

Conditional formatting is not a formula, but it does more for usability than any of them. Red for overdue invoices, amber for uncategorised transactions, green for reconciled months. You will spot problems by glancing rather than reading.

Two structural tips that matter more than any formula: format your Transactions tab as a proper Table (Ctrl+T) so formulas expand automatically as you add rows, and use named ranges for anything you reference repeatedly. Both prevent the slow decay that kills most spreadsheets by month eight.

The Monthly Routine

A spreadsheet ledger works only if it is run on a rhythm. Ours takes about an hour.

Download the month's bank and card statements as CSV. Paste new transactions into the Transactions tab, keeping the column order consistent. Categorise everything — every row gets a category from the validated list, with no blanks. Attach receipts by pasting a cloud storage link into the Receipt Link column; do not embed images in the sheet, it will become unopenable. Reconcile: compare your ledger's closing balance to the bank statement and investigate any difference immediately. Review the Monthly Summary and note the profit. Update the Tax Set-Aside tab and actually make the transfer. Chase anything on the Invoices tab that is past due.

Then — and this genuinely matters — save a dated copy. Spreadsheets have no audit trail. A monthly snapshot in cloud storage is your only protection against a formula being overwritten in June and nobody noticing until December.

What Spreadsheets Cannot Do

Being honest about the limits keeps you from finding them at the worst moment.

There is no audit trail. Any cell can be changed silently by anyone with access, and nothing records who did it or what it was before.

There is no error checking. Single-entry books do not self-balance, so a transposed digit sits there quietly until reconciliation catches it — assuming you reconcile.

There is no multi-currency handling worth the name. Once you invoice in two or three currencies, exchange-rate gains and losses need proper treatment, and formulas quickly become guesswork.

There is no sales tax or VAT engine. Manually tracking VAT-inclusive and VAT-exclusive amounts across hundreds of transactions is where spreadsheet bookkeeping most reliably breaks. If you are approaching registration thresholds — UK VAT at £90,000, or US state sales tax nexus — plan the transition now.

There is no collaboration safety. Two people in one workbook eventually means one person's work is overwritten.

And there is no automatic bank feed. Manual CSV imports mean manual gaps, and gaps mean reconciliations that never quite balance.

The Signals It Is Time to Move On

Move to proper accounting software when any two of these are true: more than about a hundred transactions a month; more than one currency; VAT, GST or sales tax registration; inventory you need to value; payroll for anyone other than yourself; outside investors, a lender or a board asking for statements; more than one person needing to enter transactions; or a reconciliation that has been "nearly right" for two months running.

Most businesses hit that point somewhere between the end of year one and the middle of year two. Migrating is far easier if your spreadsheet was well structured, because a clean Transactions tab with a controlled category list imports almost directly. That is the quiet argument for building it properly even if you know you will leave it behind.

Common Spreadsheet Mistakes We Untangle Every Month

When founders send us a year of spreadsheet books to clean up before a tax filing, the same faults appear again and again, and every one of them is preventable.

Typing over formulas. Someone needs a number to "look right", so they type it into a cell that used to calculate. From that point the sheet lies quietly. Protect your summary tabs so only the Transactions tab accepts typing.

Storing amounts as text. Numbers pasted from a bank portal often arrive as text with currency symbols or trailing spaces, and SUMIFS silently ignores them. Your totals are then wrong by exactly the amount you cannot see. Run the imported column through Text to Columns once per import and format it as currency.

Recording payouts instead of gross sales. A Stripe or Shopify payout is already net of fees, refunds and sometimes collected tax. Recording only the payout understates both your revenue and your deductible fees, which usually costs you money at filing time. Record the gross sale and the fee as two separate lines.

Duplicating transactions after a re-import. Importing an overlapping date range twice inflates both revenue and expenses. Keep a bank reference or transaction ID column and use conditional formatting to highlight duplicates on entry.

No opening balance. A ledger that starts mid-year without the opening bank balance will never reconcile, and people spend hours hunting a discrepancy that was there on day one.

Personal transactions "to be sorted later." Later never arrives. Categorise them as owner draws immediately, so the profit figure stays honest all year.

A Simple Worked Example

Say you are a consultant in month four. You raised three invoices totalling $9,400, two of which have been paid ($6,900 received). You spent $180 on software, $240 on a co-working desk, $310 on a subcontractor, and $95 on client travel.

Your Transactions tab has seven rows: two income rows totalling $6,900 and five expense rows totalling $825. Your Monthly Summary shows revenue of $6,900, expenses of $825 and profit of $6,075 on a cash basis. Your Invoices tab shows $2,500 outstanding with a due date next week, flagged amber. Your Tax Set-Aside tab, at 28 percent, tells you to move $1,701 to the reserve account. Your Reconciliation tab compares the ledger's closing balance against the bank statement and shows a difference of zero.

That is the entire month, and it took under an hour. Note what the system just told you without being asked: your real profit, your outstanding cash, your tax liability, and confirmation that nothing is missing. A single-tab list of expenses tells you none of those four things.

A Middle Path Worth Considering

There is a third option between "spreadsheet forever" and "buy software and learn accounting." Keep a simple spreadsheet for your own visibility — cash position, invoices outstanding, tax reserve — and hand the actual books to someone who does them for a living. You keep the numbers you look at weekly; they keep the ledger that has to be defensible.

That is how most of the founders we work with end up operating. Bizvee's bookkeeping service delivers reconciled monthly accounts and year-end-ready books, our invoicing service keeps receivables from drifting, and if you are selling online, our ecommerce accounting team handles the platform-fee and multi-channel mess that spreadsheets handle worst.

Whichever route you take, the principle is the same one that runs through all good bookkeeping: the tool matters far less than the rhythm. A modest spreadsheet reconciled every month beats sophisticated software opened twice a year, every single time.