How to Build a Monthly Expense Tracker in Excel (Free Template)

This tracker takes one row per purchase and turns it into a month-by-month breakdown of where your money went, with no work on your part beyond typing the spending. It's free, it works in Excel and Google Sheets, and there's no sign-up.

⬇ Download the Monthly Expense Tracker (.xlsx)
Free, no email required. Works in Excel 2016 and later. Prefer Google Sheets? Open a copy in your own Drive.

The rest of this guide explains how it works, so you can change it, fix it, or rebuild it yourself from scratch.

Every cell in the grid is a single SUMIFS — the summary builds itself as you type expenses.

What it does

Three sheets, and you only ever type into one of them.

Sheet	What it's for
Expenses	One row per purchase — date, description, category, amount. This is the only sheet you fill in.
Summary	Every category by month, a yearly total, and how each one compares to its budget. Entirely calculated.
Categories	Your category names and monthly budgets. Editing here updates the drop-down and the summary together.

Yellow cells are yours. Everything else is a formula, and typing over one breaks the totals for that row.

The formula doing all the work

Every cell in the summary grid is a single SUMIFS with two conditions — the category and the month:

=SUMIFS(Expenses!$D$2:$D$301,
        Expenses!$C$2:$C$301, $A4,
        Expenses!$F$2:$F$301, MONTH(B$3))

Read it as: add up the amounts, where the category matches the label on this row, and the month matches the heading on this column.

Two details make it work when dragged across a grid of 120 cells:

• $A4 locks the column but not the row — so it always reads the category from column A
• B$3 locks the row but not the column — so it always reads the month from row 3

That mixed locking is the whole trick behind any cross-tab summary. If you want the underlying function explained properly, see SUMIF and SUMIFS.

The hidden column that makes months work

SUMIFS can't compare a date to a month on its own. Column F in the Expenses sheet solves it:

=IF($A2="","",MONTH($A2))

It turns each date into a plain month number, 1 to 12. The IF wrapper keeps the column blank on empty rows instead of filling three hundred cells with the number 1 — which is what happens if you write =MONTH(A2) on its own.

The summary headers are real dates formatted to show only the month name, so MONTH(B$3) converts them back to numbers on the fly. That's why changing the date in cell B3 rolls the whole sheet to a different year.

The category drop-down

Column C is a drop-down rather than free text, and that isn't decoration. Typed categories are where these trackers fall apart — "Groceries", "groceries " and "Grocery" are three different values to SUMIFS, so your spending quietly splits across categories that look identical.

To rebuild it yourself: select the category column, then Data → Data Validation → Allow: List, and point the source at the category names on the Categories sheet.

Building it from scratch

If you'd rather construct your own than adapt someone else's:

1. Sheet 1 — Categories. Category names in A2 downward, monthly budgets in B.
2. Sheet 2 — Expenses. Headers: Date, Description, Category, Amount, Paid by, Month. Put the MONTH formula in the Month column and fill it down as far as you'll ever need.
3. Add the data validation list to the Category column.
4. Sheet 3 — Summary. Categories down column A (link them to the Categories sheet so renaming works). In B3 put a real date for January; in C3 use =EDATE(B3,1) and fill across to M3. Format the whole row as mmm.
5. Put the SUMIFS in B4 and drag it across and down.
6. Add a total row with SUM, and a budget column that multiplies the monthly figure by 12.

About twenty minutes, and you'll understand every cell in it — which matters the first time something looks wrong.

Using it in Google Sheets

Upload the file to Google Drive, right-click, and open with Google Sheets. Every formula in it — SUMIFS, MONTH, EDATE, IF, data validation — behaves identically. Nothing needs changing.

When the summary shows zero

The category was typed, not chosen

A trailing space is invisible and breaks the match completely. Always use the drop-down. If a row already has a typed value, retype it from the list.

The amount is text

Amounts pasted from a bank statement often arrive as text. A small green triangle in the corner of the cell gives it away. Select the column, then Data → Text to Columns → Finish to convert them.

The date cell isn't a date

If MONTH() returns an error, Excel is reading your date as text. Same fix as above, or retype one date in your system's format to see what it expects.

You added rows below row 301

The formulas cover 300 entry rows. Insert new rows inside that range rather than typing beneath it, and the ranges expand on their own.

Common questions

Can I add more categories?

Yes. Insert rows inside the existing list on the Categories sheet, then add matching rows to the Summary. Inserting inside the range keeps every reference intact; adding below it doesn't.

Can I track more than one year?

Change the date in Summary cell B3 and the twelve months follow. For several years side by side, copy the whole file per year — one sheet holding three years of daily spending gets slow and hard to read.

Can I add income and see what's left?

Add an "Income" category and enter deposits as negative amounts. The category total then shows net position. It's a workaround; a proper budget template separates the two, which is a subject for its own post.

Is my data private?

The file runs entirely on your computer. Nothing is uploaded anywhere, and there's no tracking of any kind inside it.

The short version

• Type into the Expenses sheet only; the rest calculates itself.
• Always pick the category from the drop-down — typed text is what breaks these trackers.
• The summary grid is one SUMIFS with mixed $ locking, dragged across.
• The Month helper column exists because SUMIFS can't read a month out of a date by itself.
• A zero where you expected a number means a text amount or a mistyped category, not missing data.

Next steps: if the summary is behaving oddly, the cause is usually in how SUMIFS matches its criteria. To count transactions rather than total them, COUNTIFS follows the same pattern. And if your imported statement arrives with repeated rows, clean it first — without deleting anything you needed.