How to Do Bookkeeping in Excel (The Practical Guide)

Practice Runbook · updated 2026-09-25

Bookkeeping in Excel remains one of the most practical ways to track business finances, especially for solo practitioners and small businesses. While dedicated accounting software offers automation, Excel gives you complete control and visibility into every transaction.

Why Excel for bookkeeping?

Excel works when you need full customization, when your transaction volume is under ~200 entries per month, or when you're bootstrapping and cannot justify subscription software yet. Many bookkeepers prefer it for client work because it's universal — every client already has it, and you can hand off a workbook without requiring them to purchase software.

The real advantage is transparency. Every formula is visible, every calculation can be verified, and your client sees exactly how their numbers connect. That trust matters more than speed in the first year of a practice.

The five sheets you actually need

A working bookkeeping system in Excel needs five interconnected sheets:

1. Transactions — the source of truth. Date, description, category, amount (in/out), running balance. Use Data Validation for categories so entries stay consistent.
2. Chart of Accounts — the category list. Name, type (Asset/Liability/Income/Expense), and the account code if you follow a standard numbering system. This drives the dropdown in your Transactions sheet.
3. Income Statement — revenue minus expenses for a chosen period. Built with SUMIFS that filter Transactions by category and date range. Month-to-date and year-to-date columns let you spot trends.
4. Balance Sheet — what you own, what you owe, and equity. Assets = Liabilities + Equity must always balance (the accounting equation). Most small businesses can skip detailed depreciation at the start.
5. Cash Flow — where money actually moved. Operating/Investing/Financing sections. This is what tells you whether the business can pay its bills, which profit alone does not.

Common Excel bookkeeping mistakes

Not reconciling the bank — your ending Excel balance and your bank statement must match. A discrepancy means a missing transaction, a duplication, or a keying error. Reconcile monthly, not when tax season arrives.

Mixing personal and business — track only business transactions in the business workbook. Personal draws go on the Equity side, not as expenses.

No backup — Excel files corrupt. Keep three: working copy, yesterday's version, and last month's version. Cloud sync is not a backup if you overwrite a good file with a broken one before noticing.

Formula errors that silently break — when you insert rows, cell references can shift and break your sums. Use structured references (Excel Tables) or named ranges instead of bare cell addresses, and verify your totals after every structural change.

When to stop using Excel

Move to dedicated software when:

• You hit 200+ transactions per month and data entry feels like a second job
• You need to generate invoices or accept online payments
• You have inventory to track (Excel works, but software prevents stock-out mistakes)
• You bring on a team and need role-based access
• Bank feed automation would save you more than the subscription costs

Excel is not wrong at that point — it's just no longer the right tool for the volume.

Start with a tested workbook

Building a bookkeeping system from scratch takes days of formula debugging and chart-of-accounts research. The Bookkeeping Practice Kit includes a ten-sheet Excel workbook with formulas already verified, plus onboarding documents and a month-end close checklist. It's designed for solo bookkeepers running their own practice, not generic small-business templates.

If you just need the close checklist, grab the free Month-End Close Card first.