How to calculate variance in Excel

VAR.S: variance of a sample

A snack maker checks 10 bags from each filling line against a 500 g target. Line A averages 500.1 g and Line B 500.04 g, so the averages say both lines are on target. Variance says how much the weights scatter around that average.

VAR.S takes each value's distance from the mean, squares it, adds the squares, and divides by n - 1. The formula below writes out exactly that, so you can see the parts; in Excel, =VAR.S(B2:B11) gives the same answer. Like AVERAGE, VAR.S ignores empty cells and text in the range, and it needs at least two numbers or it returns #DIV/0!.

VAR.S or VAR.P: which one to use

VAR.P divides by n instead of n - 1. Use it when the numbers are the whole population: every invoice from last year, when last year is all you want to describe. Use VAR.S when the numbers stand in for something larger. These 10 bags are a sample of everything the line produces, so VAR.S is right here.

Why n - 1? Values in a sample sit closer to their own average than to the true average of the whole population, so the plain average of squared distances comes out a little too small. Dividing by one less corrects that. The gap shrinks as n grows: with 10 values VAR.P is 10% smaller, with 1,000 values it is 0.1% smaller. If you are unsure, use VAR.S.

VAR.P with a single number returns 0. VAR.S with a single number returns #DIV/0!, which is honest: one bag tells you nothing about scatter.

Variance and standard deviation: square and square root

Standard deviation is the square root of variance, and variance is standard deviation squared. VAR.S pairs with STDEV.S and VAR.P with STDEV.P. Mixing them, such as SQRT(VAR.P(...)) compared against STDEV.S, gives two numbers that look like they should match but do not.

The practical difference is units. Line B's variance of about 10 is in grams squared, which nobody can picture. Its standard deviation is about 3.2 g, which you can: a typical bag is about 3 g off the average. Report standard deviation to people. Use variance when a method calls for it, such as adding up uncertainty: for independent sources of variation, variances add, and standard deviations do not.

Not the same as budget variance

In finance and accounting, "variance" usually means the difference between actual and budget, such as =C2-B2 or a percentage over or under plan. That has nothing to do with VAR.S. If that is what you need, see the budget vs actual variance guide linked below. VAR.S and VAR.P measure spread, the statistical meaning.

Variance with a condition, and for a whole table

Excel has no VARIF. In Excel 365 or 2021, filter first: =VAR.S(FILTER(C2:C50,B2:B50="Night")) gives the variance for night-shift bags only. In older versions use =VAR.S(IF(B2:B50="Night",C2:C50)) and confirm it with Ctrl+Shift+Enter.

For a full summary at once, turn on the Analysis ToolPak (File > Options > Add-ins > Manage Excel Add-ins > Go, tick Analysis ToolPak). Then Data > Data Analysis > Descriptive Statistics gives mean, standard deviation, sample variance, range and more for each column. Its Sample Variance row is the VAR.S version. The output is static numbers, so rerun it if the data changes.

1. Go to Data > Data Analysis and choose Descriptive Statistics.
2. Set Input Range to B1:C11, choose Grouped By Columns and tick Labels in first row.
3. Pick an output range, tick Summary statistics, and click OK.