Excel has two variance formulas that work differently depending on your data
Variance measures how spread out your numbers are from the average. Excel gives you two formulas: VAR.S for a sample of data, and VAR.P for an entire population. Most of the time you'll use VAR.S. The difference matters because sample variance uses a slightly different calculation to account for the fact that you're working with a subset of all possible data.
Both formulas do the same thing: they find the average of your numbers, then measure how far each number sits from that average, then average those distances. Excel just handles the math automatically so you don't have to do it by hand.
Key Takeaways
- Use VAR.S when your numbers represent a sample from a larger group, which is the most common situation in business and research.
- Use VAR.P only when your numbers represent the entire population you're measuring, not a subset of it.
- Both formulas work the same way: type =VAR.S(A1:A10) or =VAR.P(A1:A10) and Excel calculates the result instantly.
- Variance is always a positive number, and larger numbers mean your data is more spread out.
- If you need the square root of variance (called standard deviation), use STDEV.S or STDEV.P instead.
Calculate sample variance with VAR.S
VAR.S is the formula you'll use in almost every real situation. Use it when your data is a sample — meaning it's a subset of a larger group you're trying to understand. Sales figures from three months, test scores from one class, or customer satisfaction ratings from a survey are all samples.
To calculate sample variance, click on an empty cell where you want the result to appear. Type =VAR.S( then select the range of cells containing your numbers. For example, if your numbers are in cells A1 through A10, type =VAR.S(A1:A10) and press Enter. Excel returns a single number — that's your variance.
The number Excel gives you is always positive. A variance of 5 means your data is less spread out than a variance of 50. If all your numbers are identical, variance equals zero.
Calculate population variance with VAR.P
VAR.P is for when your numbers represent the entire population you care about, not a sample of it. This is rare in practice. You'd use VAR.P if you're measuring every single employee's salary at a company, or every student's grade in a specific class section, or every transaction in a specific month — the complete set, not a portion of it.
The steps are identical to VAR.S: click an empty cell, type =VAR.P(A1:A10), and press Enter. The only difference is the calculation inside — VAR.P divides by the total count of numbers, while VAR.S divides by the count minus one. This makes VAR.S slightly larger, which is intentional: it accounts for the fact that a sample tends to be less spread out than the full population.
In most business situations, you won't know whether you have the complete population or just a sample. When in doubt, use VAR.S.
Understanding what the variance number actually means
Variance is measured in the square of your original units. If you're measuring height in inches, variance is in square inches. If you're measuring dollars, variance is in square dollars. This makes variance hard to interpret directly, which is why many people use standard deviation instead — it's just the square root of variance and uses the same units as your original data.
To get standard deviation in Excel, use STDEV.S (for a sample) or STDEV.P (for a population) instead of VAR.S or VAR.P. The steps are identical: type =STDEV.S(A1:A10) and press Enter. Standard deviation is easier to understand because it's in the same units as your numbers.
Variance itself is most useful when you're comparing two groups. If Group A has a variance of 12 and Group B has a variance of 45, Group B's numbers are more spread out. The actual number 12 or 45 is less important than knowing which one is larger.
Common mistakes when using variance formulas
The most common error is mixing up VAR.S and VAR.P. Remember: S is for sample (the most common case), P is for population (the complete set). If you're not sure, use VAR.S.
Another mistake is including text or blank cells in your range. If you type =VAR.S(A1:A10) but cell A5 contains the word "error" or is empty, Excel either ignores that cell or returns an error message depending on what's in it. Clean your data first: make sure every cell in your range contains a number.
A third mistake is forgetting that variance measures spread, not accuracy or quality. High variance just means your numbers are far apart. Low variance means they're close together. Neither is good or bad on its own — it depends on what you're measuring.
Calculating variance step-by-step with an example
Say you have five test scores: 78, 82, 85, 88, and 92. You want to know how spread out these scores are.
- Type the numbers in cells A1 through A5.
- Click on an empty cell, such as B1.
- Type =VAR.S(A1:A5) and press Enter.
- Excel returns 41.7 — that's your variance.
If you want standard deviation instead (which is easier to understand), click cell B2 and type =STDEV.S(A1:A5). Excel returns 6.46, meaning the scores typically vary by about 6.5 points from the average of 85.
You can also calculate variance for non-consecutive cells. If your numbers are in A1, A3, A5, and A7, type =VAR.S(A1,A3,A5,A7) using commas instead of a colon. Excel treats this the same way.
When to use variance instead of other spread measurements
Variance, standard deviation, and range all measure how spread out your data is, but they work differently. Range is the simplest: it's just the highest number minus the lowest number. Standard deviation is variance's square root and uses the same units as your original data. Variance itself is useful in statistics and when comparing multiple groups mathematically.
For most everyday business questions — "Are these sales numbers consistent?" or "How much do these measurements vary?" — standard deviation is easier to understand and explain. But variance is what statisticians use behind the scenes, and some advanced Excel functions require it as input.
Frequently Asked Questions
What's the difference between VAR.S and VAR.P?
VAR.S is for a sample (a subset of data), and VAR.P is for a population (the complete set). VAR.S divides by one less than the count of numbers, making it slightly larger. Use VAR.S unless you're certain you have every single data point in the group you're measuring.
Why is my variance number so large?
Variance is measured in the square of your original units, so it's often a larger number than you'd expect. If you're measuring in dollars, variance is in square dollars. Switch to standard deviation (STDEV.S or STDEV.P) if you want a number in the same units as your original data.
Can I calculate variance for data in different sheets?
Yes. Type =VAR.S(Sheet1.A1:A10,Sheet2.B1:B5) to include ranges from multiple sheets. Use the sheet name followed by a period, then the cell range. Excel combines all the numbers and calculates variance across them.
What does it mean if variance is zero?
Zero variance means all your numbers are identical. There's no spread at all. In real data this is rare, but it can happen if you're measuring something that doesn't change, like a fixed price or a constant temperature in a controlled environment.
Should I use variance or standard deviation?
For understanding and explaining your data, standard deviation is usually better because it's in the same units as your original numbers. Use variance when you're doing statistical calculations or comparing multiple groups mathematically. Both measure the same thing — spread — just in different ways.