Excel has several variance functions, and choosing the wrong one changes your result. Start with VAR.S for a sample or VAR.P for the entire population. Then adjust the formula if you need to exclude hidden rows or calculate variance for a specific group.
Calculate sample variance with VAR.S
Enter your numbers in A2:A11. Use VAR.S when those observations are a sample of a larger population. You need at least two numeric observations.
Select an empty cell for the result.
Type =VAR.S(A2:A11), using your actual data range in place of A2:A11.
Press Enter. Excel calculates sample variance using n−1 as the denominator, where n is the number of numeric observations.
Choose VAR.P for the whole population
Use VAR.P when your range contains every observation in the population you want to measure. Population variance divides by n and needs at least one numeric observation.
Select an empty cell for the result.
Type =VAR.P(A2:A11), using the range containing your numbers.
Press Enter to calculate the population variance.
Both VAR.S and VAR.P work in Excel for Microsoft 365, Excel 2024, and Excel 2021 on Windows and Mac, plus Excel for the web. Neither needs a separate download or add-in.
Open the function dialog on Windows
Use the function dialog in Windows desktop Excel for help entering either formula.
- 1.Select the destination cell, then select Insert Function (fx).
- 2.In Search for a function, type variance and select Go. To browse instead, select Statistical under Or select a category.
- 3.Under Select a function, double-click VAR.S for a sample or VAR.P for the whole population.
- 4.Enter A2:A11, or your own data range, in Number1.
- 5.Select OK to calculate the result.
Limit variance to selected rows
For a vertical range, enter =SUBTOTAL(110,A2:A11) in an empty cell for sample variance, or =SUBTOTAL(111,A2:A11) for population variance. These formulas exclude filtered-out and manually hidden rows.
To include manually hidden rows while still excluding filtered-out rows, replace 110 with 10 for a sample, or 111 with 11 for a population.
For a condition instead, enter =VAR.S(FILTER(B2:B11,A2:A11="East")) in an empty cell. This example uses categories in A2:A11 and numeric observations in B2:B11 to calculate sample variance for rows labeled East.
Substitute VAR.P for VAR.S when the matching rows form the entire population. The FILTER formula works in Microsoft 365, Excel 2024, Excel 2021 with dynamic arrays, and Excel for the web. If no rows match, it returns an empty-array error unless handled.
Check an error or an unexpected result
Check the range inside your formula. Sample variance requires at least two numeric observations; population variance requires at least one. A conditional sample calculation needs at least two numeric observations after the condition is applied.
Confirm that the function matches your data: VAR.S for a sample, or VAR.P for the whole population. Their different denominators affect the result.
A result on a different scale from your measurements isn't an error by itself. Variance is expressed in squared units.
If the calculation still fails because the range contains errors, and you want to exclude them, enter =AGGREGATE(10,3,A2:A11) in an empty cell for sample variance or =AGGREGATE(11,3,A2:A11) for population variance. Option 3 excludes hidden rows, errors, and nested SUBTOTAL or AGGREGATE results.
Frequently Asked Questions
How do I calculate budget variance as a percentage in Excel?
With the budget or baseline in A2 and the actual or new value in B2, enter =(B2-A2)/A2. Then select Home > Percent Style. For the amount difference, use =B2-A2. A zero baseline requires separate handling. These formulas calculate budget or period differences, not statistical variance.
Can I calculate variance from standard deviation?
Yes. Variance equals standard deviation squared. Enter =STDEV.S(A2:A11)^2 for sample variance or =STDEV.P(A2:A11)^2 for population variance.
Which variance formula should I use if TRUE and FALSE should count?
Use =VARA(A2:A11) for sample variance or =VARPA(A2:A11) for population variance when logical values should participate. TRUE evaluates as 1 and FALSE as 0.