Monthly Budget Template for Excel and Google Sheets: Planned vs Actual, Month by Month

What should a monthly budget template include?

A monthly budget template needs your take-home pay at the top and one row per category with five columns: category, group, planned, actual, and actual minus planned. Below that sit a total row and one cell that shows pay minus the total. Everything else is decoration.

That matches how a US government consumer site describes the job. Consumer.gov calls a budget "a plan you write down to decide how you'll spend your money each month" and gives three steps: list your bills and other expenses, write down your monthly income, and subtract one from the other. Its guidance is that the result should be more than zero.

The planned column is the plan. The actual column is the check. With only one of the two you have either a wish list or a receipt. I build spreadsheet maths; I am not a financial adviser, and none of this is financial advice.

Worked example: one month, planned vs actual

Here is a full month on $4,200 of take-home pay. The plan assigns every dollar across 16 categories. The actual column shows a normal month: a few lines over, a few under, and a total that missed by $24. Every figure is an assumption for illustration.

Three things to read from the last column:

• Needs ran $89 over. Utilities (+$26), groceries (+$42) and transport (+$21) all drifted up. None is large alone.
• Wants ran $65 under. Eating out was $68 over, but entertainment, clothing and everything else were $133 under between them.
• Savings went out as planned, because those four lines were treated as bills and paid first.

The total was $4,224 against $4,200 of pay. That $24 came from somewhere: last month's leftover, or a card balance. A budget without an actual column would never have shown it. Next month you either raise the groceries plan to match reality and cut another line by the same amount, or you hold the plan and change the shopping.

Check how your plan splits (free budget calculator)

Type your take-home pay, the share of it you plan for needs, wants and savings, and your fixed monthly bills. The calculator returns the three budgets in dollars and what is left in the needs budget after the fixed bills. It starts on the example plan.

The calculator's heading mentions 50/30/20 because it opens on that split elsewhere on this site. Here it holds the example plan's own shares: 55% needs, 25% wants, 20% savings. Change them to yours. They should add to 100%.

The fixed bills in the example are rent, utilities, phone and internet, insurance and debt minimums: $1,850.00, which is 44.05% of take-home pay. That leaves $460.00 of the needs budget for groceries ($330) and transport ($130). If you want to test your plan against the 50/30/20 rule itself, the 50/30/20 budget spreadsheet guide covers the rule and what to do when needs run past half your pay. This guide does not repeat it.

How do I make a spreadsheet for monthly bills?

List each bill on its own row with the amount, the day it is due and how it is paid, then total the amount column. That total is the fixed part of your month. Add the yearly bills too, divided by 12.

Sort the list by due day to see which bills land before each payday.

Bills that do not come monthly

Annual and occasional costs break a monthly budget because they appear in one month and nowhere else. Add them up for the year and divide by 12. Example, with assumed amounts: car registration $180, holiday gifts $480 and annual subscriptions $240 come to $900 a year. $900 / 12 = $75 a month. That is the "irregular bills fund" row in the example. You move $75 aside every month and pay those bills from it when they arrive.

Monthly budget template in Excel: the formulas

Six formulas run the whole sheet. Put the first day of the month in B1 and take-home pay in B2. Categories start in row 5: category in A, group in B, planned in C, actual in D, difference in E. The same syntax works in Google Sheets.

You can type the actual column by hand from your bank statement once a month. That is the simplest version and it works. The SUMIFS version needs a second tab called Tx where you log each transaction with a date, a description, a category and an amount. It takes longer to keep, but the actual column then fills itself in.

For a full year, copy the tab 12 times, one per month, and change B1 on each. A thirteenth tab can total each category across the year by adding the same cell from every month tab.

How do I budget monthly when I'm paid every two weeks?

Build the monthly budget on two paychecks and treat the third paycheck, which arrives twice a year, as separate money with its own plan. A two-week pay cycle gives 26 paychecks a year, not 24.

