How to calculate standard deviation in Excel
How to calculate standard deviation in Excel depends on the statistical model for the dataset: a sample standard deviation (estimated from a subset) or a population standard deviation (computed for the entire population). Excel separates these cases with STDEV.S and STDEV.P.
Standard deviation as a measure of dispersion
Standard deviation quantifies typical spread around a central value (mean). Larger standard deviation indicates greater variability; smaller standard deviation indicates values clustered more tightly near the mean.
For observations \(x_1, x_2, \dots, x_n\), the sample mean is \[ \bar{x}=\frac{1}{n}\sum_{i=1}^{n} x_i. \] The sample standard deviation and population standard deviation are defined by \[ s=\sqrt{\frac{\sum_{i=1}^{n}(x_i-\bar{x})^2}{n-1}},\qquad \sigma=\sqrt{\frac{\sum_{i=1}^{N}(x_i-\mu)^2}{N}}. \] The denominators \(n-1\) (sample) and \(N\) (population) distinguish an estimate from a full-population calculation.
Excel functions that match the statistical definition
| Statistical quantity | Excel function | Underlying denominator | Typical use |
|---|---|---|---|
| Sample standard deviation | STDEV.S(range) |
\(n-1\) | Measured data treated as a sample from a broader process or population |
| Population standard deviation | STDEV.P(range) |
\(n\) | Complete population values (no inferential step) |
| Sample variance | VAR.S(range) |
\(n-1\) | Variance reported directly, or as an intermediate for other formulas |
| Population variance | VAR.P(range) |
\(n\) | Full population variance |
Practical interpretation often favors STDEV.S for experimental data, surveys, and repeated measurements, because those datasets typically represent a sample from a larger phenomenon. STDEV.P fits cases such as “all members of a small class” or “all transactions in a closed accounting period.”
Worked example with an Excel-ready data range
Consider eight observations placed in cells A2:A9:
2, 4, 4, 4, 5, 5, 7, 9.
The mean is
\[
\bar{x}=\frac{2+4+4+4+5+5+7+9}{8}=\frac{40}{8}=5.
\]
| Row | \(x_i\) | \(x_i-\bar{x}\) | \((x_i-\bar{x})^2\) |
|---|---|---|---|
| 1 | 2 | \(-3\) | 9 |
| 2 | 4 | \(-1\) | 1 |
| 3 | 4 | \(-1\) | 1 |
| 4 | 4 | \(-1\) | 1 |
| 5 | 5 | \(0\) | 0 |
| 6 | 5 | \(0\) | 0 |
| 7 | 7 | \(2\) | 4 |
| 8 | 9 | \(4\) | 16 |
The sum of squared deviations is \[ \sum (x_i-\bar{x})^2 = 9+1+1+1+0+0+4+16 = 32. \] Sample variance and sample standard deviation are \[ s^2=\frac{32}{8-1}=\frac{32}{7}\approx 4.5714,\qquad s=\sqrt{\frac{32}{7}}\approx 2.1381. \] Population variance and population standard deviation are \[ \sigma^2=\frac{32}{8}=4,\qquad \sigma=\sqrt{4}=2. \]
Excel aligns with these definitions:
=STDEV.S(A2:A9) returns approximately 2.1381 for the sample standard deviation, and
=STDEV.P(A2:A9) returns 2 for the population standard deviation.
Data handling details that affect the result
Blank cells in a referenced range are ignored by standard deviation functions. Non-numeric text inside the range is also ignored, which can conceal data-entry issues if a numeric value was accidentally stored as text. A quick diagnostic is that COUNT(range) counts numeric cells, while COUNTA(range) counts non-empty cells; a mismatch often indicates text values or stray characters.
Datasets filtered in Excel can be summarized with SUBTOTAL so that hidden rows are excluded. The function number 7 corresponds to sample standard deviation and 8 corresponds to population standard deviation (visible rows under filtering).
Visualization: Excel range and dispersion around the mean
A2:A9 and the function STDEV.S(A2:A9). The right panel shows the same values on a number line with the mean at 5 and a shaded interval of one sample standard deviation (approximately ±2.14) around the mean, illustrating standard deviation as a measure of dispersion.
Common pitfalls
Mixing sample and population formulas changes results, especially for small \(n\). When \(n\) is small, \(n-1\) versus \(n\) materially changes the variance and standard deviation. Matching STDEV.S to sample data and STDEV.P to full-population data prevents a silent mismatch between statistical intent and Excel output.