Monthly expense spreadsheet: how to set one up, and when to switch to an app

An expense spreadsheet is the cheapest way to find out where your money goes: nothing to install, and you can set it up in ten minutes. The problem is that almost all of them die the same way, with too many columns, too many tabs and a summary that has to be rebuilt by hand every month. This is the minimal version that lasts.

One sheet, one row per expense

The most common mistake is making one tab per month. It looks tidy, but every new month means copying the sheet, fixing the formulas and, by March, giving up. Use a single sheet with every expense one below the other; the month comes from the date.

You need six columns:

1. Date. The day you spent the money, not the card statement date.
2. Description. Short: "Groceries", "Fuel", "Jo's birthday".
3. Category. From a fixed list, always spelled the same way.
4. Amount. Just the number, no currency symbol.
5. Payment method. Cash, debit or which card.
6. Who paid. Only if you share it with someone.

If you're tempted to add a seventh, ask yourself whether you'll fill it in on the days you're in a hurry. If the answer is no, don't add it.

Categories: few and fixed

Six to ten is enough: groceries, home (rent, bills, utilities), transport, going out, health, shopping, subscriptions and "other". Put the list on another sheet and use data validation so the Category column is a drop-down: "Groceries", "groceries" and "Grocery" are three different categories to any formula, and that's the number one reason summaries come out wrong.

The summary that builds itself

On another sheet, put the categories in one column and this formula next to them, which adds up one category's amounts for one month:

=SUMIFS(Expenses!D:D, Expenses!C:C, A2, Expenses!A:A, ">="&B$1, Expenses!A:A, "<"&EOMONTH(B$1,0)+1)

Where A2 is the category and B1 is the first day of the month you want to see. Change the date in B1 and the whole summary recalculates. Depending on your regional settings, the separators may be semicolons instead of commas.

The habit matters more than the spreadsheet

No formula fixes a spreadsheet nobody fills in. Pick a fixed moment of the day —at night, before bed— and log everything at once: it takes two minutes. We go into more detail in how to start tracking your spending and not quit after a week.

Three signs your spreadsheet has outgrown you

• You share it. Two people editing the same file end up stepping on each other, and working out who owes whom with formulas is fragile. If that's you, see how to split expenses as a couple.
• You have instalments. A purchase in 12 instalments is 12 rows you have to remember to add, or a formula nobody understands two months later.
• You log from your phone. Spreadsheets on a phone are awkward, and the awkward thing is the first thing you stop doing.

If you already have a spreadsheet, don't start from scratch

Expenses does the same as this spreadsheet —fixed categories, a monthly summary, shared expenses and instalments spread out automatically— from your phone and without formulas. If you've been tracking already, save the spreadsheet as CSV and import it by choosing which column is the date and which is the amount. It's free and doesn't ask for card or bank details.