You don’t need accounting software to keep proper books. If you’re a sole trader, freelancer or small shop, learning how to do bookkeeping in Excel gets you 90% of the way there: clean records, a monthly profit figure and numbers your accountant won’t sigh at.
What you do need is structure. A spreadsheet with no rules turns into a mess by April. One with a few simple rules stays useful for years.
Below is the setup I’d use, step by step, with the formulas that do the heavy lifting. If you’d rather skip the building part, there are two ready-made templates at the end that already do all of this.
Step 1: Separate business money first
This isn’t an Excel step, but nothing else works without it. Open a separate bank account (or at least a separate card) for the business. When personal and business spending share an account, every bookkeeping session starts with “was that coffee a client meeting?” and nobody enjoys that conversation with themselves.
Step 2: Decide your categories
Categories are your mini chart of accounts. Keep them few and clear. A typical small business needs something like:
- Income: product sales, service fees, other income
- Expenses: materials or stock, software and subscriptions, advertising, travel, phone and internet, office costs, bank and payment fees, professional fees, equipment
Put them on a sheet called Settings or Lists. You’ll use this list for dropdowns, so everyone (including future you) spells “Advertising” the same way every time.
Step 3: Build the income and expense logs
Make two sheets, Income and Expenses, each with the same core columns:
- Date
- Description (client name or supplier)
- Category
- Amount
- Payment method or account
- Reference (invoice number or receipt number)
Now two Excel tricks that save hours:
Turn each log into a Table. Click inside the data and press Ctrl+T. Tables grow automatically as you add rows, and formulas can refer to columns by name, like Expenses[Amount], instead of fragile ranges like D2:D500.
Add dropdowns to the Category column. Select the column, go to Data, then Data Validation, choose List and point it at your categories. Typos disappear overnight.
Add a Month column with =TEXT([@Date],”yyyy-mm”) so every row knows which month it belongs to. You’ll need it in the next step.
Step 4: Build a monthly profit and loss
On a new sheet, list your categories down column A and months across row 1. Then one formula fills the grid:
=SUMIFS(Expenses[Amount],Expenses[Category],$A2,Expenses[Month],B$1)
Do the same for income. Add a total row for each block, then Profit = Total Income minus Total Expenses. Copy across twelve months and you’ve got a working P&L.
That’s the moment Excel bookkeeping starts paying off. You can see that advertising jumped in May, or that August income was half of July’s, without opening a single bank statement.
Step 5: Track invoices separately
Income you’ve invoiced isn’t the same as income you’ve received. Keep an Invoices sheet with: invoice number, client, date sent, due date, amount, date paid. Then add a status column:
=IF([@[Date paid]]<>"","Paid",IF(TODAY()>[@[Due date]],"Overdue","Open"))
Add conditional formatting to turn Overdue rows red. Chasing late payers becomes a two-minute job instead of a guessing game.
Step 6: Reconcile with your bank every month
This is the step people skip, and it’s the one that makes your books trustworthy. At month end, compare your spreadsheet’s total for each account with the bank statement. If they don’t match, something is missing or doubled.
The usual culprits: a bank fee you didn’t log, a refund recorded as income, or a transfer between your own accounts logged as an expense. Keep a separate Transfers sheet for money moving between your own accounts, so it never inflates income or expenses.
Step 7: Set money aside for tax
Add a small tax sheet: take profit to date and multiply by the rate you expect to owe. That’s the amount that shouldn’t really be in your spending account. Move it to a separate savings account each month. Your exact rate depends on where you live and how you’re set up, so check with an accountant, but even a rough figure beats a surprise bill.
Step 8: Keep receipts and back up the file
Add a “Receipt?” column to the Expenses sheet with a Yes/No dropdown, and store photos of receipts in a folder named by month. Back the workbook up to cloud storage. A bookkeeping file that lives only on one laptop is one spilled coffee away from disaster.
The shortcut: start from a template
Knowing how to do bookkeeping in Excel from scratch is useful. Building all of it yourself still takes a weekend, plus debugging. A template gives you the same structure on day one.
Clarity Books Easy Bookkeeping covers everything above and then some. It has 13 tabs: a Start Here guide, Setup with editable categories and tax rates, income and expense logs with auto-totals, invoices with automatic Paid/Unpaid/Overdue status, a mileage log, live bank and cash balances, monthly and annual dashboards, plus an automatic Profit & Loss, Balance Sheet, Cash Flow and Tax Summary with quarterly estimates. It works in Excel and Google Sheets and comes with a step-by-step PDF guide.
If you prefer fewer moving parts, the Bookkeeping Spreadsheet Bundle Plan is built on three input sheets: money in, money out and transfers. It pulls those into a running register, a monthly profit and loss, category breakdowns, an invoice tracker showing days overdue, a tax estimate sheet and a dashboard. It opens filled with a sample business, so you can see how it behaves before clearing it out.
FAQ: how to do bookkeeping in Excel
Is Excel good enough for small business bookkeeping?
For most sole traders and small businesses with simple finances, yes. Excel handles income, expenses, invoices and reports well. Once you need payroll, multiple users entering data or stock across sites, dedicated software becomes worth it.
What’s the easiest bookkeeping method for beginners?
Single-entry bookkeeping: one line per transaction, recorded as money in or money out with a category. It’s what most small business spreadsheets use, and it’s plenty for a simple business.
How long does Excel bookkeeping take each week?
Once it’s set up, 15 to 30 minutes a week for most small businesses, plus about half an hour at month end to reconcile and review.
Can I use these formulas in Google Sheets?
SUMIFS, IF and TODAY all work in Google Sheets. Structured table references like Expenses[Amount] work differently there, so use normal ranges or a template that’s already built for both.