Budgeting

How to build a monthly budget spreadsheet you will keep using

Build a monthly budget spreadsheet with budget vs actual columns, SUMIFS formulas, a 50/30/20 starting split, sinking funds and a month-end reconciliation.

A budget spreadsheet that lasts has four parts: a short list of categories, a budget column you set before the month starts, an actual column that fills itself from a transaction log, and a difference column you read at month end. Everything else is optional. Most abandoned budgets fail because the actual column is typed by hand, the category list is too long, or nobody checks the totals against the bank.

This article builds that layout, shows the formulas, and works through one month of numbers so you can see what the difference column tells you.

What columns should a monthly budget spreadsheet have?

Column Holds How it is filled
Category Rent, Groceries, Dining out Typed once
Group Needs, Wants, Savings Typed once
Budget The amount you plan to spend this month Typed each month
Actual What you spent SUMIFS from the transaction log
Difference How far off you were =C5-D5

For expense rows, calculate the difference as budget minus actual. A positive number then means under budget and a negative number means overspent. For income rows reverse it (actual minus budget), so that a positive number is always good news. Pick that rule once and apply it everywhere; a sheet that mixes sign conventions is the fastest way to misread your own month.

A percentage-used column helps on large categories: =IF(C5=0,"",D5/C5). Show it only for rows where the percentage is meaningful.

How do you fill the actual column from a transaction log?

Keep a second sheet named Transactions with four columns: Date, Description, Category, Amount. Record spending as positive numbers and keep income elsewhere, so the log holds spending only. Freeze the header row (how to freeze a header row) and give the Category column a drop-down list that reads from your category list. That matters because SUMIFS ignores a misspelled category without any warning.

Put the first day of the month in a cell, say H1, and use this formula in the Actual column:

=SUMIFS(Transactions!$D$2:$D$2000, Transactions!$C$2:$C$2000, $A5, Transactions!$A$2:$A$2000, ">="&$H$1, Transactions!$A$2:$A$2000, "<"&EDATE($H$1,1))

It adds every transaction in the category dated on or after the first of the month and before the first of the next. To start a new month, change H1; nothing else moves. Use bounded ranges like $D$2:$D$2000 rather than whole columns, and extend them if you ever approach the last row.

Add one check cell: the sum of all transactions in the month, calculated with the same date test but no category test, minus =SUM(D5:D19). It should read 0.00. Anything else is a transaction with a blank or misspelled category.

How many categories should a monthly budget use?

Ten to twenty lines is enough for a household. A category earns a line only if you would change your behavior when its difference turns negative. "Streaming services" and "Dining out" can be one "Entertainment" line unless you plan to cut one of them. Add a Miscellaneous line with a small, fixed budget; if it grows beyond that month after month, something inside it deserves its own category.

Can the 50/30/20 split give a first draft budget?

If you do not know what to budget, the 50/30/20 split gives a first draft. It comes from Elizabeth Warren and Amelia Warren Tyagi's book All Your Worth (2005): about 50% of after-tax income to needs, 30% to wants and 20% to savings, with debt payments above the minimum counted as savings. It is a check on proportions, not a law. Where rent alone takes half of take-home pay, the needs share will be higher and the others must give. The 50/30/20 budget rule page covers the definitions.

Use take-home pay, not gross salary. With $4,200 a month after tax:

Group Share Amount
Needs 50% $2,100
Wants 30% $1,260
Savings and extra debt payments 20% $840

What does one month of budget versus actual look like?

Here is a budget built on those targets, with the actual figures from the transaction log. Difference is budget minus actual.

