The short answer
Put your numbers in a column, click an empty cell and type:
- =STDEV.S(A2:A11) for a sample (the usual choice)
- =STDEV.P(A2:A11) for a whole population
Replace A2:A11 with the cells that hold your data, then press Enter.
Step by step in Excel
- Enter your data in one column, one value per cell, for example A2 to A11 with a heading in A1.
- Click the cell where you want the result, such as B2.
- Type =STDEV.S(, then drag over your data (Excel fills in A2:A11), type ) and press Enter.
- Format the result to a sensible number of decimals with the Decrease Decimal button on the Home tab.
| A | B | |
|---|---|---|
| 1 | Score | Std dev |
| 2 | 22 | 5.8878 |
| 3 | 35 | |
| 4 | 28 | |
| 5 | 41 | |
| 6 | 30 | |
| 7 | 25 | |
| 8 | 33 | |
| 9 | 38 | |
| 10 | 27 | |
| 11 | 31 |
STDEV.S or STDEV.P: which one?
The two functions differ only in the last division. STDEV.S divides the sum of squared deviations by n − 1, and STDEV.P divides by n:
STDEV.S: s = Σ(x − x)2n − 1
STDEV.P: σ = Σ(x − μ)2N
Dividing by n − 1 (Bessel's correction) makes up for the fact that a sample's values sit a little closer to their own mean than to the true population mean. Without it, a sample standard deviation would come out too small on average.
- Use STDEV.S for survey responses, experiment results, a sample of products, or any data meant to tell you about a larger group.
- Use STDEV.P only when the data are the whole group, and you don't want to generalize beyond it.
With large samples the two give almost the same answer. With small ones, like the 10 scores above, the difference is noticeable: 5.89 versus 5.59.
Check the answer by hand
It's worth doing once to see where Excel's number comes from. For the ten scores above:
-
Find the mean
x = 31010 = 31
-
Square each deviation from the mean and add them up
Σ(x − 31)2 = 81 + 16 + 9 + 100 + 1 + 36 + 4 + 49 + 16 + 0 = 312
-
Divide by n − 1 = 9 (sample) and take the square root
s = 3129 = 34.667 = 5.8878
That matches STDEV.S. Dividing by 10 instead gives √31.2 = 5.5857, the STDEV.P result. The standard deviation calculator writes out every one of these steps for your own data.
Related Excel functions
- =VAR.S(A2:A11) and =VAR.P(A2:A11): the variance (the standard deviation squared), 34.667 and 31.2 here.
- =AVERAGE(A2:A11): the mean, 31.
- =STDEV.S(A2:A11)/AVERAGE(A2:A11): the coefficient of variation, 0.19 or 19%, which compares spread across data sets with different scales.
- =STDEV.S(A2:A11)/SQRT(COUNT(A2:A11)): the standard error of the mean, used in confidence intervals.
Common mistakes
- Numbers stored as text are silently skipped. If the result looks wrong, compare =COUNT(A2:A11) with the number of values you expect.
- Including the heading or a total row in the range. Text headings are ignored, but a total row is a number and will distort the result.
- Using STDEV.P for a sample, which understates the spread, especially with fewer than 30 values.
- Reading too much into one number. Standard deviation describes spread around the mean; a histogram shows whether the data are skewed or have outliers that inflate it.