Budget vs Actual Excel Formula: Examples and Setup

Formulas & functions · Updated November 19, 2026

If budget is in B2 and actual spending or revenue is in C2, the basic budget-versus-actual formula is actual minus budget: =C2-B2. The result is the dollar variance; divide it by the budget to get the percentage variance, while keeping in mind that a positive result means different things for revenue and expenses.

The Basic Budget vs Actual Formula

Set up a worksheet with one row per category and columns for the category, budget, actual, variance, and variance percentage. For example, use A for the category, B for budget, C for actual, D for variance, and E for variance percentage. In row 2, enter the formula below in D2, then fill it down for the other categories.

With budget in B2 and actual in C2, enter this in D2: =C2-B2. If the budget is $10,000 and actual is $11,200, the result is $1,200: actual is $1,200 above budget.

The formula is deliberately simple: actual minus budget. A positive result means actual exceeded the budget, a negative result means actual was below budget, and zero means the two amounts match. Whether exceeding the budget is good or bad depends on what the row measures, so the sign alone is not a performance judgment.

Set Up the Worksheet

Use consistent category names and time periods before comparing figures. A monthly actual should be compared with the budget for that same month, not with an annual budget. A practical starting layout is:

  • Column A: category, such as Rent, Payroll, or Product revenue.
  • Column B: budget for the period.
  • Column C: actual for the same period.
  • Column D: dollar variance.
  • Column E: variance as a percentage of budget.

Keep amounts as numbers, not text containing typed dollar signs or commas. Select the amount columns and use Home > Number to apply Currency or Accounting formatting. For a percentage result, use Percentage formatting. Formatting changes how a value appears, not the underlying calculation.

For reliable comparisons, decide whether your budget and actual figures include the same items. If the budget includes tax or one-time costs but the actual does not, the result will not be an apples-to-apples variance. Add notes or separate rows for adjustments rather than silently mixing different definitions.

What the Variance Sign Means

For revenue, actual above budget is commonly favorable; for costs and expenses, actual above budget is commonly unfavorable. The basic actual-minus-budget formula still works for both, but its sign does not automatically label the outcome as favorable or unfavorable.

  • Revenue example: budget is $20,000 and actual revenue is $18,000. Actual minus budget is -$2,000, meaning revenue was $2,000 below plan.
  • Expense example: budget is $4,000 and actual expense is $4,500. Actual minus budget is $500, meaning spending was $500 above plan.
  • Expense below budget: budget is $4,000 and actual expense is $3,600. The basic formula returns -$400, meaning spending was $400 below plan.

Some reports instead define favorable expense variance as budget minus actual, so that underspending appears positive. With budget in B2 and actual expense in C2, the expense-oriented formula is =B2-C2. In the $4,500 example, it returns -$500, indicating an unfavorable overspend under that convention. State the convention in a heading or note, and do not mix the two conventions in one report.

Also, spending below budget is not always automatically favorable. It may reflect savings, but it could also mean a planned activity was delayed or not completed. Excel can calculate the difference; interpretation requires context about the category and the reporting period.

Calculate Percentage Variance

Percentage variance expresses the difference relative to the budget. With budget in B2 and actual in C2, enter this in E2: =IF(B2=0,"N/A",D2/B2). This assumes D2 already contains the dollar variance from actual minus budget. Format E2 as Percentage and fill the formula down.

For a $10,000 budget and $11,200 actual, D2 is $1,200 and E2 is 12%. For a $10,000 budget and $9,000 actual, the variance is -$1,000 and the percentage is -10%. The sign follows the actual-minus-budget convention; it does not by itself say whether the result is favorable.

A zero budget needs special treatment because a percentage change relative to zero is undefined. The formula above displays N/A rather than dividing by zero or implying a misleading 0%. If your organization uses another label, such as “New” for an unplanned expense, replace the text label to match the reporting rules. For a nonzero budget, the formula returns the numeric percentage as expected.

Percentage variance can also be calculated directly from the budget and actual cells. With budget in B2 and actual in C2, the direct formula is =IF(B2=0,"N/A",(C2-B2)/B2). This is mathematically equivalent to dividing the variance in D2 by the budget, provided D2 uses actual minus budget.

Calculate Totals Correctly

