Variance Formula in Excel: Calculate Percentage Variance

Formulas & functions · Updated November 18, 2026

To calculate percentage variance in Excel, subtract the reference value from the actual value and divide by the reference value. With budget in A2 and actual in B2, enter =(B2-A2)/A2, then format the result as a percentage; this is different from statistical variance, which measures spread in a data set.

What percentage variance means

Percentage variance expresses a difference relative to a chosen baseline. The baseline matters: a change of $100 means something different against $500 than against $10,000. Dividing by the reference value makes the difference comparable as a proportion, and percentage formatting displays that proportion on a scale of 100.

For a budget comparison, the reference is usually the budget and the actual is the observed amount. The calculation is (actual − budget) ÷ budget. A positive result means actual is above budget; a negative result means actual is below budget. That sign describes direction, not automatically whether the result is good or bad. Spending above budget may be unfavorable, while revenue above budget may be favorable.

People also use “variance” to mean the statistical measure of dispersion. Excel’s statistical variance functions return squared units, not a percentage difference. If you mean a comparison between two figures, use percentage variance; if you mean how spread out a group of observations is, use a variance function instead.

Set up the worksheet

Put comparable values in separate columns and label them so the reference is unmistakable. For example, enter Budget in A1, Actual in B1, and the corresponding values in A2 and B2. In C1, use a label such as Percentage variance. Put the formula in C2. If you have many rows, each row should compare values for the same item and time period.

For example, a January budget of 1,000 and actual spending of 1,150 produce a difference of 150. Dividing 150 by the budget of 1,000 gives 0.15, or 15%. The actual amount is 15% above budget. If actual spending were 900 instead, the result would be −10%, meaning spending was 10% below budget.

Keep the units consistent. Do not compare a monthly actual with an annual budget, or a value in dollars with one in thousands of dollars, unless you first convert them to the same basis. A mathematically correct formula can still give a misleading result if the inputs do not represent equivalent measures.

Enter the formula and fill it down

With budgets in A2:A20 and actual values in B2:B20, enter this formula in C2: =(B2-A2)/A2. Press Enter. To apply it to the remaining rows, select C2 and drag the fill handle at the lower-right corner of the cell down through C20, or copy C2 and paste it into C3:C20. Excel adjusts the row references automatically.

The formula has three parts: B2 is the actual value, A2 is the reference, and B2-A2 calculates the absolute difference. Dividing by A2 scales the difference against the reference. Do not divide by the actual value unless that is deliberately the baseline you want; changing the denominator changes the answer.

If your worksheet puts actual in A2 and budget in B2, reverse the cell references to match that layout. A formula should follow the meaning of the cells, not simply be copied from an example. You can check a result manually by calculating the difference, dividing by the baseline, and multiplying by 100.

Format the result as a percentage

Excel stores percentages as decimal values: 0.15 is displayed as 15% when the cell uses percentage formatting. Select the result cells, then choose Home > Number > Percent Style. The value changes in appearance, not in the underlying calculation. Do not multiply the formula by 100 and also apply percentage formatting, because that would display a result 100 times too large.

By default, the displayed number of decimal places may not suit your report. With the percentage cells selected, use Home > Number > Increase Decimal or Decrease Decimal to show the precision you need. For instance, 0.1536 can display as 15%, 15.4%, or 15.36%, depending on the chosen number of decimal places. Rounding the display does not change the stored value used in later calculations.

Microsoft 365 for Windows and Excel for Mac both provide percentage formatting on the Home tab. The ribbon layout can vary slightly by platform or window size, so look in the Number group if the command is not immediately visible. Excel for the web also supports percentage formatting from its Home ribbon.

Handle zero and missing reference values

If the reference value is zero, percentage variance is mathematically undefined because the calculation would divide by zero. A change from zero to 50 cannot be described as a finite percentage increase relative to zero. Decide how your report should show such cases rather than treating the result as 0% or an ordinary percentage.

