How to Build a Monthly Budget Spreadsheet with Formulas

A monthly budget is easier to review when it answers four questions. How much came in? How much went out? What is left? Where did you overspend?

You can answer all four with a small spreadsheet and a handful of formulas. No paid app is required to follow this tutorial. The numbers below are clearly marked sample data so you can check every formula against the arithmetic.

What you will build

One tab with:

1. An Income list in columns A:B
2. An Expense plan in columns D:G (category, planned, actual, variance)
3. An optional transaction log in columns H:I (kept separate from Income)
4. A Summary block: total income, planned total, actual total, remaining cash, savings rate, and share of income per category

These examples use SUM, SUMIF and IF with basic arithmetic; syntax may vary by spreadsheet locale. This post shows the formulas and the expected results for the sample. It does not claim a full product test inside Excel or Google Sheets.

Layout (so ranges do not overlap)

Do not put the transaction log in A:B. That range is reserved for income; a log there would overwrite income rows and could be counted again by a wide SUMIF.

Sample data (examples only — replace with your numbers)

Use these rows only to verify the formulas. Delete them when you start your own budget.

Income (sample) — columns A:B

Put total income in B10:

=SUM(B2:B3) 

Expected result with the sample: 3650.00

Expense plan (sample) — columns D:G

In this sample, "Savings transfer" is money you move to savings in the same month. Treat it as a planned outflow, then measure the savings rate against total income.

For a first pass you can type the Actual values in F2:F8 (as in the table). Step 6 shows how to fill F from a separate transaction log with SUMIF instead.

Step 1: Total income

Already covered: B10 = SUM(B2:B3) → 3650.00 with the sample.

If B2:B3 are empty, SUM returns 0. That is expected after you delete the sample rows and before you enter your own income.

Step 2: Planned and actual expense totals

Put planned total in E10 and actual total in F10:

=SUM(E2:E8) =SUM(F2:F8) 

Expected results with the sample:

• Planned total (E10): 2605.00
• Actual total (F10): 2679.55

Empty planned/actual ranges also sum to 0.

Step 3: Remaining cash

Remaining cash is income minus actual spending (including the savings transfer, if you logged it as an outflow):

=B10-F10 

Expected result with the sample: 970.45

If you prefer cash remaining before the savings transfer, subtract actual spending excluding the savings-transfer row instead. That figure is cash left before you set money aside; it is not a recommended spending allowance. Pick one definition and keep it fixed, or the history becomes meaningless.

Step 4: Savings rate (guard zero income)

With the sample definition (savings transfer ÷ total income), put this in a summary cell (for example B12):

=IF(B10=0,"",F8/B10) 

Format the cell as a percentage when it shows a number.

Expected result with the sample: 13.70% (500 ÷ 3650)

When income is zero (B10=0), the formula returns a blank cell. A blank means “rate not defined”, not 0%. Do not replace the blank with 0 — that would look like a real 0% savings rate.

Equivalent pattern with IFERROR (same idea: hide the #DIV/0!):

=IFERROR(F8/B10,"") 

IF is clearer when you only want to treat a zero denominator; IFERROR also hides other errors. Prefer IF(B10=0,"",…) for this tutorial.

If your savings sit in several categories, put them in a named list and sum that list instead of one cell (F8 here is only the sample’s “Savings transfer” actual).

Step 5: Planned vs actual (variance)

In G2:

=E2-F2 

Copy down through G8.

Reading the sign (sample convention):

• Positive = under plan (you spent less than planned)
• Zero = exactly on plan
• Negative = over plan

Expected variances with the sample:

So the sample month is over on groceries and dining out, and under on transport and utilities.

Step 6: Optional SUMIF from a separate transaction log

Keep the log in H:I, not in A:B.

Transaction log (sample) — columns H:I

In F2 (Actual for the first plan row), enter:

=SUMIF($H$2:$H$200,D2,$I$2:$I$200) 

Copy that formula down from F2 through F8. Do not put running totals or subtotal rows inside H:I — only raw transactions.

Expected results with this sample log:

• F2 (Rent) → 1200.00
• F3 (Groceries) → 438.50
• F8 (Savings transfer) → 500.00
• Empty log (H2:I200 blank) → every SUMIF returns 0
• Category with no matching rows → 0 for that row

Typed Actual values and SUMIF-filled Actual values should not be mixed in the same month without a clear rule. Pick one method per month.

Step 7: Share of income by category (guard zero income)

Keep shares in an unused column beside the plan — for example J2 (do not overwrite the H:I log). In J2:

=IF($B$10=0,"",F2/$B$10) 

Format as a percentage and copy down through the seven categories.

Expected shares with the sample (rounded):

If $B$10=0, each share cell stays blank (“not defined”), not 0%.

These shares are descriptive. They are not a rule about how anyone "should" split money. Overspending relative to income can produce a share above 100% for a category; the formula does not cap at 100%.

A simple monthly checklist

1. Enter every income amount for the month in A:B.
2. Keep category names identical in the plan (D) and in the transaction log (H).
3. Update actuals once a week, not only on the last day.
4. Read variances before you change next month's plan.
5. Change one habit at a time (for example dining out), then compare the next month.

Common mistakes

• Mixing "planned" and "actual" in the same column.
• Renaming "Groceries" to "Food" halfway through the month, which breaks SUMIF.
• Putting the transaction log in A:B on top of Income.
• Do not subtract the savings transfer twice when calculating remaining cash; showing it separately as a savings-rate metric is fine.
• Comparing months that use different category lists.
• Treating one overspent week as a full-month verdict.
• Leaving F8/B10 unprotected so a zero-income month shows #DIV/0!.

Want the workbook already built?

You can recreate everything above in a blank sheet.

If you want a ready-made annual setup instead, our 2027 Finance Planner is a connected budget workbook with monthly planned-vs-actual tracking, paycheck planning, debt payoff, sinking funds, savings goals and a dashboard. One-time purchase, no subscription. It is a separate product from this DIY tutorial; use whichever fits your workflow.

This article is for personal budgeting education only. It is not financial, tax or investment advice. Seller Sheets Lab is not affiliated with or endorsed by Google LLC or Microsoft Corporation.