The syntax and what each function assumes
Both functions take the same arguments: =STDEV.S(number1, [number2], …) and =STDEV.P(number1, [number2], …). In practice you pass one range, such as B2:B9. Text, logical values and empty cells inside a range are ignored, so a stray header or a blank row does not break the result. Both return the standard deviation in the same unit as the data: minutes in, minutes out.
The names tell you the assumption. .P stands for population: the range contains every member of the group, for example the final grades of all 24 students in one class, or the monthly sales of all 12 months of a finished year that you only want to describe. .S stands for sample: the range is a subset you use to estimate the spread of a larger group, for example 8 commute times you happened to record, 50 customers out of 10,000, or last quarter's delivery times used to predict next quarter's. The older functions STDEV and STDEVP still work and give the same results as STDEV.S and STDEV.P; the dotted names were added to make the choice explicit.
Step by step: the standard deviation by hand
Building the calculation once makes the difference obvious. With the minutes in B2:B9:
1. Mean. =AVERAGE(B2:B9) returns 33. 2. Deviations. In C2, =B2-$D$12 subtracts the mean from each value; the deviations always add up to zero. 3. Squares. =C2^2 removes the signs and gives large deviations more weight: the 41-minute trip contributes 64, the 34-minute trip only 1. 4. Sum of squares. =SUM(D2:D9) gives 178. The shortcut =DEVSQ(B2:B9) does steps 1–4 in one go.
5. Variance. Divide by n for the population variance, 178 ÷ 8 = 22.25 (this is VAR.P), or by n − 1 for the sample variance, 178 ÷ 7 = 25.43 (this is VAR.S). 6. Square root. The square root brings the unit back from minutes² to minutes: 4.72 and 5.04. Both match the built-in functions to the last decimal.
Why the sample version divides by n − 1
A sample's own mean sits, by construction, in the middle of that sample. Measuring the deviations from the sample mean instead of from the unknown true mean therefore makes them a little too small on average, and the variance comes out too low. Dividing by n − 1 instead of n corrects that bias. This is called Bessel's correction, and n − 1 is the number of degrees of freedom: once the mean is fixed, only n − 1 of the deviations can vary freely.
The size of the correction depends only on n. STDEV.S is always STDEV.P × √(n ÷ (n − 1)). With 8 values that factor is 1.069, so the sample standard deviation is about 7% larger. With 30 values it is 1.017, with 100 values 1.005. For large data sets the choice barely matters; for small ones, such as five test runs or a dozen survey answers, it changes the result noticeably and feeds directly into confidence intervals and tests.
Worked example: commute times, sample vs population
Eight recorded commute times in minutes (yellow). Columns C and D build the sum of squares, the lower block divides it by n and by n − 1 and checks both against STDEV.P and STDEV.S.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Trip | Minutes | Deviation from mean | Squared deviation |
| 2 | Mon (week 1) | 32 | -1.00 | 1.00 |
| 3 | Tue (week 1) | 28 | -5.00 | 25.00 |
| 4 | Wed (week 1) | 35 | 2.00 | 4.00 |
| 5 | Thu (week 1) | 41 | 8.00 | 64.00 |
| 6 | Fri (week 1) | 30 | -3.00 | 9.00 |
| 7 | Mon (week 2) | 26 | -7.00 | 49.00 |
| 8 | Tue (week 2) | 38 | 5.00 | 25.00 |
| 9 | Wed (week 2) | 34 | 1.00 | 1.00 |
| 10 | ||||
| 11 | Step | Value | ||
| 12 | Mean (AVERAGE) | 33.00 | ||
| 13 | Count n (COUNT) | 8 | ||
| 14 | Sum of squared deviations (SUM or DEVSQ) | 178.00 | ||
| 15 | Population variance = sum ÷ n (VAR.P) | 22.25 | ||
| 16 | Sample variance = sum ÷ (n − 1) (VAR.S) | 25.43 | ||
| 17 | Population SD by hand = √(sum ÷ n) | 4.72 | ||
| 18 | Sample SD by hand = √(sum ÷ (n − 1)) | 5.04 | ||
| 19 | STDEV.P(B2:B9) | 4.72 | ||
| 20 | STDEV.S(B2:B9) | 5.04 | ||
| 21 | Ratio STDEV.S ÷ STDEV.P | 1.069 | ||
| 22 | Check: √(n ÷ (n − 1)) | 1.069 | ||
| 23 | Coefficient of variation (STDEV.S ÷ mean) | 15.3% | ||
- C2
=B2-$D$12Deviation from the mean - D2
=C2^2Squared deviation - D14
=SUM(D2:D9)Sum of squares - D15
=D14/D13Divide by n (population) - D16
=D14/(D13-1)Divide by n − 1 (sample) - D19
=STDEV.P(B2:B9)Population standard deviation - D20
=STDEV.S(B2:B9)Sample standard deviation - D23
=D20/D12Coefficient of variation
Illustrative data. Results are rounded for display; the cells hold the full precision.
Which one should you use?
Ask one question: do I want to describe exactly these values, or draw conclusions about a larger group? If the answer is "describe these values and nothing else", use STDEV.P. If you want to estimate, compare, forecast or test, use STDEV.S. When in doubt, STDEV.S is the safer default: it is what statistics software usually reports as "standard deviation", what t-tests and confidence intervals expect, and the difference disappears as n grows.
Keep the pair consistent. If you use STDEV.S, use VAR.S for the variance and the matching sample version for covariance (COVARIANCE.S). Mixing a population standard deviation with sample-based tests understates the uncertainty. A useful companion number is the coefficient of variation, the standard deviation divided by the mean: =STDEV.S(B2:B9)/AVERAGE(B2:B9) gives 15.3% here, which lets you compare the spread of data sets with different units or scales.
Standard deviation of filtered or conditional data
There is no STDEVIFS function. To get the standard deviation of only the values that meet a condition, filter first and then apply the function, for example =STDEV.S(FILTER(C2:C200,A2:A200="North")) in Excel 365 for the sales of one region, or build a helper column with IF. For data in a filtered list, SUBTOTAL(7,range) works like STDEV.S and SUBTOTAL(8,range) like STDEV.P, and both skip the rows the filter hides. For a frequency table, where each value occurs several times, calculate the weighted variance with SUMPRODUCT, as in the weighted average guide, and take its square root.
Common mistakes
- Using STDEV.P for a sample. Dividing by n when the data are a sample understates the spread, most visibly for small data sets: with 8 values by about 6.5%.
- Mixing sample and population formulas. STDEV.S with VAR.P, or a population SD inside a t-test, gives numbers that do not belong together. Pick one convention per analysis.
- Including totals or subtotals in the range. A sum row inside the range is treated as one more, very large value and inflates the standard deviation.
- Numbers stored as text. Values imported as text are silently skipped, so n is smaller than you think. Check with
=COUNT(range)against the number of rows. - Reading the variance as the spread. The variance is in squared units (minutes²). Report the standard deviation, which is in the original unit.
- Expecting a result from one value. STDEV.S needs at least two numbers and returns #DIV/0! for a single value, because n − 1 is zero.
Frequently asked questions
What is the difference between STDEV.S and STDEV.P?
STDEV.S calculates the sample standard deviation and divides the sum of squared deviations by n - 1. STDEV.P calculates the population standard deviation and divides by n. STDEV.S is therefore always slightly larger, by the factor square root of n / (n - 1).
Should I use STDEV.S or STDEV.P?
Use STDEV.P only when the data contain every member of the group you want to describe. If the data are a sample used to estimate, compare or forecast a larger group, use STDEV.S. Most practical analyses use STDEV.S.
Is STDEV the same as STDEV.S in Excel?
Yes. The older STDEV function returns the same result as STDEV.S, and STDEVP returns the same as STDEV.P. Both old functions still work for compatibility, but the dotted names make the choice clearer.
Why does the sample standard deviation divide by n - 1?
Deviations measured from the sample mean are on average a little smaller than deviations from the true mean, so dividing by n would underestimate the variance. Dividing by n - 1, called Bessel's correction, removes that bias.
How do I calculate standard deviation by hand in Excel?
Subtract the mean from each value, square the differences, add them up with SUM or DEVSQ, divide by n - 1 for a sample or by n for a population, and take the square root with SQRT.