With reference values in A2:A20 and actual values in B2:B20, use this guarded formula in C2 if you want Excel to display #N/A when the baseline is zero: =IF(A2=0,NA(),(B2-A2)/A2). Fill it down and format the numeric results as percentages. The #N/A marker makes the undefined case visible; it can also be excluded from many chart series. If blank inputs are possible, decide whether they should remain blank or be flagged, rather than allowing a missing value to look like a genuine zero.

For a user-facing report, you could choose a text label such as “N/A” instead, but text results do not behave like numbers in averages or charts. Whatever convention you choose, document it and apply it consistently. In particular, do not silently replace a zero baseline with 1 merely to avoid an error: that changes the meaning of the percentage.

Percentage variance versus percentage difference

Percentage variance normally compares a value with a designated reference, such as actual versus budget or this year versus last year. If neither value is the natural baseline, some people instead use percentage difference, dividing the difference by the average of the two values. That convention treats both values symmetrically and is not interchangeable with percentage variance.

With the two values in A2 and B2 and neither designated as the baseline, this formula calculates the signed percentage difference using their average: =(B2-A2)/AVERAGE(A2,B2). If A2 is 100 and B2 is 120, the difference is 20 and the average is 110, so the result is about 18.18%. By comparison, percentage variance relative to A2 is 20%. State which definition you used when sharing results, especially when comparing reports created by different teams.

Statistical variance is a different calculation

For a data set, statistical variance describes how far observations tend to be from their mean. It is measured in squared units: if the data are in dollars, variance is in squared dollars. That is why statistical variance is not itself a percentage. Excel provides VAR.S for a sample and VAR.P for an entire population.

With the five observations 8, 10, 12, 14, and 16 in A2:A6, enter =VAR.S(A2:A6) to calculate sample variance. The mean is 12; the squared deviations total 40, and VAR.S divides by 5 − 1, giving 10. For those same five values in A2:A6, =VAR.P(A2:A6) treats them as the complete population and divides 40 by 5, giving 8.

Choose VAR.S when the observations are a sample used to estimate a larger group, and VAR.P when they are the whole group you want to describe. If you need a relative measure of spread, a coefficient of variation is often used: the standard deviation divided by the mean, commonly expressed as a percentage. That is a separate statistic and should not be confused with actual-versus-budget percentage variance.

Common mistakes and useful checks

  • Using the wrong denominator: For actual versus budget, divide by budget. Dividing by actual answers a different question.
  • Multiplying by 100 twice: Use the decimal result and percentage formatting, or multiply by 100 for a plain numeric display—not both.
  • Misreading the sign: Positive means above the reference and negative means below it. Favorable or unfavorable depends on what the measure represents.
  • Comparing mismatched periods or units: Confirm that both figures cover the same time span and use the same scale.
  • Ignoring a zero baseline: A relative percentage cannot be calculated from a zero reference. Flag that case explicitly.

For a quick audit, test a familiar row: if actual equals budget, the percentage should be 0%; if actual is twice the budget, it should be 100%; if actual is half the budget, it should be −50%. These checks can reveal a reversed subtraction order or an incorrect reference cell before you copy the formula through a large table.

Frequently Asked Questions

What is the Excel formula for percentage variance?
For actual in B2 and reference or budget in A2, use =(B2-A2)/A2 and format the result as a percentage. The reference belongs in the denominator.

How do I calculate percentage variance from budget?
Subtract budget from actual, divide by budget, and apply percentage formatting. A positive result means actual exceeded budget; whether that is favorable depends on whether the figure is revenue, cost, or another measure.

Why does my percentage variance show as a decimal?
The calculation returns a decimal by design. Select the cell and choose Home > Number > Percent Style to display, for example, 0.15 as 15%.

Can percentage variance be calculated when the budget is zero?
Not as a finite percentage relative to budget, because division by zero is undefined. Report the case as not applicable or use a clearly explained alternative comparison.

Is variance the same as standard deviation in Excel?
No. Variance measures squared deviations from the mean; standard deviation is the square root of variance and is expressed in the original units. Neither is the same as percentage variance between two figures.

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.