Pro workbook: Budget, Transactions, Year, Setup, Formulas. Sample data, real formulas.
| Monthly budget | Month | September 2026 | |||
| Yellow cells are yours to fill. Actuals come from the Transactions sheet for the month in Setup. | |||||
| Income | Planned | Actual | Difference | ||
| Salary | 3,900.00 | 3,900.00 | 0.00 | ||
| Freelance | 400.00 | 450.00 | 50.00 | ||
| Other income | 0.00 | 0.00 | 0.00 | ||
| Total income | 4,300.00 | 4,350.00 | 50.00 | ||
| Expenses | Planned | Actual | Difference | % of income | |
| Housing | 1,400.00 | 1,400.00 | 0.00 | 32.2% | |
| Utilities | 180.00 | 151.40 | 28.60 | 3.5% | |
| Groceries | 520.00 | 519.95 | 0.05 | 12.0% | |
| Transport | 210.00 | 89.00 | 121.00 | 2.0% | |
| Insurance | 120.00 | 120.00 | 0.00 | 2.8% | |
| Debt payments | 300.00 | 300.00 | 0.00 | 6.9% | |
| Savings | 600.00 | 600.00 | 0.00 | 13.8% | |
| Health | 90.00 | 140.00 | -50.00 | 3.2% | |
| Personal | 250.00 | 96.00 | 154.00 | 2.2% | |
| Other | 150.00 | 60.00 | 90.00 | 1.4% | |
| Total expenses | 3,820.00 | 3,476.35 | 343.65 | 79.9% | |
| Bottom line | Planned | Actual | Difference | ||
| Left over (income − expenses) | 480.00 | 873.65 | 393.65 | ||
| Savings rate (savings ÷ income) | 14.0% | 13.8% | |||
| How to use: type this month's plan in the yellow Planned cells, log spending on the Transactions sheet. | |||||
| Difference for expenses is Planned − Actual: red means you overspent, green means you came in under. | |||||
| Add a category: insert a row inside the Expenses block; the totals and formatting extend automatically. |
| Transactions | |||
| One row per payment or income. Category must match the Setup list — the cell offers a dropdown. | |||
| Date | Description | Category | Amount |
| 2026-09-01 | September salary | Salary | 3,900.00 |
| 2026-09-01 | Rent | Housing | 1,400.00 |
| 2026-09-03 | Electricity | Utilities | 96.40 |
| 2026-09-04 | Weekly shop | Groceries | 131.20 |
| 2026-09-05 | Transit pass | Transport | 89.00 |
| 2026-09-06 | Car insurance | Insurance | 120.00 |
| 2026-09-08 | Student loan | Debt payments | 300.00 |
| 2026-09-08 | Transfer to savings | Savings | 600.00 |
| 2026-09-11 | Weekly shop | Groceries | 118.75 |
| 2026-09-12 | Logo job | Freelance | 450.00 |
| 2026-09-14 | Dentist | Health | 140.00 |
| 2026-09-18 | Weekly shop | Groceries | 142.10 |
| 2026-09-20 | Concert tickets | Personal | 96.00 |
| 2026-09-22 | Internet | Utilities | 55.00 |
| 2026-09-25 | Weekly shop | Groceries | 127.90 |
| 2026-09-27 | Birthday present | Other | 60.00 |
| 2026-08-01 | August salary | Salary | 3,900.00 |
| 2026-08-01 | Rent | Housing | 1,400.00 |
| 2026-08-09 | Weekly shop | Groceries | 510.30 |
| 2026-08-15 | Holiday | Personal | 640.00 |
| Rows outside the month chosen in Setup are greyed out: they still count on the Year sheet, just not in this month's Budget. | |||
| These 20 rows are examples — delete them and start logging. | |||
| The formulas read rows 5–400 (about a year of daily entries). Need more? Change 400 to 2000 in the Budget and Year formulas — the Formulas sheet lists them. | |||
| Year at a glance | ||||
| Twelve months from the Setup month back. Income, expenses and what was left, from the Transactions sheet. | ||||
| Month | Income | Expenses | Left over | Savings rate |
| Oct 2025 | 0.00 | 0.00 | 0.00 | 0.0% |
| Nov 2025 | 0.00 | 0.00 | 0.00 | 0.0% |
| Dec 2025 | 0.00 | 0.00 | 0.00 | 0.0% |
| Jan 2026 | 0.00 | 0.00 | 0.00 | 0.0% |
| Feb 2026 | 0.00 | 0.00 | 0.00 | 0.0% |
| Mar 2026 | 0.00 | 0.00 | 0.00 | 0.0% |
| Apr 2026 | 0.00 | 0.00 | 0.00 | 0.0% |
| May 2026 | 0.00 | 0.00 | 0.00 | 0.0% |
| Jun 2026 | 0.00 | 0.00 | 0.00 | 0.0% |
| Jul 2026 | 0.00 | 0.00 | 0.00 | 0.0% |
| Aug 2026 | 3,900.00 | 2,550.30 | 1,349.70 | 0.0% |
| Sep 2026 | 4,350.00 | 3,476.35 | 873.65 | 13.8% |
| Total | 8,250.00 | 6,026.65 | 2,223.35 | 7.3% |
| Setup | ||
| Pick the month to budget and keep the category list here. Both feed every other sheet. | ||
| Month (first day) | 2026-09-01 | Type the 1st of any month — Budget and Year follow it. |
| Currency symbol | $ | |
| Categories (income) | Categories (expenses) | |
| Salary | Housing | |
| Freelance | Utilities | |
| Other income | Groceries | |
| Transport | ||
| Insurance | ||
| Debt payments | ||
| Savings | ||
| Health | ||
| Personal | ||
| Other | ||
| Rename a category here and the Budget sheet, the Transactions dropdown and the Year sheet all update. | ||
| Keep income and expense names different from each other — the budget tells them apart by name. |
| How the formulas work | ||
| Every formula in this workbook, in plain words. Nothing is hidden and nothing needs a macro. Formulas are shown without the leading = sign. | ||
| Where | Formula | What it does |
| Budget · Actual | SUMIFS(Transactions!D5:D400, Transactions!C5:C400, A5, Transactions!A5:A400, ">="&Setup!B3, Transactions!A5:A400, "<="&EOMONTH(Setup!B3,0)) | Adds every Transactions amount whose category matches this row and whose date falls inside the Setup month. EOMONTH(date,0) is the last day of that month. Ranges stop at row 400 on purpose — whole-column references make Google Sheets slow; raise the number if you log more. |
| Budget · Difference (income) | C5-B5 | Actual minus planned. Positive = earned more than planned. |
| Budget · Difference (expenses) | B11-C11 | Planned minus actual, so a negative number always means overspent. Conditional formatting colours it red. |
| Budget · % of income | IF($C$8=0,0,C11/$C$8) | Share of actual income this category consumed. The IF stops a #DIV/0! error before any income is logged. |
| Budget · Left over | C8-C21 | Total actual income minus total actual expenses. |
| Budget · Savings rate | IF(C8=0,0,C17/C8) | The Savings category as a share of income. Rename the category in Setup and this still works — it points at the row, not the word. |
| Transactions · Category | Data validation: list =AllCats | A dropdown fed by the named range AllCats (Setup!A7:C16). Typing a category that isn't in the list is refused, so SUMIFS never misses a row because of a typo. |
| Transactions · grey rows | Conditional format: =AND($A5<>"", OR($A5<Setup!$B$3, $A5>EOMONTH(Setup!$B$3,0))) | Rows dated outside the Setup month are shown in grey. They still feed the Year sheet. |
| Year · Month | EOMONTH(Setup!$B$3,-12)+1 | EOMONTH(date,-12) is the last day of the month a year earlier; +1 makes it the first day of the month eleven months before the Setup month; each row below moves one month forward, so the table always ends on the current month. |
| Year · Income | SUMPRODUCT((dates>=A5)*(dates<=EOMONTH(A5,0))*ISNUMBER(MATCH(cats,IncomeCats,0))*amounts) | One SUMPRODUCT instead of a SUMIFS per category: multiplies three yes/no tests (in month, is an income category) by the amount and adds up. MATCH against the named range IncomeCats decides what counts as income. |
| Year · Expenses | Same, with ExpenseCats | Identical shape, tested against the expense list instead. |
| Year · Savings rate (total) | SUMPRODUCT(E5:E16,B5:B16)/B17 | A weighted average: each month's rate weighted by that month's income, so a big month counts more than a small one. |