Choose the Right Variance Formula for Your Spreadsheet

In Excel, “variance” can mean statistical dispersion or a business difference. Use VAR.S or VAR.P to measure how values vary around their mean. Subtract a baseline from an actual value to calculate an amount variance, and divide that difference by a nonzero baseline to calculate percentage variance.

Choose the right Excel variance calculation

Start by identifying the question your spreadsheet needs to answer:

Question	Excel formula	Use when
How dispersed is a sample?	=VAR.S(range)	The observations represent part of a larger population
How dispersed is a complete population?	=VAR.P(range)	The range contains every member of the defined population
How much did a value change?	=Actual-Budget	You need an absolute difference
What is the change relative to the baseline?	=(Actual-Budget)/Budget	You need a percentage difference

For example, use =VAR.S(A2:A21) when the values in A2:A21 are a sample. Use =VAR.P(A2:A41) when A2:A41 contains the entire population defined by your analysis. Microsoft documents the distinction in its VAR.S reference and VAR.P reference.

For a business comparison, subtract budget, forecast, target, or prior-period values from actual or current values. Divide that difference by the baseline when you need a relative change, then apply Excel’s percentage number format. This follows the standard actual-versus-baseline percentage variance calculation.

Statistical variance is expressed in squared units. If readers need a measure in the original unit—such as dollars, centimeters, or seconds—use STDEV.S for a sample or STDEV.P for a population. Standard deviation is the square root of variance.

Current workbooks should use VAR.S and VAR.P. The older VAR and VARP names are legacy counterparts rather than the clearest defaults for new workbooks.

A PivotTable calculation such as “difference from previous year” is another kind of business comparison. It subtracts one period’s value from another; it does not calculate statistical dispersion with VAR.S or VAR.P.

Calculate actual-versus-budget variance

For budget, forecast, target, prior-period, or benchmark reporting, calculate the absolute and percentage differences separately.

Suppose your worksheet contains this synthetic data:

Category	Budget	Actual	Variance
Revenue	100,000	112,000	12,000; 12%
Services	80,000	76,000	-4,000; -5%
New program	0	15,000	15,000; New

If the budget is in C5 and the actual value is in D5, enter this amount-variance formula:

=D5-C5

Calculate percentage variance with:

=(D5-C5)/C5

Excel returns a decimal. Apply percentage number formatting if you want the cell to display a percentage; do not also multiply the formula by 100 unless you specifically need a whole-number result rather than a percentage-formatted decimal.

With a baseline of 100,000 and an actual value of 112,000:

Amount variance = 112,000 - 100,000 = 12,000
Percentage variance = 12,000 / 100,000 = 0.12 = 12%

The sign does not determine whether a business variance is favorable. A positive revenue variance may be favorable, while a positive cost, defect, or delivery-time variance may be unfavorable. If interpretation matters, add a Status field containing labels such as Favorable, Unfavorable, or On target.

A zero baseline makes the ordinary percentage calculation undefined because the formula would divide by zero. Choose and document a reporting policy instead of silently replacing the result with 0. Depending on the context, the percentage cell might contain:

• a blank;
• #N/A;
• a label such as New; or
• no percentage, with only the amount variance reported.

=IF(C5=0,NA(),(D5-C5)/C5)

For negative baselines, dividing by the baseline’s absolute value is an optional reporting convention:

=(D5-C5)/ABS(C5)

This prevents a negative denominator from reversing the displayed direction. It does not eliminate the interpretation problem when values cross zero or move between losses and gains. Define the convention in the table notes so readers know how the percentage was calculated.

Use VAR.S for a sample and VAR.P for a population

Statistical variance measures how dispersed values are around their mean. Excel needs to know whether the observations are a sample or the complete population because the two calculations use different denominators.

Use:

=VAR.S(A2:A21)

when the observations are a sample intended to represent a larger population.

Use:

=VAR.P(A2:A41)

when those cells contain every member of the population defined by your analysis.

Make this decision from the dataset’s scope, not its row count. Ten records can be a complete population if they represent every relevant unit. Conversely, 100,000 records can still be a sample if they were drawn from a larger group.

For observations x_1 through x_n:

• Population variance divides the sum of squared deviations by n.
• Sample variance divides the sum by n-1.

The sample formula uses n-1 when estimating population variance from sampled observations. Before Excel 2010, the corresponding functions were VARP for population variance and VAR for sample variance, as shown in this reference to Excel variability functions.

VAR.S requires at least two valid numeric observations and may return #DIV/0! when fewer are available, according to this VAR.S function guide. Genuine recorded zeros are numeric observations, so do not remove them merely because they resemble missing-value placeholders.

