Formula for Over or Under Budget in Excel: Examples

Formulas & functions · Updated November 20, 2026

To calculate whether spending is over or under budget in Excel, subtract the budget from the actual amount: with actual spending in B2 and budget in C2, enter =B2-C2. A positive result means over budget, a negative result means under budget, and zero means exactly on budget.

What the budget variance means

The difference between actual spending and the budget is called a budget variance. The subtraction order matters: actual minus budget makes overspending positive and underspending negative. This is a common convention for tracking expenses because a positive variance flags an amount that needs attention.

For example, if a team planned to spend $1,200 and actually spent $1,350, the variance is $150 over budget. If it spent $1,080 instead, the variance is -$120, meaning it spent $120 less than planned. The negative sign is not an error; it communicates that actual spending came in below the budget.

Some organizations prefer to show the remaining budget as a positive number when there is money left. That is a different measure: budget minus actual. Choose the direction that fits your report, label it clearly, and use the same convention throughout the workbook.

Set up the worksheet

A simple layout makes the calculation easy to read and copy down. Put each expense category on its own row, with a heading for each column. For example:

  • Column A: category, such as Travel or Supplies
  • Column B: actual amount spent
  • Column C: budgeted amount
  • Column D: variance
  • Column E: status or variance percentage, if needed

Enter amounts as numbers, not as text containing currency symbols. You can apply a currency format after entering them. Put the first category on row 2, then enter the variance formula in D2. When the references are relative, copying the formula down adjusts the row numbers automatically, so the next row compares its own actual amount with its own budget.

For a clear example, suppose Supplies has actual spending of $1,350 in B2 and a budget of $1,200 in C2. The result in D2 is $150. The formula calculates from the cell values; the currency symbol is supplied by number formatting, not by the arithmetic itself.

Calculate the amount over or under budget

With actual spending in B2 and budget in C2, enter this in D2:

=B2-C2

For the example values, Excel calculates 1350 minus 1200, which returns 150. With actual spending of $1,080 and the same $1,200 budget, it calculates 1080 minus 1200, returning -120. Format D2 as currency if you want those results displayed as $150.00 or -$120.00.

To apply the calculation to additional categories, select D2 and drag the fill handle at the lower-right corner of the cell down the column. You can also copy D2 and paste it into the remaining variance cells. Check that each row refers to its own actual and budget cells; for example, the next row should compare B3 with C3.

If your workbook places budget before actual, adapt the references rather than copying the example blindly. The key rule for a positive-over convention is always actual minus budget. Reversing the order flips the signs.

Add an over, under, or on-budget label

A numeric variance is useful for calculations, while a text label can make a summary easier to scan. With actual spending in B2 and budget in C2, enter this in E2:

=IF(B2>C2,"Over budget",IF(B2<C2,"Under budget","On budget"))

The formula checks whether actual spending is greater than the budget, then whether it is less. If neither condition is true, the amounts are equal and the result is “On budget.” For actual spending of $1,350 and a $1,200 budget, it returns Over budget; for $1,080 against $1,200, it returns Under budget.

Copy the formula down to label the other rows. Keep in mind that the result is text, not a number, so it is useful for display and filtering but cannot replace the variance amount in further arithmetic. If you only need a two-way label and want equality grouped with under budget, you can use a simpler test, but that may hide categories that are exactly on plan.

Calculate the variance as a percentage

A dollar variance alone can be misleading when budgets differ in size. Being $100 over a $500 budget is a larger proportional miss than being $100 over a $10,000 budget. Variance percentage expresses the difference relative to the budget.

With actual spending in B2 and budget in C2, enter this in F2 to return a blank when the budget is zero:

=IF(C2=0,"",(B2-C2)/C2)

For actual spending of $1,350 and a $1,200 budget, the calculation is 150 divided by 1,200, or 0.125. Format the result as a percentage to display 12.5%. For actual spending of $1,080 against $1,200, the result is -10% after formatting. A positive percentage is over budget, and a negative percentage is under budget under this convention.

To apply percentage formatting, select the result cells and choose Home > Number > Percent Style. Adjust decimal places with the increase or decrease decimal controls in the Number group if the report needs more or less precision. A zero budget needs special care: a percentage relative to zero is undefined, so the formula displays a blank rather than a division error. If you prefer a visible note, replace the empty text argument with text such as “No budget”; remember that this makes the result column a mix of numbers and text.

Show remaining budget instead

If the main question is “How much money is left?”, use budget minus actual. With budget in C2 and actual spending in B2, enter this in G2:

