How to Make a Budget in Excel, Step by Step (Free Template)
One row per expense, a dozen categories, money left over and savings rate: a step-by-step method to keep a monthly budget in Excel, with a free template.
A budget in Excel doesn't need to be complicated to be useful. It needs a simple structure, a dozen categories that don't move, the two figures that really matter (what is left at the end of the month and your savings rate) and a habit that fits into ten minutes a month. Everything else is decoration.
This tutorial walks you through building a monthly budget in a spreadsheet, step by step. It is based on our free monthly budget spreadsheet template, which you can download with no sign-up: fill it in as you read, or use it as inspiration to build your own file. The figures quoted are those of the template's made-up examples.
Step 1: one row per income or expense
The first decision is the most important one: how to lay out the data. The usual reflex is to draw a big grid with categories down the side and twelve months across the top. It looks tidy, but it gets awkward as soon as an expense changes nature, a month has two bills of the same type or you want to filter.
The sturdiest layout is a list: one row per income or expense, every month. In the template, the "Budget" tab has these columns:
- Month: any day of the month will do, the workbook only looks at the month;
- Section: income or expense;
- Category: picked from a list;
- Amount (€);
- Paid by: me, partner or joint, useful for a couple;
- Fixed: yes or no, does the expense come back every month;
- Note: free text.
The template has six hundred entry rows, four years at a dozen rows a month. A list can be sorted, filtered, copied and extended without breaking anything: that is what makes it last. The calculations happen elsewhere, in a summary tab that reads the list.
Step 2: choose a dozen stable categories
The temptation is to break everything down: bakery, fuel, tolls, streaming, haircuts… By the third month nobody knows where anything goes, and the file is abandoned. A dozen stable categories beat fifty you end up no longer filling in.
The template offers 6 income categories (each partner's salary, bonuses, rent received, benefits, other income) and 12 expense categories:
- housing (rent or mortgage);
- utilities and energy;
- groceries;
- transport;
- insurance;
- subscriptions and phone;
- health;
- children;
- leisure and going out;
- holidays;
- taxes;
- gifts and miscellaneous.
The list helps, it doesn't forbid anything: a missing category can be typed by hand. In the summary, the doughnut chart of your spending then files it under "Other items". The useful rule is above all not to change categories along the way, or months stop being comparable. To decide which metrics to follow next, our article on budget tracking covers the most useful ones.
Step 3: work out what is left and your savings rate
Two figures sum up a month.
What is left after spending is what remains once everything is paid: income minus expenses. Divided by income, it gives your savings rate, the figure that tells you whether your wealth can grow.
With the template's made-up examples, in October:
| October | |
|---|---|
| Income | €4,950 |
| Expenses | €3,440 |
| Left after spending | €1,510 |
| Savings rate | 30.5% |
The arithmetic is simple: 4,950 − 3,440 = €1,510, and 1,510 ÷ 4,950 = 30.5%. The template's "Summary" tab shows it for the chosen month, along with the share of fixed costs and how your spending splits by category.
These figures are reading aids, not targets: they depend on your income, your housing and your plans. A savings rate that dips in a holiday month is nothing to worry about; the trend over several months is what tells you something. That monthly surplus is also what feeds your emergency fund: knowing it tells you how long it will take to build one.
Step 4: separate fixed and variable costs
The "Fixed" column looks like a detail. It is actually valuable. Fixed costs come back every month without you having to think about them; variable costs move from one month to the next and are the ones you can act on in the short term.
In the template's examples, housing, utilities and energy, insurance, subscriptions and phone, and taxes are marked fixed. Groceries, transport, health, children, leisure and holidays are variable. You can see it in the amounts: housing stays at €1,150 in September and October, while holidays go from €0 to €250.
The summary works out the share of fixed costs in the month's total. A high share means a rigid budget: little room if an income drops. That isn't a flaw in itself, but it is worth knowing before you commit to a new recurring expense.
Step 5: keep a twelve-month history
A single month doesn't say much. A quarterly tax bill, back-to-school costs or a summer holiday are enough to distort the picture. Comparing over time is what makes a budget useful.
Below the month at a glance, the template's summary lines up twelve months side by side, starting from the first month entered: income, expenses, money left over and savings rate, with a chart of income and expenses. A negative balance shows in red, so you can spot at a glance the months when you dipped into your savings.
Over twelve months, annual costs (insurance paid in one go, end-of-year gifts, summer holidays) find their place, and your average savings rate for the year becomes readable.
Step 6: update it in ten minutes a month
This is where most budgets fail: data entry becomes a chore. Here is the method that holds up.
- In the first month, enter every income and every expense in the "Budget" tab, working from your statements. It is the only session that takes a little while.
- In the following months, copy last month's rows, change the month, then adjust the amounts that moved. Fixed costs hardly ever change; only the variable ones need a look at your statement.
- Open the "Summary": the latest month entered is shown automatically. To look at an earlier month, type another date in the gold cell.
In the template, only the gold cells need filling in. The file has no macros or scripts: it opens in Excel, in Google Sheets (upload it to Google Drive, then "Open with › Google Sheets"), and also in LibreOffice and Numbers.
The formulas, to understand or rebuild the workbook
If you are building your own file, two functions are enough: SUMIFS and EDATE.
To add up a month's expenses, the dates must fall between the first day of the month and the first day of the next month. That is what lets you enter any day of the month in the "Month" column. With the first day of the month shown in C6:
=SUMIFS(Budget!$D$6:$D$605,Budget!$B$6:$B$605,"Expense",Budget!$A$6:$A$605,">="&C6,Budget!$A$6:$A$605,"<"&EDATE(C6,1))
The same formula with "Income" gives your income. What is left is a subtraction, and the savings rate a division protected against an empty month:
=IF(C10=0,"",C9/C10)
where C9 holds what is left and C10 the income. For the history, EDATE also generates the twelve months: the template starts from the first month entered, adds 0, 1, 2… up to 11 months, then applies the same SUMIFS to each row. The template only uses these ordinary formulas, which is why it works everywhere.
Common mistakes
- Too many categories. Beyond a dozen, entry slows down and comparisons lose their meaning. Group them.
- Keeping the examples. The rows already filled in the template (two months, September and October) are made up and marked "Sample row: delete it". Delete them before you start, or they will mix with your own figures.
- Forgetting annual costs. A tax or an insurance premium paid once a year is entered in the month it is paid. The twelve-month history puts it back in perspective.
- Changing categories mid-year. Months stop being comparable. If a category is really missing, add it, but keep the others.
- Treating the indicators as rules. A 30.5% savings rate is not a target: it is the result of a made-up example. What counts is your own trend.
- Giving up after a missed month. Just copy the rows of the latest month entered and adjust. Ten minutes, no more.
When the spreadsheet is no longer enough
A budget in Excel works well as long as it stays a budget. The limits show up when you want to connect it to everything else: savings building up, investments, the mortgage, cash flow over the coming months. The spreadsheet then turns into a collection of tabs that don't talk to each other, as we explain in why spreadsheets aren't enough.
When that day comes, nothing is lost. The template imports into Patrice for free: Settings › Import & export › Import › Budget Import. Patrice recognises the Month, Section, Category, Amount, Paid by and Fixed columns, files each row as income or expense and shows you the months that will be written before you confirm. Each imported month replaces the month already saved, and the file is read in your browser.
Patrice then links your budget to your net worth and your cash flow forecasts. The advanced budget (each partner's share, savings goals, AI analysis) is part of Premium; the template itself stays free and complete.
To get started, all you need is a file and last month's statements: download the budget spreadsheet template, delete the examples and enter your first month.
