A budget only works if keeping it current is effortless and going over is obvious. Most budgets die because updating them feels like a chore and overspending hides until the month’s over. The fix is a two-part build in Google Sheets: a plain log where you jot each transaction, and a summary that totals categories, compares them to your plan, and turns red the moment you’re over. Log a coffee, and the summary updates itself. This walks that build from the two tabs up to the chart.
Two tabs: a log and a summary
The single best decision is to separate where data goes in from where you read it. One tab for raw transactions, one for the summary that reads them.
| Tab | What lives there |
|---|---|
| Transactions | One row per expense: date, category, amount — appended, never reformatted |
| Summary | Each category’s budget, actual spend, and the difference — all formulas |
You only ever type on the Transactions tab — date, category, amount, one row at a time. The Summary tab never gets manual numbers; it reads the log through formulas. This split is what makes the budget low-effort to maintain: logging is mindless, and the analysis happens on its own. It’s the same data-and-display separation behind any live dashboard — keep the raw entry apart from the calculated view and the whole thing stays trustworthy as months pile up.
Lock categories with a dropdown
Before the formulas can work, the category column has to be clean — and that means a dropdown, not free typing. SUMIF matches text exactly, so “Groceries,” “groceries,” and “Grocery” become three separate totals that silently undercount each.
- On the Transactions tab, select the whole Category column.
- Data → Data validation → Dropdown, and list your categories: Groceries, Rent, Transport, Dining…
- Now every entry is picked from the list — no typos, and every SUMIF finds all its matches.
This one step is what keeps the whole budget honest. A dropdown guarantees the category text on the log matches the category text in your summary formulas exactly, so nothing slips through a spelling crack. It’s faster to log, too — tapping a choice beats typing it. The data validation techniques behind it work the same in Sheets as in Excel.
Total each category with SUMIF
The formula that powers the summary is SUMIF — it adds up every transaction matching a category. On the Summary tab, beside “Groceries”:
=SUMIF(Transactions!C:C, "Groceries", Transactions!D:D)This scans the category column on the Transactions tab, finds every “Groceries” row, and totals the matching amounts — your actual spend for that category, updating the instant you log a new expense. One SUMIF per category builds the whole actual-spending column, and because the ranges are whole columns (C:C, D:D), new transactions flow in with no formula edits. It’s the same idea as conditional totals with SUMIFS, applied to your own spending.
Budget vs actual: the number that matters
A total alone doesn’t tell you much; what matters is total against plan. Put your planned amount beside the actual and subtract.
| Category | Budget | Actual | Left |
|---|---|---|---|
| Groceries | 400 | 340 | =B2-C2 |
The “Left” column (=Budget-Actual) is the one you’ll actually watch — it tells you how much room remains in each category, or by how much you’ve blown past it when it goes negative. This single comparison turns a passive record of what you spent into an active tool that tells you what you can still spend. That’s the difference between tracking a budget and running one.
Flag overspending with color
Numbers you have to read get ignored; colors you can’t miss get acted on. A conditional-formatting rule turns the summary into a traffic-light view.
Custom formula: =$C2>$B2 → red fill (actual over budget)Applied to your category rows, this reddens any category where actual spending (column C) exceeds the budget (column B). Add a second rule for the warning zone — =$C2>=$B2*0.8 in amber — and you get a three-stage signal: green while there’s room, yellow at 80% of budget, red once you’re over. Now overspending announces itself the moment you log the transaction that causes it, instead of surfacing in a painful month-end review. The conditional formatting guide covers the rule setup in full.
Your bottom line: income minus expenses
The whole point of a budget is the single number at the bottom — did more come in than went out? Total your income, total your expenses, and subtract.
=SUM(income_range) - SUM(expense_range)A positive result is a surplus, negative is a deficit, and a conditional-formatting rule coloring it green or red makes the month’s verdict unmissable. This figure is the budget’s headline — keep it large and near the top of the Summary tab so it’s the first thing you see when you open the file. Everything else is detail explaining how you arrived at it.
One structural choice makes a budget genuinely work: treat savings as a budgeted line, not whatever’s left over. List “Savings” as a category with its own planned amount, right alongside rent and groceries, so it gets funded first instead of last. Budgets that leave saving to chance rarely save anything; budgets that give it a line and color it red when underfunded actually build a cushion. It costs one more row, and it quietly reframes saving from an afterthought into a bill you pay yourself.
Track it month over month
A single month is a start, but the value compounds when you compare months. Add a Month column to the log and switch SUMIF for SUMIFS, which filters on two conditions at once — category and month:
=SUMIFS(Transactions!D:D, Transactions!C:C, "Groceries", Transactions!E:E, "May")Now you can total each category for a specific month, and laying those columns side by side — January through December — reveals the trends a single month hides: groceries creeping up, utilities spiking in summer. You don’t need a new spreadsheet each month; one log with a month column feeds every monthly total through SUMIFS. Add a year-to-date column that simply sums the twelve monthly cells, and you get a running annual total beside the monthly detail — useful for spotting whether you’re on pace before December arrives. For a cleaner monthly summary that reshapes on its own, the QUERY function can group all spending by category and month in a single formula.
See where the money goes
A chart turns the budget from a ledger into a mirror. Seeing that dining-out is a fifth of your spending does more to change behavior than any total. Build the two tabs, total with SUMIF, compare against budget, color the overspend, and chart the breakdown — that’s a complete budgeting system that costs nothing and updates itself. From then on the only effort is logging each transaction; the spreadsheet does the rest, and tells you in red the moment a category needs your attention. Google’s function list is a handy reference as you extend it with more formulas.