Mixed ranges need closer inspection. Blanks, text, logical values, direct arguments, references, and error cells may not all behave identically. If a range contains anything other than the intended numeric observations, clean or validate the input rather than relying on assumptions about how every nonnumeric entry will be treated.

Verify Excel’s result with a worked variance example

Consider this eight-value dataset:

2, 5, -1, 3, 4, 5, 0, 2

Its sum is 20, so the mean is:

20 / 8 = 2.5

Subtract 2.5 from every value and square each difference:

Value	Difference from mean	Squared difference
2	-0.5	0.25
5	2.5	6.25
-1	-3.5	12.25
3	0.5	0.25
4	1.5	2.25
5	2.5	6.25
0	-2.5	6.25
2	-0.5	0.25

The squared differences total 34.

If the eight values are the complete population, divide by eight:

Population variance = 34 / 8 = 4.25

Excel should return the same result from:

=VAR.P(A2:A9)

If the values are a sample, divide by seven:

Sample variance = 34 / 7 ≈ 4.86

Excel should match that result with:

=VAR.S(A2:A9)

The documented worked example of population and sample variance reports the same mean, squared-deviation total, population variance, and sample variance.

Neither answer is inherently wrong. The population result is appropriate if the eight values constitute the complete population of interest. The sample result is appropriate if the observations are being used to represent a larger population.

Interpret variance without overstating it

A higher statistical variance means values are more dispersed around their mean. A lower variance means they are more concentrated near the mean.

Lower variance is not automatically better. Consistency may be desirable when measuring manufacturing tolerances, processing times, or forecast errors. In another analysis, variation may be expected, harmless, or central to the question being studied.

Because each deviation is squared, variance is reported in squared units. If the original values are dollars, the variance is in dollars squared. If they are centimeters, the variance is in square centimeters. That can make raw variance difficult to communicate to a general audience.

Standard deviation returns the result to the original unit because it is the square root of variance. Use:

=STDEV.S(A2:A9)

for a sample, or:

=STDEV.P(A2:A9)

for a complete population.

Variance also incorporates every value, unlike the range, which uses only the minimum and maximum. Two machines can therefore produce datasets with the same mean and range but different variances because their intermediate observations lie at different distances from the mean.

Variance alone does not establish profitability, product quality, investment risk, causation, or statistical significance. Interpret it alongside the metric’s meaning, the data-collection method, the distribution, relevant comparison groups, and the analytical objective.

Add a previous-period variance to a PivotTable

A previous-period PivotTable variance is an optional business-reporting calculation. It shows an arithmetic difference between periods; it is not a substitute for VAR.S or VAR.P.

To add a year-over-year revenue difference:

1. Place Years in the PivotTable’s column area.
2. Add Revenue to the Values area twice.
3. Open the value-field settings for the second Revenue field.
4. Choose Show Values As.
5. Select Difference From.
6. Set the base field to Years.
7. Set the base item to Previous.

The earliest year has no displayed difference because there is no preceding year. The comparison remains part of the PivotTable when you change row fields such as customer or sector. This workflow is demonstrated in the MrExcel previous-period PivotTable tutorial.

Do not assume that calculated items or DAX measures containing specific year names will remain current automatically. Formulas that explicitly refer to particular years require maintenance as new reporting periods are added.

Prepare a variance table for public sharing

A clear business-variance output should contain one row per category or period and explicit fields such as:

• Category or Period;
• Baseline;
• Actual;
• Amount Variance;
• Percentage Variance; and
• Status.

Add concise notes defining the baseline, units, sign convention, zero-baseline policy, and meaning of favorable or unfavorable.

For statistical results, publish enough information for readers to interpret the number:

Dataset	Observations	Method	Variance
Example measurements	8	VAR.P	4.25
Example measurements	8	VAR.S	4.86

State why the dataset was treated as a sample or population. You may also publish the corresponding STDEV.S or STDEV.P result when the original measurement unit will be easier to understand.

Use a small synthetic dataset for a demonstration or data that is already public and suitable for republication. Do not place sensitive spreadsheet data on a public data page.

Frequently asked questions

Does a larger variance prove that two groups are statistically different?

No. Separate variance calculations describe dispersion within each group, but a larger variance in one group does not by itself establish a statistically significant difference. Calculating grouped variances is descriptive analysis, not a significance test. Choosing an appropriate statistical test requires considering the research question, sampling design, distribution, and assumptions.

For the spreadsheet as a whole, follow a three-way rule: use VAR.S or VAR.P to measure dispersion, subtract actual and baseline values to report an amount difference, and divide that difference by a nonzero baseline to report a percentage. Before sharing the results, label the method, units, baseline, sign convention, and sample-or-population choice.