Home › Excel functions › STDEV
Standard Deviation in Excel: STDEV.S and STDEV.P
Standard deviation tells you how far your numbers typically sit from the average. A low standard deviation means the values are close together; a high one means they're spread out. In Excel you calculate it with STDEV.S (for a sample) or STDEV.P (for a whole population).
STDEV · In this guide
STDEV.S or STDEV.P?
| Function | Use it when | Example |
|---|---|---|
STDEV.S | Your data is a sample of a bigger group | A survey of 200 customers out of thousands |
STDEV.P | Your data is the whole group | The scores of every student in one class |
When in doubt, use STDEV.S: most data is a sample. The old STDEV function is the same as STDEV.S and still works, but Microsoft keeps it only for compatibility.
Worked example: test scores
| A | B | |
|---|---|---|
| 1 | Student | Score |
| 2 | Anna | 72 |
| 3 | Ben | 85 |
| 4 | Chris | 90 |
| 5 | Dana | 66 |
| 6 | Eli | 78 |
| 7 | Fay | 95 |
| 8 | Gus | 81 |
| 9 | Hana | 73 |
| Formula | Result |
|---|---|
=AVERAGE(B2:B9) | 80 |
=STDEV.S(B2:B9) | 9.74 |
=STDEV.P(B2:B9) | 9.11 |
The average score is 80 and the scores typically lie about 10 points above or below it. STDEV.P is a bit lower because it divides by the number of values (8), while STDEV.S divides by one less (7) to correct for working with a sample.
How Excel calculates it
- Take the average: 80.
- Subtract the average from each score and square the result: 64, 25, 100, 196, 4, 225, 1, 49.
- Add these up: 664.
- Divide by 7 for a sample (94.86) or by 8 for the population (83). This is the variance (VAR.S and VAR.P in Excel).
- Take the square root: 9.74 or 9.11.
Standard deviation with a condition
There's no STDEVIF. In Excel 365 you can combine STDEV.S with FILTER. With classes in column C:
=STDEV.S(FILTER(B2:B9, C2:C9="Class A"))In older versions, use =STDEV.S(IF(C2:C9="Class A", B2:B9)) and confirm with Ctrl+Shift+Enter.
Compare spread: coefficient of variation
A standard deviation of 10 is a lot for scores around 20 and little for scores around 1,000. To compare, divide by the average:
=STDEV.S(B2:B9) / AVERAGE(B2:B9)Format the cell as a percentage: 12%. The lower this number, the more consistent the data.
Related functions
- STDEVA / STDEVPA: also count text as 0 and TRUE as 1. Rarely what you want.
- VAR.S / VAR.P: the variance, the standard deviation squared.
- AVERAGE, MEDIAN: the centre of your data. See AVERAGE.
Frequently asked questions
Why do I get #DIV/0! with STDEV.S?
STDEV.S needs at least two numbers. Empty cells and text are ignored, so check whether your range really contains numbers.
Does STDEV ignore blank cells?
Yes. Blank cells and text in a range are skipped. A cell with 0 does count.
How do I show mean ± standard deviation?
Use =ROUND(AVERAGE(B2:B9),1) & " ± " & ROUND(STDEV.S(B2:B9),1), which shows 80 ± 9.7.
How do I add error bars with the standard deviation to a chart?
Select the chart, click the + button, tick Error Bars and choose Standard Deviation, or choose More Options › Custom to use your own calculated values.