How to set up an expense tracker you will actually keep using

An expense tracker is any system that records what you spend so you can see where your money goes. The simplest one is a spreadsheet, and you can build it in half an hour. You need four columns and one formula. This guide walks through it, and then looks at when a spreadsheet stops being enough.

What you need before you start

Just two things: last month's transactions from your bank and a short list of categories. The list matters more than it seems. Eight to ten categories are enough, for example housing, food, transport, utilities, health, entertainment, subscriptions and other. Create thirty and you will stop using them.

Step 1: create the columns

Open a new spreadsheet and type these headings in the first row:

A	B	C	D
Date	Description	Category	Amount

Enter one expense per row, with the amount as a positive number. If you also want to track income, use a separate sheet: mixing the two in one column makes the totals easier to get wrong.

Step 2: add a category drop-down

Typing categories by hand ends badly, because a single typo drops a purchase out of your totals. To avoid that:

1. Create a sheet called Categories and type your list in column A.
2. Go back to your expenses sheet and select column C.
3. Open Data validation in the Data menu, choose List and point it at your list.

From then on you pick the category from a drop-down, and there can be no variations.

Step 3: total by category

On a summary sheet, put each category in column A and this formula next to it in column B:

=SUMIF(Expenses!C:C,A2,Expenses!D:D)

This adds up the amounts on the Expenses sheet whose category matches cell A2. If your sheet has a different name, change it in the formula. Fill it down for the other categories and add a total row with =SUM(B2:B9), adjusting the range to your list. Some spreadsheet setups use semicolons rather than commas between the parts of a formula.

Step 4: compare with what you planned

A column beside it with the planned amount for each category turns the sheet into a budget: subtract what you spent and you can see what went over. If you don't know what numbers to use, the 50/30/20 rule is a good starting point.

Step 5: one file a year, one sheet a month

The simplest way to set it up is to copy the expenses sheet each month and name each copy after the month. That keeps each monthly summary simple. The price is that comparing months takes extra work, and that's where many spreadsheets start to get complicated.

How to keep it going

The biggest enemy of an expense tracker isn't the formula. It's stopping. Three habits help:

• Set a regular time. Ten minutes a week is easier to keep up than recording every purchase.
• Start from your bank statement. Downloading the transactions and pasting them in means nothing gets forgotten.
• Don't aim for perfect. If you don't know where an expense belongs, put it in Other. The overall picture is what matters.

When a spreadsheet isn't enough

A spreadsheet is free, flexible and yours. For one person with one account, it may be all you need. It tends to get harder when:

• you have several accounts and cards, and a transfer between them looks like an expense;
• you are categorising the same things by hand again and again;
• you share expenses with someone and two copies of the file stop matching;
• a formula breaks and you don't notice;
• you want to record an expense on your phone as it happens.

If that sounds familiar, an app can take over that work. In abacova you can import your spreadsheet or your bank's file with a preview before anything is saved, and rules assign a category to whatever repeats. Transfers between your own accounts don't count as expenses. In the phone app you can add an expense, scan a receipt or dictate it, and if you want to keep working in your spreadsheet, every table can be exported to Excel.

The free plan allows two accounts and 200 transactions a month. You can see what each plan includes on the pricing page.

Where to start

Open a spreadsheet today, create the four columns and paste in last month's transactions. In half an hour you will have your first summary by category. Then compare what you see with what you thought you were spending, and work out how much you can save each month.