Budgeting: How to make a budget spreadsheet in Google Sheets or Excel

How do you make a budget spreadsheet?

Set up three tabs: Plan, Transactions and Summary. The plan holds what you mean to spend, the transactions tab holds what you did spend, and the summary compares them. It works the same in Google Sheets and Excel.

Plenty of templates exist, and they're fine. Building your own takes about 20 minutes, and you'll understand every cell. That's the part that matters when a month doesn't fit and something needs changing: you know where to look, and you know what the change will break.

Tab 1: what goes in the budget plan?

Your take-home pay at the top, then one row per category with its monthly amount.

Here's a worked example in round numbers, the same in any currency:

Put take-home pay in a cell nearby, say D2 = 3,000, and a check cell, =D2-B12 in D3. If D3 isn't zero, not every unit of pay has a job yet. If you're not sure what amounts to start with, the budget calculator suggests a split, and how to make a budget explains how to set each line.

Keep categories to ten or fewer at first. You can split groceries into five sub-categories later, once you know you'll keep the spreadsheet going.

Tab 2: how do you set up the transactions log?

Four columns, one row per purchase: date, amount, category, note.

Make the category column a drop-down list that pulls from the Plan tab's categories. In both Google Sheets and Excel it's under Data validation: choose a list from a range and point it at Plan!A2:A11. That one step prevents most broken totals, because "Groceries", "groceries" and "Grocery" all count as different categories to a formula.

Start a new transactions tab each month, or keep one long log and add a month column. One tab per month is easier to read; one long log is easier for yearly totals.

Tab 3: which formula makes the summary work?

SUMIF. It adds up every transaction in a category, so the summary fills itself in as you log.

Read the SUMIF as: look down column C of the transactions tab, find every row that says what's in A2, and add up the amounts in column B for those rows. Copy the row down for each category.

Two more additions make it useful mid-month:

• Conditional formatting on the Left column: red when it goes below zero, so overspending jumps out.
• A pace check: in E2, =C2/B2 shows the share of the budget spent, and =DAY(TODAY())/DAY(EOMONTH(TODAY(),0)) shows the share of the month gone. If you've spent 70% of groceries and only 45% of the month has passed, you'll know on the 14th, not the 31st.

How do you keep a budget spreadsheet up to date?

Log daily or every couple of days, from receipts and your banking app. Most spreadsheets that get abandoned don't fail because of the formulas. They fail because logging falls a week behind, then a month, and catching up feels worse than starting again.

A few habits help:

• Same time each day, two minutes in the evening.
• Log cash immediately, or photograph the receipt. Cash is the spending a bank statement won't remind you about.
• Do a 15-minute review each week against the summary tab.
• Copy last month's Plan tab at the start of each month and adjust it, rather than starting from scratch.

Should you use a spreadsheet or an app?

A spreadsheet if you enjoy building it and you'll sit down to update it. An app if most of your spending happens on the go and logging is where you fall behind.

Wealtie is a budget app that never connects to your bank, so it works the same way a spreadsheet does, by logging, but from your phone in a few taps, cash included. It has a monthly budget by category and a mid-month warning called Budget Health on the Free plan. If you like your spreadsheet for analysis, the Free plan also exports all your data as a ZIP of CSV files, so you can log in the app and keep the formulas.