Monthly Budget vs Actual Excel Template: How to Build One

A monthly budget vs actual Excel template compares what you planned to spend with what you actually spent, then calculates the difference for each category and for the month overall. In Microsoft Excel for Microsoft 365 on Windows, set up columns for Category, Budget, Actual, Variance, and Variance %, then use formulas such as =C2-B2 to calculate each category’s variance.

What a budget-versus-actual template shows

A budget is a plan; actuals are the amounts recorded after transactions happen. Comparing them helps answer practical questions: Did groceries exceed the plan? Was a utility bill unusually high? Did the month finish below the planned spending total? A spreadsheet makes those differences visible rather than relying on memory or a bank balance alone.

A useful template usually includes one row per category and one column per measure. For example, an expense-focused sheet might have columns for category, budget, actual, variance, and variance percentage. A total row summarizes the month. You can also add notes for one-off purchases or explain why a category changed, but avoid adding so many fields that updating the sheet becomes a chore.

“Actual” should mean a consistent thing in your workbook. You might enter posted transactions from a bank statement, or record transactions as they occur. Avoid mixing pending transactions with cleared transactions without labeling them, because the comparison can look inconsistent when the statement balance and worksheet differ.

Set up the worksheet

In a blank workbook, enter a month and year near the top, then create headers in row 1: Category in A1, Budget in B1, Actual in C1, Variance in D1, and Variance % in E1. Add spending categories in A2:A6, such as Rent, Groceries, Utilities, Transport, and Subscriptions. Enter the planned amount in column B and the month’s recorded spending in column C.

Here is an illustrative set of values: rent budget $1,200 and actual $1,200; groceries $500 and $560; utilities $250 and $270; transport $150 and $130; subscriptions $80 and $80. These are sample numbers, not recommended spending levels. Add a Total row in row 7, and format the money columns as currency using Home > Number.

Keep the data structure simple: one category per row, one month per worksheet, and numbers stored as numbers rather than typed with explanatory text. For instance, enter 500 and apply currency formatting instead of entering “$500 groceries.” This makes totals and calculations work reliably.

Use formulas to calculate the difference

For a layout with budget amounts in B2:B6 and actual amounts in C2:C6, enter this formula in D2 to subtract budget from actual: =C2-B2. Copy it down through D6. For groceries in the example, the result is $60, meaning actual spending was $60 above budget. A negative result means actual spending was below budget.

For the total row, with category budgets in B2:B6 and actual amounts in C2:C6, enter this formula in B7 to add the budgets: =SUM(B2:B6). The result is $2,180. With actual amounts in C2:C6, enter this formula in C7: =SUM(C2:C6). The result is $2,240. With the total budget in B7 and total actual in C7, enter this formula in D7: =C7-B7. The result is $60 over budget, matching the category differences.

A percentage can help compare the size of a difference across categories. With a category’s budget in B2 and its variance in D2, enter this formula in E2, then copy it down: =IF(B2=0,"",D2/B2). Format column E as Percentage. For groceries, $60 divided by a $500 budget is 12%. The formula leaves the percentage blank when the budget is zero, because a percentage of a zero budget is not a useful comparison. A nonzero actual amount in a category with no budget still appears in the dollar variance.

Choose categories and define the rules

Useful categories are specific enough to explain meaningful changes but not so detailed that tracking becomes burdensome. Someone who wants to monitor food spending might use separate rows for groceries and restaurants; someone who only needs a broad view could combine them as Food. The right level depends on what decisions the comparison needs to support.

Before entering numbers, decide how to handle irregular costs. Annual insurance, school fees, and car repairs may not occur every month. You can set aside a planned monthly amount for these costs, or show the full expense in the month it is paid. Either approach can be reasonable for tracking, but mixing approaches between months makes comparisons harder to interpret.

Use the same category names month to month so trends remain comparable.
Decide whether credit-card spending is recorded when a purchase is made or when the card bill is paid. Recording both can count the same expense twice.
Separate transfers between your own accounts from purchases if you want the sheet to measure spending rather than cash movement.
Use a notes column for unusual items that explain a temporary spike.

Read variance in context

For spending categories, the formula actual minus budget has a straightforward sign: a positive number means overspending relative to the plan, while a negative number means spending was below the plan. But “below budget” is not automatically good. It could reflect a genuine saving, a bill that has not arrived yet, or a purchase postponed until next month.

Income needs a different interpretation. If actual income is greater than budgeted income, that is usually favorable; for expenses, higher actual spending is usually unfavorable. If you include income and expenses in one worksheet, label the rows clearly and interpret variance by type rather than applying one color rule to every row. A simple expense-only sheet avoids much of this ambiguity.

