=STDEV.S(B2:B9) returns the standard deviation of the values in B2:B9 treated as a sample, and =STDEV.P(B2:B9) treats them as the whole population. The standard deviation says how far values typically sit from their average: a small one means the values are close together.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Student | Score | Measure | Result | |
| 2 | Ana | 72 | STDEV.S | 12.82853961 | |
| 3 | Ben | 84 | STDEV.P | 12 | |
| 4 | Cleo | 84 | Average | 90 | |
| 5 | Dan | 84 | |||
| 6 | Eve | 90 | |||
| 7 | Finn | 90 | |||
| 8 | Gia | 102 | |||
| 9 | Hal | 114 |
The scores average 90. STDEV.P gives exactly 12 and STDEV.S gives about 12.83. Change Hal's score to 90 and both drop sharply: one value far from the rest moves the standard deviation a lot.
STDEV.S vs STDEV.P: which one to use
The two differ in one step. STDEV.P divides the sum of squared differences by the number of values, n. STDEV.S divides by n minus 1, which makes the result slightly larger. The reason: a sample's spread is measured around the sample's own average, which sits closer to its values than the true average does, so dividing by n would understate the spread of the whole group.
- STDEV.P (population): the range holds every value you want to describe. The scores of all 8 students in this class, when the question is about this class.
- STDEV.S (sample): the range is a part of something bigger. 8 students picked from a school of 600, used to estimate the spread of the whole school.
When in doubt, use STDEV.S. Most data in a spreadsheet is a sample, and statistics tools (t tests, confidence intervals) expect the sample version. With hundreds of values the two results are almost the same; with 8 values the gap is about 7%.
The older functions STDEV and STDEVP give the same results as STDEV.S and STDEV.P and still work in every version of Excel. STDEVA and STDEVPA also count text as 0 and TRUE as 1, which is rarely what you want.
How Excel calculates it, step by step
This sheet does by hand what STDEV.S does in one call: subtract the average from each value, square the differences, add them up, divide by n minus 1, and take the square root.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Value | Difference | Squared | Result | ||
| 2 | 4 | -2 | 4 | Sum of squares | 34 | |
| 3 | 8 | 2 | 4 | n | 6 | |
| 4 | 6 | 0 | 0 | Variance (sample) | 6.8 | |
| 5 | 5 | -1 | 1 | Std dev (sample) | 2.607680962 | |
| 6 | 3 | -3 | 9 | STDEV.S | 2.607680962 | |
| 7 | 10 | 4 | 16 |
The sum of squares is 34, the sample variance is 6.8, and its square root (about 2.61) matches STDEV.S in F6. Change F4 to =F2/F3 and you get the population variance; its square root is what STDEV.P returns.
Variance: VAR.S and VAR.P
The variance is the standard deviation before the square root: =VAR.S(A2:A7) gives 6.8 for the data above, and =VAR.P(A2:A7) divides by n instead of n minus 1. Variance is in squared units (points squared, dollars squared), so for reporting, the standard deviation is easier to read. VAR and VARP are the old names.
Mean plus or minus one standard deviation
A common way to report spread is "mean ± SD", for example 90 ± 12.8. The two ends of that range are simple formulas, and a conditional formatting rule can mark the values outside it.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Student | Score | Measure | Value | |
| 2 | Ana | 72 | Mean | 90.0 | |
| 3 | Ben | 84 | SD | 12.8 | |
| 4 | Cleo | 84 | Low | 77.2 | |
| 5 | Dan | 84 | High | 102.8 | |
| 6 | Eve | 90 | |||
| 7 | Finn | 90 | |||
| 8 | Gia | 102 | |||
| 9 | Hal | 114 |
The rule highlights Ana and Hal, the two scores outside about 77.2 to 102.8. In normally distributed data about two thirds of the values fall within one standard deviation of the mean, and about 95% within two. To format the text in a cell as "90.0 ± 12.8", use =TEXT(E2,"0.0")&" ± "&TEXT(E3,"0.0").
Standard deviation with a condition
There is no STDEVIF function. Put an IF inside STDEV.S: IF returns the score where the region matches and FALSE elsewhere, and STDEV.S skips the FALSE values.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Sales | Region | STDEV.S | |
| 2 | North | 120 | North | 25.61737691 | |
| 3 | South | 95 | South | 3.872983346 | |
| 4 | North | 150 | |||
| 5 | South | 101 | |||
| 6 | North | 90 | |||
| 7 | South | 98 | |||
| 8 | North | 135 | |||
| 9 | South | 104 |
North's sales vary much more than South's. In Excel 365 and 2021 this formula works as typed. In Excel 2019 and earlier, finish it with Ctrl+Shift+Enter (Cmd+Shift+Enter on a Mac) or it returns a wrong result or #VALUE!. With Excel 365 you can also write =STDEV.S(FILTER(B2:B9,A2:A9=D2)).
Try it: spread of delivery times
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order | Days | Std dev | ||
| 2 | A1 | 3 | |||
| 3 | A2 | 5 | |||
| 4 | A3 | 4 | |||
| 5 | A4 | 9 | |||
| 6 | A5 | 3 | |||
| 7 | A6 | 4 | |||
| 8 | A7 | 6 |
Your turn: The orders in B2:B8 are a sample of all orders. In E2, calculate their standard deviation.
Hint: a sample means the function ending in .S.
Common mistake: the total row in the range
A range like B2:B10 that also takes in a total or an average at the bottom of the column treats that summary as one more data point, and the standard deviation comes out far too large. Select only the data rows, or keep summaries in a different column, as the sheets on this page do. Empty cells and text in the range are ignored, but a 0 is a value and counts: a missing score typed as 0 widens the spread just as a real 0 would. To check how many values were used, put =COUNT(B2:B9) next to the result.
Frequently Asked Questions
What is the formula for standard deviation in Excel?
=STDEV.S(B2:B9) for a sample and =STDEV.P(B2:B9) for a full population. Both ignore text and empty cells in the range.
Should I use STDEV.S or STDEV.P?
Use STDEV.P only when the range holds every member of the group you describe, such as all 8 people on a team. When the data is a sample used to describe something larger (some customers, some test runs), use STDEV.S. With many values the two are close; with few values STDEV.S is noticeably larger.
What is the difference between STDEV and STDEV.S?
None in the result. STDEV and STDEVP are the pre-2010 names, kept for compatibility; STDEV.S and STDEV.P are the current ones. Google Sheets accepts both sets of names.
How do I calculate variance in Excel?
Use =VAR.S(B2:B9) for a sample and =VAR.P(B2:B9) for a population. The variance is the standard deviation squared, so =STDEV.S(B2:B9)^2 gives the same number as VAR.S.
How do I calculate the standard error in Excel?
Excel has no function for the standard error of the mean. Divide the sample standard deviation by the square root of the count: =STDEV.S(B2:B9)/SQRT(COUNT(B2:B9)).
