Budgeting

How to create a budget planner in Excel

Every formula here works identically in Excel, Google Sheets and Numbers. Building it yourself takes about twenty minutes and beats a downloaded template, because you will understand every cell and therefore trust the number at the bottom.

Step 1: three blocks on one sheet

Name the first sheet after the month, for example Jan 2027. You want three blocks stacked vertically, with labels in column A and numbers in column B. Leave column C for notes.

Savings sits in the committed block on purpose. If it lives at the bottom as "whatever is left", it will be whatever is left, which is usually nothing.

Step 2: the formulas that do the work

Four of them live in this block. The fifth arrives in step 4.

• =SUM(B2:B3) adds a block. Add rows inside the range rather than below it, so the total keeps including them.
• =B4-B14 is the whole point of the sheet: income minus everything already promised.
• =EOMONTH(TODAY(),0)-TODAY()+1 counts the days left in the current month, including today. EOMONTH returns the last day of the month, and subtracting today's date gives the gap.
• =IF(B18>0,B17/B18,B17) gives what you can spend per day. The IF stops the cell showing a divide by zero error if you ever overwrite the day count by hand.

One quirk worth knowing before it confuses you: EOMONTH(TODAY(),0) always means the current calendar month. When you duplicate the sheet for a new month, the old month's copy keeps counting down today's month rather than freezing. That is harmless, because the daily figure only means anything in the month you are actually in, but do not read it as history.

Format B4, B14, B17 and B19 as currency, and make row 17 and row 19 bold and larger than everything else. Those two numbers are the only ones you will look at during the month.

Step 3: a second sheet for transactions

Add a sheet called Transactions with four columns: Date, Description, Category, Amount. Nothing else. Every purchase is one row, typed the same way every time, which matters because the next step matches on the text in the Category column.

Use a dropdown so the categories stay consistent. Select the Category column, then Data, then Data validation, and give it your list. One misspelled "Grocries" is enough to make a total quietly wrong.

Step 4: pull the totals back with SUMIF

Back on the month sheet, add a small table of spending categories. Against each one:

SUMIF reads as: look down the Category column on the Transactions sheet, find every row matching the label in A22, and add up the matching amounts. Whole column references like Transactions!C:C are fine here and save you from re-editing the range every time the sheet grows.

Step 5: make it warn you

Select the "left" column, then Format, then Conditional formatting. Add a rule for less than 0 with a red fill. Now a category colours itself the moment you cross the line, instead of waiting for you to notice.

Add a second rule on the same range for less than 20 in amber if you want warning before the fact rather than confirmation after it.

Step 6: reuse it, do not rebuild it

At the end of the month, right click the sheet tab and duplicate it. Rename it for the new month, clear the typed numbers, and leave every formula alone.

Keep all months in one file. A new file per month means you can never answer "is this normal for us?", which is the most useful question a budget can answer and the one a fresh spreadsheet each month makes impossible.

Five mistakes that kill spreadsheet budgets

• Too many categories. Eight is plenty. Thirty guarantees that entering a transaction requires a decision, and decisions are what you stop making.
• Budgeting the average month. Annual insurance, car tax and Christmas are real. Either give them a monthly twelfth in the committed block, or be surprised by them yearly.
• Typing over last month. You lose the history and with it the only benchmark you had.
• Leaving cash out. If it never gets typed in, the sheet says you underspent every month while your balance disagrees.
• Updating it monthly. A budget you reconcile once a month is a report. Ten minutes on a Sunday is what makes it a plan.

The one thing a spreadsheet cannot do

This is worth saying plainly, since I would rather you finish the sheet than download something.

A spreadsheet is excellent at the arithmetic and useless at the moment that actually matters. You decide to spend money in a shop, on a Tuesday, holding a phone. The spreadsheet is on a laptop at home. By the time you open it, the decision is already three days old and you are transcribing history rather than steering anything.

No formula fixes that. If you find your sheet is always four days behind and you have started guessing at the gaps, the problem is not the spreadsheet's design. It is that entry has to happen where the spending happens.

Disclosure: that is the problem Wallety exists to solve, and I build it. It uses the same structure as this sheet, committed money out first and a clear figure for what is safely spendable today, on a phone with no account and no bank login, with everything stored on the device. It also imports a CSV, so a spreadsheet you have already filled in is not wasted.

If your spreadsheet is working, keep it. It costs nothing and nobody needs a subscription to do arithmetic.

Related

The method behind this layout, with a printable version if you would rather not open a spreadsheet at all, is in a month to month budget planner that survives past week two. For the category that breaks most budgets there is a family meal budget planner, and if you are weighing up software, how to choose a budget planner app includes who mine is wrong for.