Percentages also need context. A $30 difference on a $60 category is 50%, while a $30 difference on a $1,500 category is 2%. Consider both the dollar amount and percentage before deciding a variance deserves attention. When the planned amount is zero, compare the dollar value and investigate the unplanned expense rather than trying to interpret a percentage.

Make the sheet easier to scan and maintain

Format budget, actual, and variance columns consistently as currency, and use percentage formatting for the percentage column. Freeze the header row if the list grows: in Excel for Windows, select the row below the headers and choose View > Freeze Panes > Freeze Panes. You can use Home > Conditional Formatting to highlight positive or negative values, but set the rule to match the meaning of your sheet. For an expense-only variance column, highlighting positive values can draw attention to overspending.

Avoid relying on color alone. Keep the numbers visible and consider adding a text label or a short note for important exceptions. Check that copied formulas refer to the correct row, and verify that the total row sums only the category rows—not itself. If a formula displays as text or returns an unexpected result, check whether the cell is formatted as Text, whether amounts were entered as numbers, and whether the formula starts with an equals sign.

Excel’s Insert > Table feature can make category lists easier to filter and extend. A table can automatically carry formatting and formulas into new rows, but it is not required for a basic monthly comparison. If you add categories beneath the existing list, update any fixed-range totals so the new rows are included.

One sheet per month or a year-long workbook?

A separate worksheet for each month is approachable: duplicate the sheet, update the month label, and enter new actuals. It is easy to print or review a single month, but comparing categories across the year may require switching tabs or building a summary.

A single data table can be more flexible for longer-term reporting. It can store Month, Category, Budget, and Actual as separate fields, with one row for each category-month combination. You can then filter by month or category, or build a PivotTable to summarize results. The trade-off is that the table needs a consistent structure and may feel less direct to someone who wants a one-page monthly view.

For a first template, choose the arrangement you are most likely to update. A detailed workbook that is abandoned is less useful than a compact sheet maintained regularly. If using monthly tabs, keep category order and definitions consistent and consider a year-to-date summary only after the monthly process is working.

Common problems and how to avoid them

One common mistake is treating the account balance as the month’s actual spending. A balance can include earlier deposits, transfers, pending card purchases, and payments for expenses recorded in another month. The sheet is more informative when actuals come from a consistent transaction list or statement and follow a clearly stated timing rule.

Another problem is double-counting. If you record each card purchase as an expense, then also record the payment to the card as an expense, the same spending may appear twice. Similarly, moving money from checking to savings is not usually a purchase. Decide whether the workbook tracks expenses, cash flows, or both, and label transfers accordingly.

Finally, investigate unusual results before changing the plan to make the numbers look better. A recurring shortfall might mean the budget needs revising, but one high bill might be a one-time event. Keep the original plan available if you want to assess how well it performed; changing the budget after the fact can obscure what the comparison was meant to show.

Template options and alternatives

You can create the worksheet from a blank Excel workbook, adapt a built-in template if one suits your needs, or download a spreadsheet from another provider. Before using a downloaded file, check that its categories, formulas, and date conventions fit your purpose. A template may calculate a variance as budget minus actual rather than actual minus budget, so confirm the sign before interpreting its colors or totals.

A spreadsheet is not the only option. A budgeting app may import transactions and categorize them automatically, while a paper worksheet can be adequate for a small number of categories. Excel is useful when you want control over categories, calculations, and layout; it also requires you to maintain the data and review formulas. Do not treat a budget template as financial, tax, or legal advice. Check actual account terms with the relevant institution and consult a qualified professional for advice specific to your circumstances.

Frequently Asked Questions

What formula compares budget to actual in Excel? If budget is in B2 and actual is in C2, use =C2-B2. A positive result means actual is higher than budget; for an expense category, that generally indicates overspending.

How do I calculate the budget variance percentage? If budget is in B2 and variance is in D2, use =IF(B2=0,"",D2/B2) and format the result as a percentage. The example avoids showing a misleading percentage when the budget is zero.

Should variance be budget minus actual or actual minus budget? Either convention can work if it is clearly labeled. This template uses actual minus budget, so positive expense variances indicate spending above plan. Check the convention before comparing with another workbook.

Can I use one Excel worksheet for the whole year? Yes. You can use separate monthly tabs or keep a year-long table with a month field. Separate tabs are simple to review, while a single table is easier to filter and summarize across months.

Why is my total variance different from the amount in my bank account? A budget variance compares planned and recorded amounts, not necessarily the account balance. Timing differences, transfers, pending purchases, omitted transactions, and duplicate entries can all explain a mismatch. Review the workbook’s recording rules and reconcile the transaction list to the relevant statement.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.