GridWorkshop
Guide

A simple budget method that works in a spreadsheet

Published by GridWorkshop. About 4 minutes to read.

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:

GroupTypical categories
BillsRent or mortgage, energy, phone and internet, insurance, subscriptions
EssentialsGroceries, transport, health, household items
LifestyleEating out, entertainment, shopping, gifts
SavingsEmergency fund, holiday fund, other goals
DebtCredit 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:

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:

ColumnFormula for row 2
A: CategoryTyped
B: GroupTyped
C: BudgetTyped
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:

  1. Add the week's transactions.
  2. Look at the % used column. A category at 90% with half the month to go needs attention.
  3. 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