For a list of categories in rows 2 through 10, place the budget total in B11 and actual total in C11. With budget values in B2:B10, the budget total formula in B11 is =SUM(B2:B10). With actual values in C2:C10, the actual total formula in C11 is =SUM(C2:C10).

Then calculate the overall dollar variance from the totals. With total budget in B11 and total actual in C11, enter in D11: =C11-B11. For example, if the summed budget is $50,000 and summed actual is $53,000, the total variance is $3,000 over budget.

Calculate the overall percentage from the totals, not by averaging the percentages in E2:E10. With total budget in B11 and total actual in C11, enter in E11: =IF(B11=0,"N/A",(C11-B11)/B11). If the total budget is $50,000 and actual is $53,000, this returns 6%. Averaging individual category percentages can distort the overall result because a small category and a large category would receive equal weight.

When budgets and actuals contain credits, refunds, or negative values, verify that the signs are consistent with your accounting convention. A sum can be mathematically correct while a sign convention makes its business meaning unclear. Keep the source figures and document any adjustments so another reader can trace the totals.

Build a Readable Variance Report

Once the formulas are in place, format the report so readers can spot important differences without mistaking formatting for analysis. Select the variance cells and use Home > Conditional Formatting > Highlight Cells Rules to apply a rule such as Greater Than or Less Than. Choose thresholds that make sense for the report; coloring every small variance can make a sheet harder to read.

For expense rows using actual minus budget, a positive variance generally means an overspend, while a negative variance generally means underspending. For revenue rows, those interpretations reverse. You can use separate rules or separate sections for income and expenses, and include labels such as “Over/(under) budget” or “Actual less budget” to make the direction explicit.

If you need to review several periods, put each month in its own column or create a structured table with one row per category and period. Avoid comparing a partial-month actual with a full-month budget without labeling it. A report that includes the period, currency, variance convention, and any known exclusions is easier to interpret and audit.

Common Errors and Useful Checks

  • Reversing the subtraction: =C2-B2 and =B2-C2 produce opposite signs. Choose the convention first and label it clearly.
  • Dividing by actual instead of budget: Standard budget variance percentage uses the budget as the baseline. Dividing by actual answers a different question.
  • Comparing different periods: Match the budget period to the actual period, and check that both use the same categories and accounting basis.
  • Ignoring a zero budget: A percentage relative to zero is undefined. Use a clear label or a separately defined policy for unbudgeted items.
  • Adding rounded display values: Currency formatting may show whole dollars even when cells contain cents. Excel calculates with the stored values, so displayed totals can appear off by a cent or more.
  • Using averages for overall percentages: Divide total variance by total budget instead of taking a simple average of category percentages.

After copying formulas down, check a few rows by hand: confirm the input cells, subtract actual and budget in the stated order, and compare the result with Excel. If a formula shows as text rather than calculating, check that the cell is not formatted as Text and that the entry begins with an equals sign. These checks catch common setup errors before a report is shared.

Frequently Asked Questions

What is the Excel formula for budget versus actual?
With budget in B2 and actual in C2, use =C2-B2 for dollar variance. A positive result means actual is above budget; interpret whether that is favorable based on whether the row is revenue or expense.

How do I calculate budget variance percentage in Excel?
With budget in B2, actual in C2, and variance in D2, use =IF(B2=0,"N/A",D2/B2), then format the result as a percentage. This uses budget as the comparison baseline.

Should actual be subtracted from budget, or budget from actual?
Either order can be used if it is clearly defined. Actual minus budget is common and makes an overspend positive; budget minus actual is sometimes used for expenses so underspending is positive. Do not switch conventions without labeling the report.

How do I compare actuals with a zero budget?
The dollar variance can still be calculated as actual minus budget, but percentage variance against zero is undefined. Display a label such as N/A or apply a documented rule for unbudgeted activity rather than presenting a misleading percentage.

Can I average the category variance percentages for the total?
Usually not. Calculate the overall percentage as total actual minus total budget, divided by total budget. This weights the result by the size of each budget rather than treating every category as equally large.

Does the formula change in Excel for Mac or Excel for the web?
No. These formulas use basic Excel arithmetic and SUM and work in Microsoft 365 for Windows, Excel for Mac, and Excel for the web. The ribbon layout can differ slightly by platform, but the calculation itself is the same.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

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

Spotted an error in this guide? Let us know.