The STDEV.S and STDEV.P functions in Excel
STDEV.S is the one you usually want: it treats the range as a sample of a larger set (the scores you collected, not every score that exists). A larger result means the values sit further from the average.
STDEV.P treats the range as the whole population. Use it only when you truly have every value, for example every pupil in a class of eight, not a sample of eight from a school.
Syntax
=STDEV.S(number1, [number2], …)
| Argument | What it means |
|---|---|
| number1 | A number or a range of numbers. |
| number2, …optional | More numbers or ranges. Text and empty cells in a range are ignored. |
Examples
1. Spread of a sample
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Reading | Value | Sample spread | |
| 2 | 1 | 2 | 2.1381 | |
| 3 | 2 | 4 | ||
| 4 | 3 | 4 | ||
| 5 | 4 | 4 | ||
| 6 | 5 | 5 | ||
| 7 | 6 | 5 | ||
| 8 | 7 | 7 | ||
| 9 | 8 | 9 |
The values average 5. STDEV.S divides by n − 1 (seven), so the spread is about 2.14.
2. Spread of the whole set
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Reading | Value | Population | |
| 2 | 1 | 2 | 2 | |
| 3 | 2 | 4 | ||
| 4 | 3 | 4 | ||
| 5 | 4 | 4 | ||
| 6 | 5 | 5 | ||
| 7 | 6 | 5 | ||
| 8 | 7 | 7 | ||
| 9 | 8 | 9 |
The same numbers, treated as a complete set. STDEV.P divides by n (eight) and returns exactly 2.
3. A tighter list
| A | B | C | |
|---|---|---|---|
| 1 | Value | Spread | |
| 2 | 10 | 0.5774 | |
| 3 | 10 | ||
| 4 | 11 | ||
| 5 | 11 |
The four numbers sit close together, so the sample spread is well below 1.
Common mistakes
Using STDEV.P on a sample. That slightly understates the spread.
Fix: Default to STDEV.S unless you really have every member of the group.
The older STDEV and STDEVP names. They still work, but Microsoft documents STDEV.S and STDEV.P.
Fix: Prefer the dotted names in new workbooks so the sample vs population choice is obvious.
Frequently asked questions
What does a result of 0 mean?
Every number in the range is the same, so there is no spread.
How is this different from AVERAGE?
AVERAGE is the centre. STDEV.S is how far the values typically sit from that centre.
Related functions
- AVERAGEAVERAGE returns the arithmetic mean of numbers or a range, skipping text and empty cells.
- MEDIANMEDIAN returns the middle value of a list and MODE.SNGL the most common one.
- RANK.EQRANK.EQ returns the position of a number in a list, highest first by default, or lowest first with order 1.
More on standard deviation in Excel
Standard deviation tells you how spread out a set of numbers is around its average. Excel has two main versions, STDEV.S and STDEV.P, and picking the right one depends on whether your data is a sample or the whole population.
Sample or population
Use STDEV.P when your numbers are every member of the group you care about, for example the scores of all 8 players on a team. Use STDEV.S when they are a sample drawn from a bigger group, for example 8 customers surveyed out of thousands. The sample version divides by n minus 1 instead of n, which corrects for the fact that a sample tends to understate the true spread.
=STDEV.S(A2:A9)
=STDEV.P(A2:A9)The older STDEV function still works and behaves like STDEV.S. New workbooks should use the dotted names because they state the intent.
How to read the number
The result is in the same unit as the data. If daily sales average 200 with a standard deviation of 15, a typical day lands within roughly 15 of 200. For data that is roughly bell-shaped, about two thirds of values fall within one standard deviation of the average, and about 95% within two.
A bigger deviation means less consistency. Compare it with the AVERAGE to judge whether it is large: 15 on an average of 200 is small, 15 on an average of 20 is not.
What gets counted
- In a range, text, logical values and empty cells are skipped. A number stored as text is silently left out, so check the count with COUNT if the answer looks odd.
- A zero is a real value and is included, which pulls the deviation up.
- STDEV.S needs at least two numbers. With one value you get #DIV/0!.
Deviation for one group only
There is no STDEVIF, but you can use a filtered range. In Microsoft 365 this gives the deviation of the amounts in C for one region in B:
=STDEV.S(FILTER(C2:C100,B2:B100="North"))See [statistics basics](/learn/statistics-basics) for how average, median and spread fit together.