Excel STDEV and VAR: Measure How Spread Out Your Data Is
Measure how far the values in a set typically sit from their average.
An average on its own hides as much as it reveals. Two teams can both average 40,000 with one clustered between 38,000 and 42,000 and the other split between 28,000 and 154,000. Standard deviation is the number that tells them apart: it is the typical distance between a value and the mean.
Variance is the same measurement before the final square root, which leaves it in squared units and therefore hard to interpret. It matters mathematically and rarely gets published. Standard deviation is in the same units as your data, so a result of 45,000 on a salary column reads directly as "salaries typically sit about 45,000 away from the mean".
The naming is where everyone stumbles. STDEV.S treats your numbers as a sample drawn from a larger group; STDEV.P treats them as the entire population. Sample is the right default for almost every business question, because you almost always hold a subset. Use .P only when the range genuinely contains every case that exists.
Syntax
=STDEV.S(number1, …) =STDEV.P(number1, …) =VAR.S(number1, …)Arguments
number1Required- The first range or value. Text and blanks are ignored; STDEV.S needs at least two numbers.
number2, …Optional- Further ranges or values, up to 255.
The example data
Headers in row 1, data in A2:C8. Note how far F8's salary sits above the rest.
| A | B | C | |
|---|---|---|---|
| 1 | Employee | Salary | Team |
| 2 | Alice Moreau | 32000 | Support |
| 3 | Bruno Santos | 28000 | Support |
| 4 | Chen Wei | 45000 | Engineering |
| 5 | Dana Okafor | 28000 | Support |
| 6 | Erik Halls | 38000 | Engineering |
| 7 | Farah Idris | 41000 | Engineering |
| 8 | Greg Nolan | 154000 | Executive |
Worked examples
=STDEV.S(B2:B8)Result: 45569.32
A huge spread relative to a mean of 52,286 — the signature of a distribution with one extreme value in it.
=STDEV.S(B2:B7)Result: 6979.97
The same data without the executive salary. Removing one row cuts the spread by a factor of six.
=STDEV.P(B2:B8)Result: 42191.31
The population version, dividing by n rather than n-1. Always slightly smaller, and only correct if these seven are everyone.
=VAR.S(B2:B8)Result: 2076562857
Variance is standard deviation squared, which is why the number is unreadable and STDEV is what gets reported.
Now practise it
Reading about a formula is not the same as writing one. Open this function's exercise and type it into a real grid — you get instant feedback on exactly which cell is wrong and why.
Common errors and how to fix them
#DIV/0!
Why it happens: STDEV.S was given fewer than two numbers. It divides by n-1, which is zero when n is one.
How to fix it: Use STDEV.P if a single value genuinely is the whole population, or guard with IFERROR.
The result seems impossibly large
Why it happens: One outlier dominates. Standard deviation squares each distance from the mean, so extreme values count far more than moderate ones.
How to fix it: That is the measurement working correctly. Report the median and an interquartile range alongside it if the outlier is not the point.
STDEV and STDEVA disagree
Why it happens: STDEVA counts text as 0 and includes logicals; STDEV ignores both.
How to fix it: Use STDEV.S unless you have a specific reason to treat text as zero.
Tips worth knowing
- Use .S by default. You are almost always looking at a sample, even when it feels like all the data you have.
- Standard deviation is in the same units as the data; variance is not. Report the first.
- The coefficient of variation — STDEV.S/AVERAGE — makes spread comparable between datasets with different scales.
- STDEV and VAR without a suffix are the legacy names and behave as the .S versions.
Frequently asked questions
Should I use STDEV.S or STDEV.P?
STDEV.S in nearly every case. Population (.P) is only correct when your range contains every member of the group you are describing — every employee in the company, every transaction ever. If the data is a subset, a period, or a sample, .S is the right choice and gives a slightly larger, more honest figure.
What is the difference between variance and standard deviation?
Standard deviation is the square root of variance. Variance is in squared units, so a salary variance is in squared pounds and means nothing intuitively. Standard deviation is back in the original units and can be compared directly with the mean.
What counts as a high standard deviation?
It depends entirely on the scale of the data, which is why it should always be read next to the mean. Divide one by the other to get the coefficient of variation — a value near 0.1 is tight, near 1.0 is very spread out, and above that usually means outliers are dominating.
Related functions
Guides that use it
Longer reads where this function does real work in a real sheet.