=C2-B2

For a $1,200 budget and $1,080 of spending, the result is $120 remaining. If spending reaches $1,350, the result is -$150, indicating a $150 overrun. This measure is often intuitive for available funds, but the sign interpretation is the reverse of actual-minus-budget: a positive number means funds remain, while a negative number means spending exceeded the plan.

A report can include both measures, but avoid presenting two unlabeled figures that appear to conflict. Use headings such as “Variance (actual minus budget)” and “Budget remaining” so readers know what each sign means.

Compare totals as well as individual categories

For a department-wide result, compare the total actual spending with the total budget. This is different from simply looking at one category: a category overrun may be offset by savings elsewhere. If actual amounts are in B2:B10 and budgets are in C2:C10, enter this in D11 to calculate the overall variance:

=SUM(B2:B10)-SUM(C2:C10)

For example, if the actual amounts sum to $8,450 and the budgets sum to $8,000, the result is $450 over budget. Check that the ranges include all relevant rows and exclude any subtotal or grand-total rows already included in the range; otherwise, amounts can be counted twice.

The overall percentage should use total variance divided by total budget, not an unweighted average of category percentages. With actual amounts in B2:B10 and budgets in C2:C10, enter this in E11:

=IF(SUM(C2:C10)=0,"",(SUM(B2:B10)-SUM(C2:C10))/SUM(C2:C10))

If those totals are $8,450 actual and $8,000 budget, the percentage is 450 divided by 8,000, or 5.625% over budget. Format E11 as a percentage. Averaging the individual percentages would give a different result when categories have different budget sizes, so use the total-based calculation when you want the percentage for the combined budget.

Format results so exceptions stand out

Formatting helps readers understand the numbers without changing the underlying formulas. Select the variance cells and choose Home > Number > Currency or Accounting to show monetary values. For percentage variance, use Percent Style. Keep negative values visible: a custom number format or a display setting that hides minus signs could make an underspend look like an overrun or obscure the difference altogether.

You can also use conditional formatting to highlight positive and negative amounts. Select the variance range, then choose Home > Conditional Formatting > Highlight Cells Rules > Greater Than to highlight values above zero. Repeat with Less Than for values below zero, choosing clear, accessible formats. Apply the rules to the numeric variance column, not the text status column. Check the selected range and rule threshold before relying on colors in a shared report; color alone should not be the only indication of status.

Common errors and decisions

  • Reversed subtraction: If overspending appears negative, the formula may be budget minus actual. Decide which sign convention you need and reverse the order consistently if necessary.
  • Numbers stored as text: Values imported with symbols, spaces, or apostrophes may not behave like numbers. Check the cell alignment and whether Excel recognizes the values as numeric; clean or convert imported data before drawing conclusions.
  • Zero budget: A variance amount can still be calculated when the budget is zero, but a percentage relative to zero cannot. Report the amount and label the percentage as not applicable or leave it blank.
  • Wrong range in totals: Verify that total formulas include each category once and do not include a subtotal that repeats the same expenses.
  • Budget is not the same as cash available: A budget variance compares actuals with a plan. It does not, by itself, show cash flow, commitments not yet paid, or whether future spending will stay within the plan.

For a useful review, confirm that actuals and budgets cover the same period and use the same units. Comparing a monthly actual with an annual budget will produce a mathematically valid result but a misleading business comparison. Also decide how to treat refunds, credits, and transfers before summarizing the data, since different reporting rules can change the totals.

Frequently Asked Questions

What is the Excel formula for budget variance?
With actual spending in B2 and the budget in C2, use =B2-C2. A positive answer is over budget, a negative answer is under budget, and zero is on budget.

How do I show “over budget” or “under budget” in Excel?
With actual in B2 and budget in C2, use =IF(B2>C2,"Over budget",IF(B2<C2,"Under budget","On budget")). It returns a label based on the relationship between the two amounts.

How do I calculate the percentage over budget?
With actual in B2 and budget in C2, use =IF(C2=0,"",(B2-C2)/C2), then format the result as a percentage. For a $1,350 actual against a $1,200 budget, it returns 12.5% when displayed as a percentage.

Should an underspend be negative or positive?
It depends on the measure. Actual minus budget makes an underspend negative; budget minus actual makes remaining funds positive. Both are valid, so label the column and keep the convention consistent.

Why does my budget percentage show an error?
A percentage calculation divides by the budget, so a zero budget causes a division-by-zero error unless handled. Use a zero check such as the IF formula above, and decide whether a blank or a text note is clearer for your report.

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.