Monthly Expense Tracker Spreadsheet: A Layout You Can Build

A useful monthly expense tracker for a business is a spreadsheet with three tabs: a transaction log with one row per income or expense item, a fixed list of categories, and a monthly summary that totals each category by month with a SUMIFS formula. Build those three pieces correctly and you have an income and expense worksheet that shows profit by month, feeds your tax return and takes about ten minutes to set up in Excel or Google Sheets.

There is no file to download here. Instead, below is the exact layout, column by column, so you can build it yourself and understand every formula in it.

Tab 1: the transaction log

This is where every transaction goes, one per row, in date order. Put the headings in row 1 and never merge cells or leave blank rows in the middle; formulas depend on a clean, continuous list.

• Column A — Date. The transaction date, entered as a real date (format the column as a date) so formulas can read it.
• Column B — Month. A helper column with the formula =TEXT(A2,"yyyy-mm"), copied down. It turns 15/01/2026 into 2026-01, which is what the summary tab groups by.
• Column C — Type. Income or Expense, chosen from a dropdown.
• Column D — Payee or customer. Who you paid or who paid you.
• Column E — Description. A few words you will still understand in a year.
• Column F — Category. A dropdown that pulls from the Categories tab (Data → Data validation → List from a range). Dropdowns stop the same cost being typed three different ways.
• Column G — Payment method. Bank transfer, business card, cash, personal card.
• Column H — Net amount. The amount before any VAT, GST or sales tax you can reclaim. Numbers only, no currency symbols typed in.
• Column I — Tax. The reclaimable tax on the invoice, if you are registered. Leave it zero otherwise.
• Column J — Total. =H2+I2. This should equal what left or reached the bank.
• Column K — Business use %. 100% for most items; less for mixed-use costs such as a phone you also use personally.
• Column L — Business amount. =H2*K2. This is the figure the summary adds up.
• Column M — Receipt reference. The file name of the saved receipt, for example 2026-01-15_ssd_180.pdf.
• Column N — Reconciled. Tick it once you have matched the line to your bank or card statement.

Format the whole range as a table (Insert → Table in Excel; Format → Convert to table in Google Sheets) so new rows pick up the formulas automatically.

Tab 2: the category list

Keep categories in a single column on their own tab, income categories first. Fewer, well-defined categories beat dozens of vague ones. A starting list for a small service business:

• Income: client fees, product sales, other income
• Advertising and marketing — ads, printing, sponsorships
• Bank and payment fees — account charges, card processing fees
• Contractors — freelancers and subcontractors
• Equipment — tools and devices below your capitalisation threshold
• Insurance — business liability, equipment cover
• Production costs — materials and services that go directly into client work
• Professional fees — accountant, lawyer
• Rent — office, studio or coworking
• Software and subscriptions
• Phone and internet
• Travel — fares, accommodation
• Vehicle and mileage
• Meals and entertainment — kept separate because tax rules often restrict them
• Training and memberships
• Other — review this every month and move items to a proper category

If you file a tax return with specific expense lines, rename your categories to match them. Year-end then becomes a copy-and-paste job rather than a re-sort.

Tab 3: the monthly summary (income and expense worksheet)

Lay this out as a grid:

• Column A — every category, income categories at the top.
• Row 1, columns B to M — the months as text: 2026-01, 2026-02 and so on to 2026-12. Type them as text (start each with an apostrophe so the spreadsheet does not turn them into dates) so they match the helper column exactly.
• Column N — full-year total, =SUM(B2:M2).

In cell B2, enter this formula and copy it across and down the grid:

• =SUMIFS(Log!$L:$L, Log!$F:$F, $A2, Log!$B:$B, B$1)

It adds up the business amount for rows where the category matches the row label and the month matches the column heading. The dollar signs lock the right parts when you copy, and the formula works the same in Excel and Google Sheets. Rename Log to whatever you called your first tab.

Below the grid, add three rows: total income (a SUM of the income rows), total expenses (a SUM of the expense rows) and net profit (income minus expenses). Optionally add an expenses-as-percentage-of-income row, and a budget column next to each month if you want to track against a plan.

Worked example: January for a freelance motion designer

Kestrel Studio is a one-person motion-graphics business working from a coworking desk. Here are its January log entries, shown as date, description, category and business amount:

• 2 Jan — coworking desk — Rent — 320.00
• 3 Jan — animation software subscription — Software and subscriptions — 60.00
• 5 Jan — stock footage pack for a client job — Production costs — 145.00
• 9 Jan — Brightwater Hotels pays invoice — Client fees (income) — 4,200.00
• 12 Jan — train fare to a client shoot — Travel — 86.00
• 15 Jan — 2 TB external drive — Equipment — 180.00
• 20 Jan — mobile phone bill of 50.00 at 70% business use — Phone and internet — 35.00
• 24 Jan — Orchard Lane Bakery pays invoice — Client fees (income) — 1,600.00
• 28 Jan — monthly bookkeeping fee — Professional fees — 150.00
• 31 Jan — bank account fee — Bank and payment fees — 12.00

The summary tab's 2026-01 column then shows:

• Client fees — 5,800.00
• Rent — 320.00; Software — 60.00; Production costs — 145.00; Travel — 86.00; Equipment — 180.00; Phone and internet — 35.00; Professional fees — 150.00; Bank fees — 12.00
• Total expenses — 988.00
• Net profit for January — 4,812.00
• Expenses as a share of income: 988 ÷ 5,800 = about 17%

One check before you trust the numbers: the phone bill shows 35.00 in the business amount column but 50.00 left the bank. Your bank reconciliation should use the total column, and only the summary uses the business amount. Mixing the two is the most common reason a spreadsheet will not agree with the bank.

Habits that keep the tracker accurate

• Enter weekly, reconcile monthly. Tick off each row against the bank and card statements at month-end; anything on the statement without a row is a missing entry.
• Save every receipt with a consistent name (date, payee, amount) in one folder per month, and put that name in column M.
• Keep personal spending out. If a personal card paid for a business cost, log it with payment method personal card so you know to reimburse yourself.
• Protect closed months. Once a month is reconciled, protect those rows (Review → Protect in Excel; Data → Protect sheets and ranges in Google Sheets) so a stray edit cannot change it.
• Keep records long enough. Retention rules depend on your tax authority. In the US, for example, the IRS's general guidance is three years from filing, longer in some situations.

Where a spreadsheet starts to struggle

A spreadsheet works well for a handful of transactions a week. It gets fragile when you have dozens: every row is typed by hand, a formula dragged one row short silently drops a transaction, there is no record of who changed what, and it cannot tell you which customers owe you money. If you find yourself spending more time maintaining the tracker than reading it, that is the signal to move to accounting software.

hellobooks< / a > -takes-over-the-manual-parts">How HelloBooks takes over the manual parts

HelloBooks does the same job as this spreadsheet without the typing. Bank feeds (via Plaid in the US, Canada, the UK and the EU) bring transactions in automatically, bank rules and AI auto-categorisation suggest the category for each one for you to approve, and document extraction reads a dropped-in receipt or PDF, pulling out vendor, amount and tax and matching it to the bank line. Bank reconciliation replaces the ticked column, and the audit trail records every change.

The monthly summary becomes the profit and loss report, which you can run by month, and budget versus actual replaces the budget column. In the US, a built-in mileage tracker keeps an IRS-compliant log. If you still like working in a spreadsheet, CSV export gives you the data to analyse in Excel or Google Sheets. There is a free plan, so you can try it alongside your existing tracker for a month and compare the totals.

This is general information as of November 2026, not tax or legal advice; check the official source or a qualified CA/CPA for your situation.