Excel Budget Variance Formula: How to Calculate It

Formulas & functions · Updated November 22, 2026

The core Excel budget variance formula is =Actual - Budget, which returns the difference in dollars, or =(Actual - Budget) / Budget, which returns the variance as a percentage. For example, if your budgeted amount is in cell B2 and the actual amount is in C2, the dollar variance formula is =C2-B2 and the percentage variance formula is =(C2-B2)/B2. This article explains how to set up these formulas, interpret positive and negative results, and avoid common errors when tracking a budget in Excel.

What a Budget Variance Actually Measures

A budget variance is simply the difference between what you planned to spend or earn (the budget) and what actually happened (the actuals). In financial reporting, a variance can be favorable or unfavorable. For revenue, actual higher than budget is favorable. For expenses, actual lower than budget is favorable. The formula itself does not label results—you interpret the sign based on the context. A positive variance for an expense line item means you overspent; a positive variance for a revenue line means you earned more than planned.

Setting Up Your Spreadsheet for Variance Formulas

To use the formulas cleanly, lay out your data in columns. A typical layout has the item name in column A, budget in column B, actual in column C, and variance in column D. For example:

  • A2: “Rent”
  • B2: 1500 (budget)
  • C2: 1480 (actual)
  • D2: =C2-B2 → result 20 (dollars under budget)
  • E2: =(C2-B2)/B2 → result 0.0133 or 1.33% formatted as a percent

If you have many rows, enter the formula in D2 and drag it down. For the percentage column, format the cells as percentages (Home > Number > Percent Style) so Excel multiplies by 100 and adds the % sign.

Using the ABS Function to Show Variance Size

Sometimes you only care about the magnitude of the difference, not whether it is positive or negative. The ABS function returns the absolute value. For example, =ABS(C2-B2) gives 20 whether actual is 1480 or 1520. This is useful in dashboards where a large variance, regardless of direction, triggers a review. You can combine it with conditional formatting to highlight any variance above a threshold, such as =ABS(C2-B2)>100.

Percentage Variance Formula and the Division by Zero Problem

The percentage variance formula =(C2-B2)/B2 fails when the budget is zero, because Excel returns a #DIV/0! error. To handle this, wrap the formula in IFERROR: =IFERROR((C2-B2)/B2, "N/A"). This displays “N/A” when the budget is zero. Alternatively, you can use IF(B2=0, 0, (C2-B2)/B2) to show a zero instead. In practice, a zero budget with actual spending is an unusual case that deserves manual review, so the error message can be a helpful flag.

Comparing Multiple Periods or Categories

For a budget that spans months or departments, you can use the same formulas with structured references. If you have a table with columns named “Budget” and “Actual”, you can write =[@Actual]-[@Budget] in a calculated column. For a pivot table, you can add a calculated field that subtracts one sum from another. For example, create a calculated field named “Variance” with the formula =Actual - Budget after adding both fields to the Values area. This works for any grouping—month, department, project—without rewriting formulas for each row.

Common Mistakes and How to Avoid Them

One frequent error is mixing up the order of subtraction. Always put actual first: =Actual - Budget. Reversing it flips the sign and makes overspending look favorable. Another mistake is using absolute references when you intend relative ones. If you copy a formula like =C2-B2 down a column, Excel adjusts the row numbers automatically. But if you add a $ sign (e.g., =$C$2-$B$2), the formula will not change when copied, which is usually wrong for a list of items. Also, be careful with percentage formatting: if you type =0.1 and format as percentage, Excel shows 10%—but if you type =(C2-B2)/B2 and the result is 0.1, formatting as percent shows 10%, which is correct. Do not multiply by 100 manually if you are using the percent format, or you will get 1000%.

Using Conditional Formatting to Flag Variances

To make variances easy to spot, apply conditional formatting to the variance column. Select the variance cells, go to Home > Conditional Formatting > Highlight Cells Rules > Greater Than, and enter a threshold like 100. Then add another rule for Less Than -100. For a more visual approach, use Data Bars (Home > Conditional Formatting > Data Bars) to show the relative size of variances. For percentage variances, you can use a color scale (Green-Yellow-Red) where green is favorable and red is unfavorable, but remember that favorable depends on whether the line is revenue or expense—so you may need separate rules for each category.

Frequently Asked Questions

What does a negative variance mean? A negative variance means actual is less than budget. For expenses, that is favorable (you spent less). For revenue, it is unfavorable (you earned less). The formula does not know which is which, so you must interpret based on the line item.

Can I use the variance formula for a whole budget summary? Yes. If you have total budget in B10 and total actual in C10, use =C10-B10 for the total dollar variance and =(C10-B10)/B10 for the total percentage variance. This works for any summary row.

How do I show variance as “favorable” or “unfavorable” text? Use a nested IF formula. For an expense line, =IF(C2<=B2, "Favorable", "Unfavorable"). For revenue, reverse the comparison: =IF(C2>=B2, "Favorable", "Unfavorable"). You can combine this with the dollar amount using the & operator, e.g., =IF(C2<=B2,"Favorable","Unfavorable") & " " & TEXT(ABS(C2-B2),"$#,##0").

Why is my percentage variance showing as a decimal like 0.15 instead of 15%? That is the raw value. Select the cell, go to Home > Number, and choose Percentage format. Excel will display 15%. If you already formatted as percentage and it shows 1500%, you likely multiplied by 100 in the formula—remove the *100.

Does the formula work in Google Sheets? Yes, the same formulas work in Google Sheets with identical syntax. The only difference is the menu path for formatting: Format > Number > Percent in Sheets, versus Home > Number > Percent in Excel.

What if my budget and actual are in different sheets? Reference the other sheet directly in the formula, e.g., =Actual!C2 - Budget!B2. Make sure the sheet names match exactly, and if a sheet name contains spaces, enclose it in single quotes like ='Actual Data'!C2.

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.