If you run a small business and your expense records live in a shoebox, a wallet, and three different email accounts, a simple Excel template can fix that in an afternoon. You do not need accounting software on day one. You need a consistent place to record what you spent, when, on what, and which tax category it belongs to.
This guide walks through a template layout that holds up in practice: the columns to include, the formulas that do the math for you, and the category structure that makes your tax preparer’s job easier in April.
Why a Spreadsheet Still Makes Sense
Dedicated tools exist, and good ones. Wave offers free small-business accounting with bank connections, and Expensify is built around receipt capture. But a spreadsheet has three advantages that keep it relevant:
- Total control. You decide the columns, categories, and reports. No app forces its structure on you.
- Zero cost and zero lock-in. Your data is a file you own. If you switch to accounting software later, you can import it.
- It matches how taxes actually work. If you file a Schedule C in the US, your categories can mirror the form’s expense lines directly, which saves real time at filing.
The tradeoff is manual entry. If you have hundreds of transactions a month, a spreadsheet becomes a chore and software earns its keep. For a business with 20–80 expenses a month, Excel is comfortable.
The Column Layout That Works
Here is a layout that covers what a small business actually needs — enough detail to answer tax questions later, not so much that you stop filling it in. Put this on a sheet named Expenses, one row per transaction.
| Column | What goes in it | Format / formula notes |
|---|---|---|
| A: Date | Transaction date | Date format; sort by this column |
| B: Vendor | Who you paid | Plain text, consistent spelling |
| C: Description | What it was for | One short line |
| D: Category | Tax-aligned category | Dropdown via Data Validation |
| E: Amount | What you paid | Currency format |
| F: Payment method | Card, checking, cash, personal (reimbursable) | Dropdown |
| G: Receipt? | Yes / No / Digital | Dropdown — flags missing documentation |
| H: Month | Auto-derived for reporting | =TEXT(A2,"YYYY-MM") |
Columns A through E are the core record. F matters if you ever pay for business items from a personal card and need to reimburse yourself cleanly. G is your audit-anxiety reducer: filter on “No” once a month and chase the stragglers while the memory is fresh. H exists purely to make monthly summaries trivial.
Format the whole thing as an Excel Table (select the range, press Ctrl+T). Tables auto-extend formatting and formulas to new rows and let you reference columns by name.
Categories: Match Your Tax Return, Not Your Mood
The most common template mistake is inventing categories on the fly — “Software,” “Apps,” “Subscriptions,” and “Tools” all meaning the same thing. Three months later your totals are useless.
Instead, start from the expense lines your tax return already uses. For a US sole proprietor filing Schedule C, that means categories like advertising, office expense, supplies, travel, meals, utilities, insurance, legal and professional services, and car/truck expenses. Add a few of your own where your business genuinely needs the detail (a designer might split “Software — design” from “Software — admin”), but keep the list under 20 items.
Put the category list on a second sheet named Lists, then use Data Validation (Data tab → Data Validation → List) to turn column D into a dropdown. This one step eliminates typos and makes every formula below reliable.
Formulas That Do the Heavy Lifting
Create a third sheet named Summary. These formulas assume your data lives in an Excel Table named tblExpenses.
Total spent by category:
=SUMIF(tblExpenses[Category], A2, tblExpenses[Amount])List each category down column A of the Summary sheet, and this formula gives you a running year-to-date total per category — exactly the numbers your tax preparer will ask for.
Spending by category per month:
=SUMIFS(tblExpenses[Amount], tblExpenses[Category], $A2, tblExpenses[Month], B$1)With categories down the side and months (2026-01, 2026-02, …) across the top, this builds a full monthly grid. Now you can see at a glance that, for example, your software spending doubled in March and decide whether that was intentional.
Monthly burn rate (average monthly spend):
=SUM(tblExpenses[Amount]) / COUNTA(UNIQUE(tblExpenses[Month]))Count of transactions missing receipts:
=COUNTIF(tblExpenses[Receipt?], "No")None of these require macros or add-ins, and all of them update automatically as you add rows.
A Monthly Routine That Keeps It Honest
A template only works if the data goes in. A workable rhythm:
- Weekly (5 minutes): enter anything paid by cash or personal card while you still remember what it was.
- Monthly (20 minutes): download your business bank and card statements as CSV, paste new transactions in, categorize them, and mark receipt status.
- Quarterly: review the Summary grid. Look for categories drifting upward and for vendor charges you no longer recognize — recurring software charges for tools nobody uses are the classic find.
The monthly CSV step is the honest answer to “automation” in Excel: banks won’t push transactions into your spreadsheet, but nearly all of them let you pull a clean export.
When to Graduate to Real Software
A spreadsheet stops being the right tool when any of these become true: you invoice clients and need to track receivables, you have employees or contractors to pay, you handle sales tax, or your transaction volume makes monthly entry take more than an hour. At that point, move to a proper small-business tool — Wave is a solid free starting point, and Expensify shines if receipt volume is your main pain. Your Excel history imports as CSV, so nothing you build today is wasted.
Until then, a clean template with tax-aligned categories and a handful of SUMIF formulas gives you 90% of the benefit at 0% of the cost. Build it once, spend twenty minutes a month feeding it, and next tax season becomes an export instead of an archaeology dig.