Example, with an assumed $2,100 take-home paycheck:

• 26 paychecks x $2,100 = $54,600 a year.
• Two paychecks a month = $4,200. That is the monthly budget.
• 12 months x $4,200 = $50,400. The other $4,200 arrives as a third paycheck in two months of the year.

The alternative is to average: $54,600 / 12 = $4,550 a month. The average is accurate over a year, but ten months of the year you will have $350 less in hand than the budget says. Budgeting on two paychecks avoids that. Decide in advance where the two extra paychecks go, and write it in the sheet.

If your pay changes every month, consumer.gov suggests adding up last year's income and dividing by 12 for a monthly estimate. For a month-by-month view of uneven money in and out, see the cash flow forecast template guide.

How can I create a realistic monthly budget?

Start from what you spent, not from what you wish you spent. Fill the planned column with the average of your last three months of actual spending per category, then change one or two lines on purpose.

The mistakes that make a budget unrealistic:

• Budgeting on gross pay. Use take-home pay, the amount that reaches your account.
• Leaving out yearly bills. The $75 a month in the example is $900 that would otherwise land on one month.
• No row for the unplanned. The example has an "everything else" row of $300. A budget with no slack breaks on the first surprise.
• Planned amounts that do not add up to pay. If the plan totals less than your pay, the rest gets spent without a decision. If it totals more, the plan cannot be kept.
• Treating savings as what is left over. In the example the four savings rows are paid first, like bills, and they were the only group that hit plan exactly.
• Never looking at the difference column. The plan only improves when last month's differences change next month's planned figures.

If one of your rows is debt, the order you pay it off in changes the interest you pay. The debt snowball spreadsheet guide compares the two payoff orders on the same example.

Free budget template vs a paid planner vs an app

A free template or the layout in this guide is enough for a plain monthly budget, and you do not need to buy anything to follow it. Pay for a file or an app only if it does something you would otherwise not do, such as a debt payoff schedule or automatic bank imports.

For a free printable, consumer.gov publishes a one-page Make a Budget worksheet as a PDF.

The budget spreadsheet template from ProSheet Studio is $14.99 one-time for Excel and Google Sheets. Be clear about what it is before you buy. Its planner works at group level: you enter take-home pay and your totals for needs, wants and savings, and it returns a planned figure for each (it starts at the 50/30/20 targets and you can type your own), actual minus planned, a status in words and the amount left to assign. It does not have the 16-row category list or the 12 month tabs described here; you would build those yourself from this guide. What it adds is a Debt Payoff tab that takes up to eight debts and runs the snowball and avalanche orders month by month with interest.

So: if the category table is all you want, build it for free. If you also want a debt-free month worked out, the monthly budget planner with debt payoff saves building that part. The same page offers a free 50/30/20 Budget Starter by email.

What to put in your monthly budget template: take-home pay; one row per category with a group; planned, actual and difference columns; subtotals per group; a total; pay minus total; a bills list with due days; and a monthly amount for yearly bills.

Step-by-step: Monthly Budget Template for Excel and Google Sheets: Planned vs Actual, Month by Month

1. Write down take-home pay. Use the amount that reaches your account in a month. Paid every two weeks? Use two paychecks.
2. List your bills. One row per bill with amount and due day. Add yearly bills divided by 12. Example: $900 a year is $75 a month.
3. Add the flexible categories. Groceries, transport, eating out, entertainment, clothing and a row for everything else. Tag each row needs, wants or savings.
4. Fill the planned column. Start from your last three months of spending. Adjust until the planned total equals take-home pay. The example assigns all $4,200.00.
5. Record the actual column. Type each category's total at month end, or total a transaction list with SUMIFS.
6. Read the difference column. Actual minus planned shows where the month drifted. The example ran $89 over on needs and $65 under on wants.
7. Adjust next month's plan. Move planned amounts toward what happened, or decide what changes. Then copy the tab for the new month.

Related guides