A simple budget method that works in a spreadsheet
A budget does not need special software. A spreadsheet with two columns, what you planned and what really happened, is enough to show where your money goes. This guide sets out a simple method you can build in Excel or Google Sheets in under an hour.
This is a general guide to organising a budget. It is not financial advice, and it cannot take your own situation into account.
Step 1: start with take home pay
Use the money that actually arrives in your account each month, after anything taken off before you are paid. If your income changes from month to month, use a cautious figure, such as the lowest of your last few months. It is easier to deal with a surprise surplus than a surprise shortfall.
Step 2: sort spending into a few groups
Too many categories make a budget tiring to keep up. Start with five groups and put a handful of categories in each:
| Group | Typical categories |
|---|---|
| Bills | Rent or mortgage, energy, phone and internet, insurance, subscriptions |
| Essentials | Groceries, transport, health, household items |
| Lifestyle | Eating out, entertainment, shopping, gifts |
| Savings | Emergency fund, holiday fund, other goals |
| Debt | Credit card and loan payments |
Bills are the amounts that are fixed or nearly fixed. Essentials are things you must buy but can control a little. Lifestyle is everything you choose to spend.
Step 3: give every pound a job
Write a planned amount next to every category. Then check one sum:
Take home pay, minus all planned amounts = what is left over
Aim for the left over figure to be zero or slightly above. If it is negative, the plan spends more than comes in, and something has to shrink before the month starts. If it is well above zero, decide now where that money should go, otherwise it tends to vanish.
A starting point: the 50, 30, 20 split
If you have no idea what the planned amounts should be, one common rule of thumb is to split take home pay like this:
- 50% for needs (bills and essentials)
- 30% for wants (lifestyle)
- 20% for savings and paying down debt
On 2,000 a month that gives 1,000 for needs, 600 for wants and 400 for savings and debt.
It is only a rule of thumb. If your rent alone is more than half your pay, the split will not fit, and that is not a failure on your part. Use it as a first draft and adjust the percentages until they describe a month you can really live. The free budget split calculator lets you try different percentages.
Step 4: build the sheet
You need two tabs.
Transactions
One row for each thing you spend or receive, with four columns: date, description, category and amount. Add rows as the month goes on. Copying them from your banking app once a week takes a few minutes.
Budget
One row per category, with these columns:
| Column | Formula for row 2 |
|---|---|
| A: Category | Typed |
| B: Group | Typed |
| C: Budget | Typed |
| D: Actual | =SUMIF(Transactions!C:C,A2,Transactions!D:D) |
| E: Left | =C2-D2 |
| F: % used | =IF(C2=0,0,D2/C2) |
The SUMIF formula adds up every transaction whose category matches the row, so the Actual column fills itself in. It works the same way in Excel and Google Sheets. To keep one month separate from the next, either start a fresh Transactions tab each month or switch to SUMIFS with a start and end date.
Two small touches make the sheet much easier to read. First, make the category column in Transactions a dropdown list (Data validation) so that a typing slip cannot create a category that the budget does not know about. Second, add conditional formatting to the Left column so that negative numbers turn red.
Step 5: check in once a week
A budget that is only looked at on the last day of the month is a report, not a plan. Once a week:
- Add the week's transactions.
- Look at the % used column. A category at 90% with half the month to go needs attention.
- If one category is going to run over, move money from another so that the total still balances. Changing the plan is allowed. Ignoring it is what causes trouble.
Step 6: review at the end of the month
Compare Budget and Actual for every category and ask two questions. Which planned amounts were unrealistic? What came up that had no category at all? Adjust next month's plan to match. The first month is always a rough guess. By the third, the numbers usually settle.
Irregular costs
Annual bills, car repairs and presents are what break most budgets. List the ones you can predict, add them up for the year, divide by twelve and set that amount aside each month as its own savings category. When the bill arrives, the money is already there.
For a finished version of this method, the Budget Planner spreadsheet has a monthly and yearly budget, a bill tracker, a debt payoff planner and savings goals, for Excel and Google Sheets.
Keep reading
- Free tool: Budget split calculator
- Guide: How to track your small shop's profit in a spreadsheet
- Guide: How to use a risk log