A step-by-step guide to building a monthly budget in Excel or Google Sheets: categories, a transactions log, SUMIFS totals, budget vs actual, and a chart.
A budget spreadsheet shows where your money goes and whether you are on track. You can build a useful one in under an hour with a few simple formulas, and it will be tailored to you in a way most apps are not. This guide works in both Excel and Google Sheets.
The structure
Use three tabs (sheets):
- Categories: your spending and income categories and the monthly budget for each.
- Transactions: every payment and income, one per row.
- Summary: budget vs actual for the month, calculated automatically.
Step 1: Set up your categories
On the Categories tab, put headings in row 1: Category (A), Type (B, income or expense) and Monthly budget (C). Start with broad categories, such as:
- Income: salary, other income.
- Essentials: rent or mortgage, council tax, energy, water, broadband and phone, insurance, groceries, transport.
- Flexible: eating out, entertainment, clothes, subscriptions, gifts.
- Saving and debt: emergency fund, other savings, debt repayments.
Keep it to about 15–20 categories. Too many and it becomes a chore.
Step 2: Build the transactions log
On the Transactions tab, use headings: Date (A), Description (B), Category (C) and Amount (D). Enter spending as positive numbers in this layout to keep formulas simple.
Make the Category column a dropdown so spelling is consistent: select column C, then use Data > Data validation, and choose a list from the Categories range. Consistent categories are what make the totals work.
Convert the range into a table (Excel: Insert > Table) so new rows are included automatically.
Tip: many banks let you export transactions as a CSV file, which you can paste in rather than typing.
Step 3: Create the summary
On the Summary tab, put the month's start date in B1 (for example 01/10/2026) and the end date in B2 with =EOMONTH(B1,0).
List categories in column A (or reference them from the Categories tab), and in column B bring in the budget:
=XLOOKUP(A5,Categories!A:A,Categories!C:C,0)
In column C, calculate actual spending for that month:
=SUMIFS(Transactions!D:D,Transactions!C:C,A5,Transactions!A:A,">="&$B$1,Transactions!A:A,"<="&$B$2)
In column D, the difference: =B5-C5. A positive number means you are under budget.
Copy the formulas down for each category. Add totals at the bottom with SUM.
Step 4: Make problems visible
- Add conditional formatting to column D so negative values (overspends) show in red.
- Add a chart: select categories and actuals, then insert a bar chart to see where money goes.
- Add a "left to spend" cell: total income minus total spending so far.
Step 5: Use it
- Log transactions weekly, or import them monthly.
- At month end, review the overspends and decide what to change.
- Change the date in B1 to start a new month. The formulas update automatically.
- After three months, adjust budgets to reflect reality.
Common problems
- Totals are zero: dates may be stored as text. Check they are right-aligned, or use DATEVALUE.
- Some spending is missing: categories may be spelt differently. Use the dropdown.
- Irregular bills: add a "sinking fund" category for annual costs divided by 12.
Privacy
If you store financial details in a cloud spreadsheet, use a strong password and two-step verification on the account, and be careful about sharing links.
Worked example
Suppose your Groceries budget is £350. By 20 October your Transactions tab holds 14 grocery entries totalling £281.40. The Summary row shows Budget £350, Actual £281.40 and Difference £68.60, so you know you have about £68 for the rest of the month, or a little over £6 a day. If Eating out shows a negative difference of £-45, it turns red, and you can decide whether to cut back or move money from another category. That quick, specific view is what makes a budget spreadsheet useful.
Sources and further reading
- Microsoft Support: Excel functions (by category)
- Google Docs Editors Help: Google Sheets function list
- Microsoft Support: SUMIFS function
- MoneyHelper: budgeting
Facts checked on 20 October 2026. Spotted an error? Tell us and we will correct it.
Frequently asked questions
What should you know about Step 1: Set up your categories?
On the Categories tab, put headings in row 1: Category (A), Type (B, income or expense) and Monthly budget (C).
What should you know about Step 2: Build the transactions log?
On the Transactions tab, use headings: Date (A), Description (B), Category (C) and Amount (D). Enter spending as positive numbers in this layout to keep formulas simple.
What should you know about Step 3: Create the summary?
On the Summary tab, put the month's start date in B1 (for example 01/10/2026) and the end date in B2 with =EOMONTH(B1,0) .
What should you know about Privacy?
If you store financial details in a cloud spreadsheet, use a strong password and two-step verification on the account, and be careful about sharing links.