A budget vs actual Excel template compares planned amounts with recorded results by category and period. In Excel for Microsoft 365 on Windows, use Budget, Actuals, and Summary sheets, then calculate variance as actual minus budget and variance percentage as variance divided by budget.
What a budget-versus-actual template shows
A budget is the amount you planned to spend or earn; actual is what your records show happened. Comparing them helps you spot differences early, understand what caused them, and decide whether the plan or your actions need attention. A useful template does more than show a grand total: it makes differences visible by category and, when useful, by month.
For expenses, a positive variance using actual minus budget means spending exceeded the plan, while a negative variance means spending came in below it. That sign convention is easy to calculate, but it does not make every positive number bad. For revenue, actual revenue above budget is generally favorable. Label the columns clearly and interpret each category according to what it measures.
A practical workbook separates the original plan, the transactions or actual totals, and the comparison. Keeping those inputs distinct makes it easier to update actuals without accidentally overwriting the budget. It also gives reviewers a way to trace a summary figure back to its source.
Set up a simple workbook
Create three worksheets and name them Budget, Actuals, and Summary. On Budget, enter categories in A2:A20 and their approved budget amounts in B2:B20. Examples might include Rent, Payroll, Software, and Travel. Use one row per category and choose whether the figures represent a month, quarter, or full year; write that period near the top of the sheet.
On Actuals, create columns D:F with Date, Category, and Amount headings in row 1. Enter one actual transaction per row, with real Excel dates in column D, matching category names in E, and numeric amounts in F. A transaction list is more flexible than typing over totals because you can filter, check, or summarize the underlying records. For a small one-off comparison, actual totals can instead be entered directly on Summary, but transaction-level records are easier to audit.
On Summary, put the category names in H2:H20, with headings Budget, Actual, Variance, and Variance % in I1:L1. Keep category spelling consistent with the Budget and Actuals sheets; a trailing space or alternate spelling can make a SUMIF result omit records. Add a period label, such as April 2026 or FY2026, so a copied or printed report cannot be mistaken for a different time span.
Core formulas and a worked example
Assume Budget lists Rent at $2,000, Payroll at $6,000, Software at $500, and Travel at $1,500. Their total budget is $10,000. Suppose actual amounts for the same period are $2,150, $6,200, $450, and $1,800, respectively, totaling $10,600. The summary should show an overall $600 overrun, or 6% of budget. These figures are illustrative, not financial guidance.
With the category to summarize in Summary!H2, budget categories in Budget!A2:A20, and budget amounts in Budget!B2:B20, enter this in Summary!I2. For Rent, it returns $2,000:
=SUMIF(Budget!$A$2:$A$20,H2,Budget!$B$2:$B$20)
With the category in Summary!H2, actual categories in Actuals!E2:E1000, and actual amounts in Actuals!F2:F1000, enter this in Summary!J2. If Rent transactions total $2,150, it returns $2,150:
=SUMIF(Actuals!$E$2:$E$1000,H2,Actuals!$F$2:$F$1000)
With actual in Summary!J2 and budget in Summary!I2, enter this in Summary!K2. For Rent, it returns $150, meaning actual spending exceeded budget by $150:
=J2-I2
With variance in Summary!K2 and budget in Summary!I2, enter this in Summary!L2. For Rent, it returns 7.5%:
=IF(I2=0,0,K2/I2)
Format the variance percentage as Percentage, then fill the formulas down for the other categories. The formula returns zero when budget is zero so it avoids a divide-by-zero error; however, a zero budget is not the same as a meaningful 0% variance. Review those rows separately, or change the formula to display a blank or a note when a category has no budget. Use SUM on the budget and actual columns for total rows: the example totals are $10,000 and $10,600, and total variance is $600.
Track monthly results from transaction data
An annual or project-to-date summary can conceal timing differences. A monthly view lets you see whether an expense was merely recorded in a different month or whether the plan is being exceeded over time. It can also show seasonal activity, such as annual insurance or holiday sales, that a simple monthly average would hide.
One approach is to add a monthly summary area with the first day of the month in G2 and the category in H2. With actual dates in Actuals!D2:D1000, categories in Actuals!E2:E1000, and amounts in Actuals!F2:F1000, put this formula in I2 to total that category for the month. If April software transactions are $100, $150, and $200, and G2 contains April 1, 2026, the result is $450:
=SUMIFS(Actuals!$F$2:$F$1000,Actuals!$E$2:$E$1000,$H2,Actuals!$D$2:$D$1000,">="&$G2,Actuals!$D$2:$D$1000,"<"&EDATE($G2,1))
EDATE supplies the first day of the following month, and the less-than condition excludes it. This date-boundary method includes transactions with times on the last day of the month too. Make sure the dates are actual Excel date values, not text that merely looks like a date. If you need monthly budgets, store a budget amount for each category and month rather than dividing an annual budget evenly unless an even allocation is genuinely appropriate.
Make the variance useful to readers
Variance alone does not explain what happened. Add a Notes or Explanation column for the cause, owner, or next action. “Travel is $300 over because of a required client visit” gives a reviewer more useful context than the number alone. Keep explanations factual, concise, and tied to the period being reported.
- Show both the dollar difference and the percentage difference. A $100 change can be material in a small category and negligible in a large one.
- Use a consistent sign convention and state whether positive expense variance means over budget.
- Separate one-time costs from recurring changes when that distinction matters for future planning.
- Include the reporting period, currency, and whether figures are rounded.
- Compare like with like: an actual-to-date figure should not be compared with a full-year budget without indicating that the periods differ.
Budget phasing matters. If a department has an annual budget of $12,000 but expects most costs in December, comparing January spending with one-twelfth of the annual budget may create a misleading alert. A monthly budget plan should reflect expected timing, while preserving the approved annual total where that is the governing limit.
Format and review the workbook
Format amounts consistently as currency or numbers with an appropriate number of decimal places, and display percentages consistently. In Excel for Microsoft 365 on Windows, select a range and use Home > Conditional Formatting > Highlight Cells Rules to call attention to values above a chosen threshold. Conditional formatting should clarify the report, not replace labels: include text or a legend so color is not the only way to tell whether a result is favorable.
Turn input ranges into Excel tables using Insert > Table, and confirm that the range has headers. Tables expand as rows are added and make transaction lists easier to filter. Keep formulas in the summary area rather than mixing them into manually entered actuals. Protecting formula cells can reduce accidental edits, but protection is not a substitute for keeping an unchanged copy of the workbook and checking key totals.
Before sharing, review the transaction date range, category names, blank amounts, duplicate entries, and total calculations. A simple reconciliation is valuable: if the actuals sheet is intended to reflect a ledger or bank export, compare the included transactions and total with that source. Excel can calculate from the data supplied; it cannot determine whether the data is complete or classified correctly.
Common problems and how to avoid them
A frequent error is treating every underspend as a saving. A lower actual may reflect delayed work, an unpaid invoice, or a missed purchase rather than a lasting reduction in cost. Likewise, an overrun may be a timing issue or an approved change rather than uncontrolled spending. Use notes and compare matching periods before drawing conclusions.
Another problem is inconsistent granularity. If the budget groups all software together but actuals split products into several names, the summary can undercount. Decide on category definitions before importing records, and map source labels to those definitions. Also check whether credits and refunds are entered as negative amounts or handled another way; mixing conventions can distort totals.
Beware of blank or zero budgets. A percentage variance is undefined when the budget is zero, and a formula that displays zero can imply there was no difference. Flag such rows and explain them instead of relying on the percentage alone. Finally, do not overwrite a prior approved budget with a revised forecast. Preserve the baseline and, if needed, add a separate forecast column so readers can distinguish the original plan from the current expectation.
Excel template or budgeting software?
Excel works well for a small team, a one-time analysis, or a process where categories and reporting needs change often. It is easy to adapt, and a workbook can be shared with readers who need a straightforward comparison. The trade-off is that people must control versioning, data entry, formula changes, and access themselves. Manual imports and edits can introduce errors.
Accounting or planning software may be a better fit when actuals need to sync with a ledger, several people approve changes, access permissions matter, or reports need a controlled audit trail. Software costs, setup, and training vary, so compare the time saved and controls provided with the added expense. A spreadsheet can also serve as a useful export or review tool even when another system holds the official records. For loans, taxes, investments, or other consequential financial decisions, verify the terms and figures with the relevant lender or a qualified professional; this template is an organizational tool, not financial, tax, investment, or legal advice.
Frequently Asked Questions
What formula do I use for budget vs actual in Excel?
For a simple comparison, use actual minus budget, such as =J2-I2 when actual is in J2 and budget is in I2. Divide that difference by the budget for a percentage, while treating a zero budget as a special case.
Should a positive variance be good or bad?
It depends on the measure and convention. With actual minus budget, a positive expense variance means spending is over budget, while a positive revenue variance usually means revenue exceeded budget. Label the report and interpret each category accordingly.
How do I compare monthly actuals with an annual budget?
Use a monthly budget allocation or a period-to-date budget that matches the actual dates. Do not compare one month of actual spending directly with the full-year amount without clearly labeling the mismatch.
Can I use this template for revenue as well as expenses?
Yes. Use categories such as Product Sales or Service Revenue and enter actual revenue consistently. Because higher revenue is usually favorable while higher expense is usually unfavorable, add a favorable/unfavorable label or reverse the presentation for revenue if readers need one shared sign convention.
Why does my actual total not match the source records?
Check the reporting date range, category spelling, duplicate or missing transactions, number formats, and whether refunds or credits use a consistent sign. SUMIF only includes rows matching the category criterion; it cannot fix missing or misclassified source data.
Related
- Excel Not Responding Due to Large Data: Causes and Fixes
- Excel Not Responding and Not in Task Manager
- Excel Not Responding? How to Save Your Work
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- DVD Labels in Microsoft Word: Setup and Printing GuideYes. You can create DVD labels in Microsoft Word by opening a matching label template or laying out…
- How to Create Email Rules in Outlook 365To create an email rule in Outlook 365, open Settings, go to Mail > Rules, choose Add new…
- Excel Option Dropdown List: Create and Manage ChoicesIn Excel for Microsoft 365 on Windows, create an option dropdown list by selecting the cells that need…
- Find and Replace Enter in Excel: Remove Line BreaksTo find and replace an Enter line break inside Excel cells, press Ctrl+H, click the Find what box,…
- How to Combine Two Cells in Excel and Keep Both ValuesTo combine two cells in Excel and keep both values, put a formula in a third cell, such…
- Can You Shuffle Names in Excel? Methods and ExamplesYes. In Excel for Microsoft 365 on Windows, add a random number beside each name and sort the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.