Category Group Budget Actual Difference
Rent Needs 1,150.00 1,150.00 0.00
Groceries Needs 450.00 487.35 -37.35
Utilities Needs 180.00 164.20 15.80
Transportation Needs 150.00 172.60 -22.60
Insurance Needs 110.00 110.00 0.00
Car maintenance fund Needs 60.00 60.00 0.00
Dining out Wants 260.00 318.40 -58.40
Entertainment and subscriptions Wants 120.00 96.99 23.01
Shopping Wants 300.00 341.25 -41.25
Hobbies Wants 180.00 150.00 30.00
Travel fund Wants 250.00 250.00 0.00
Gifts and holidays fund Wants 150.00 150.00 0.00
Emergency fund Savings 400.00 400.00 0.00
Retirement outside employer plan Savings 250.00 250.00 0.00
Extra debt payment Savings 190.00 190.00 0.00

Group totals come from =SUMIF($B$5:$B$19,"Needs",D$5:D$19) and the same formula for the other groups:

Group Budget Actual Share of $4,200 Difference
Needs 2,100.00 2,144.15 51.05% -44.15
Wants 1,260.00 1,306.64 31.11% -46.64
Savings 840.00 840.00 20.00% 0.00
Total 4,200.00 4,290.79 102.16% -90.79

The month ended $90.79 over income. The sheet shows where: four lines ran over by a combined $159.60 (groceries, transportation, dining out, shopping), and three came in under by $68.81 (utilities, entertainment, hobbies). The $90.79 gap came out of the checking balance or onto a card.

The useful response is a decision, not a resolution to try harder. If groceries ran over for three months, raise that budget to a realistic number and take the money from wants. If dining out is the line you least mind cutting, cut it.

How do sinking funds handle irregular costs in a budget?

Car repairs, holiday gifts and a yearly insurance premium arrive in lumps. Without a plan, the month they land looks like a failure. A sinking fund spreads the cost: divide the yearly amount by twelve, =B2/12, and budget that as a monthly line.

Fund Yearly cost Monthly line
Car maintenance $720 $60
Travel $3,000 $250
Gifts and holidays $1,800 $150

Budget the contribution, not the eventual bill. Track each fund's balance on its own small block: previous balance plus the month's contribution minus anything spent from it. When the bill arrives, record it against the fund, not against that month's regular categories. The example above counts the three contributions as spending, which is why the month shows them at their budgeted amounts.

How do you reconcile a monthly budget every month?

The reconciliation takes about as long as a coffee and is what keeps the sheet honest.

  1. Enter or import the month's transactions from your bank and card statements.
  2. Check the arithmetic: opening balance plus income minus spending should equal the statement's closing balance. With those three figures in B2, B3 and B4, =B2+B3-B4 should match the statement to the cent.
  3. Clear any transaction that has no category, and remove duplicates.
  4. Exclude transfers between your own accounts and credit card payments. The purchases are already in the log; counting the payment too double-counts the same money.
  5. Read the difference column, and change next month's budget for any line that was wrong two months in a row.
  6. Change H1 to the next month.

What mistakes should you avoid in a monthly budget spreadsheet?

  • Budgeting gross pay. Taxes are not spendable. Use what lands in your account.
  • Aspirational numbers. Set the first budget from your last three months of actual spending, using =AVERAGE on three cells, then adjust.
  • Typing totals. Every total should be a formula. A typed total goes stale the day you add a row.
  • Too many categories. Forty lines mean forty small decisions and an abandoned sheet by March.
  • No buffer. A budget with zero slack fails on the first surprise.
  • Skipping the month end. A budget never reconciled drifts until the totals mean nothing.
  • Treating debt as one fixed line. If debt is a large part of your plan, see avalanche versus snowball and the debt payoff guide.

How do the pieces of a monthly budget fit together?

  • Write categories and groups once, with a drop-down list feeding the log.
  • Set budgets from take-home pay, starting from 50/30/20 if you have no better data.
  • Fill Actual with SUMIFS, never by hand, and keep a check cell that reads 0.00.
  • Make irregular costs monthly lines through sinking funds.
  • Reconcile to the bank before reading anything into the differences.

If you would rather start from a working file, the monthly household budget on Sheet Reserve is a free sample with this budget-versus-actual structure. The annual budget planner plans the same categories month by month across the year. For how to organize any sheet like this, see inputs, calculations and outputs.

Keep reading