Excel STDEV/STDEV.P/STDEV.S Functions
last modified October 5, 2026
The STDEV family of functions measures how far numbers spread out
from their average. Excel offers three variants: the modern STDEV.S
for samples, STDEV.P for populations, and the legacy
STDEV. This tutorial provides a comprehensive guide with detailed
examples. You'll learn when to use each function, how they differ, and
practical applications to analyze data variability effectively in your
spreadsheets.
STDEV Functions Basics
Standard deviation is the square root of the variance. A small value means the numbers cluster close to the mean; a large value means they are widely dispersed. The three functions share the same syntax and differ only in the denominator they use.
| Component | Description |
|---|---|
| Function Name | STDEV, STDEV.S, STDEV.P |
| Syntax | =STDEV.S(number1, [number2], ...) |
| Arguments | 1-255 values |
| Return Value | Standard deviation of the arguments |
This table breaks down the essential components of the standard deviation
functions. The syntax shown for STDEV.S applies equally to
STDEV.P and the legacy STDEV.
| Argument | Meaning |
|---|---|
| number1 | Required. The first number, cell reference, or range to analyze. |
| number2, ... | Optional. Additional numbers, cell references, or ranges, up to a total of 255 arguments. |
Inside a range, these functions ignore empty cells, text, and logical values, but they include cells that hold the number zero. Logical values and text that looks like a number, when typed directly into the argument list, are counted instead. The key decision is not how to pass the data but whether the data is a sample or a population.
Sample vs Population Standard Deviation
STDEV.S divides the squared deviations by n-1 and
STDEV.P divides them by n. The n-1 divisor, called Bessel's
correction, makes the sample result an unbiased estimate of the population
spread. For large samples the two values converge.
| Feature | STDEV.S (legacy STDEV) | STDEV.P (legacy STDEVP) |
|---|---|---|
| Data assumed to be | A sample of the population | The entire population |
| Denominator | n-1 | n |
| Purpose | Estimate spread from partial data | Describe the exact spread of all data |
| Single numeric value | #DIV/0! error | Returns 0 |
| Companion variance function | VAR.S | VAR.P |
Use STDEV.S whenever your data is a subset, such as polled users
or sampled products, and STDEV.P only when every member of the
group is present, such as all students in one class.
Legacy Names: STDEV and STDEVP
Before Excel 2010 the two functions were named STDEV and
STDEVP. STDEV is the legacy sample function,
identical to STDEV.S, and STDEVP is the legacy
population function, identical to STDEV.P. Both are kept for
backward compatibility and return the same results as their modern
counterparts.
=STDEV(A1:A4) ' same result as =STDEV.S(A1:A4) =STDEVP(A1:A4) ' same result as =STDEV.P(A1:A4)
The legacy names still work in every new workbook, but Microsoft recommends
STDEV.S and STDEV.P for new formulas because the
meaning is spelled out in the name.
Basic STDEV.P Example
This example demonstrates STDEV.P calculating population standard
deviation for a complete dataset of test scores.
| A | B |
|---|---|
| 85 | |
| 90 | |
| 78 | |
| 92 | |
| 88 | |
| =STDEV.P(A1:A5) |
The table shows test scores for an entire class (population).
STDEV.P calculates how much these scores vary from the mean of
86.6.
=STDEV.P(A1:A5)
This formula returns approximately 4.88, indicating moderate variation in test
scores. Since we have all student scores, we use STDEV.P for the
population standard deviation.
STDEV.S with Sample Data
When working with a sample of a larger population, use STDEV.S.
This example calculates standard deviation for product weights from a sample.
| A | B |
|---|---|
| 502 | |
| 498 | |
| 505 | |
| 495 | |
| =STDEV.S(A1:A4) |
The table shows weights (in grams) of four randomly selected products from a
production line. STDEV.S estimates the standard deviation for the
entire production.
=STDEV.S(A1:A4)
The result is approximately 4.40 grams. This sample standard deviation helps quality control assess production consistency. The n-1 denominator provides an unbiased estimate.
STDEV with Mixed Data
The legacy STDEV function handles mixed data types similarly to
STDEV.S. This example shows its behavior with numbers and text.
| A | B |
|---|---|
| 10 | |
| 15 | |
| Text | |
| 20 | |
| =STDEV(A1:A4) |
The table demonstrates how STDEV ignores non-numeric values when
calculating standard deviation. Only the numbers 10, 15, and 20 are considered.
=STDEV(A1:A4)
The formula returns exactly 5.0 as the standard deviation of 10, 15, and 20.
While STDEV still works, Microsoft recommends using
STDEV.S for new worksheets as it's more clearly named.
Comparing STDEV.P and STDEV.S
This example demonstrates the difference between population and sample standard deviation calculations using the same dataset.
| A | B | C |
|---|---|---|
| 22 | ||
| 25 | ||
| 28 | ||
| 31 | ||
| =STDEV.P(A1:A4) | =STDEV.S(A1:A4) |
The table shows the same temperature readings analyzed as both a population (column B) and a sample (column C). This highlights the mathematical difference between the two functions.
=STDEV.P(A1:A4) ' 3.35, divides by n =STDEV.S(A1:A4) ' 3.87, divides by n-1
STDEV.P returns 3.35 (using n denominator), while
STDEV.S returns 3.87 (using n-1). The sample version produces a
larger value to account for estimation uncertainty.
STDEV.P with Named Range
Standard deviation functions work well with named ranges, improving formula
readability. This example uses STDEV.P with a named range.
| A | B |
|---|---|
| 8.2 | |
| 8.5 | |
| 8.3 | |
| 8.6 | |
| =STDEV.P(Diameters) |
Assuming A1:A4 is named "Diameters", this calculates population standard deviation of machined part diameters. Named ranges make formulas more understandable.
=STDEV.P(Diameters)
The formula returns approximately 0.16, indicating tight diameter control. Named ranges are especially helpful in complex worksheets with many calculations.
STDEV Functions with Blank Cells
Standard deviation functions ignore blank cells in calculations. This example
shows STDEV.S behavior with incomplete data.
| A | B |
|---|---|
| 120 | |
| 125 | |
| 118 | |
| =STDEV.S(A1:A4) |
The table contains three blood pressure readings with one blank cell.
STDEV.S automatically excludes the blank from its calculation.
=STDEV.S(A1:A4)
The formula returns approximately 3.61 for the three valid values. Blank cells don't affect the result, making these functions robust for real-world datasets with missing values.
STDEV vs STDEVA
The companion function STDEVA estimates the sample standard
deviation but, unlike STDEV.S, it counts text and logical values
found in references: TRUE is read as 1, while FALSE and any text are read as
0. Empty cells are still ignored. The population counterpart is
STDEVPA.
| A | B |
|---|---|
| 10 | |
| TRUE | |
| 20 | |
| =STDEV.S(A1:A3) | |
| =STDEVA(A1:A3) |
For the same range, STDEV.S returns 7.07 because it sees only the
numbers 10 and 20. STDEVA returns 9.50 because it counts TRUE as
the value 1 and computes the spread of 10, 1, and 20.
=STDEV.S(A1:A3) ' 7.07, TRUE is ignored =STDEVA(A1:A3) ' 9.50, TRUE is counted as 1
Relationship to VAR Functions
Standard deviation is the square root of variance, so each STDEV function has
a matching variance function: STDEV.S equals
SQRT(VAR.S(...)) and STDEV.P equals
SQRT(VAR.P(...)). Variance expresses spread in squared units,
while standard deviation is back in the original units, which is why it is
usually easier to interpret.
=VAR.S(A1:A3) ' 25, variance of 10, 15, 20 =STDEV.S(A1:A3) ' 5, the square root of 25 =VAR.P(A1:A3) ' 16.67, population variance =STDEV.P(A1:A3) ' 4.08, the square root of 16.67
STDEV Errors and Troubleshooting
| Cause | Symptom | Fix |
|---|---|---|
| Fewer than two numeric values | #DIV/0! error | Supply more values, or use STDEV.P for a complete dataset |
| Text or logical cells in the range | Result uses fewer values than expected | Use STDEVA or STDEVPA, or convert numbers stored as text with VALUE |
| Range contains an error value | The same error is returned | Remove the error in the source cells first |
| Sample function used on full population | STDEV.S slightly larger than STDEV.P | Expected behavior of the n-1 divisor; pick the function by data scope |
| Unconvertible text typed directly as an argument | #VALUE! error | Pass numbers or cell references instead |
The standard deviation functions are essential for measuring data dispersion
in Excel. Choose STDEV.S (or the legacy STDEV) when
your data is a sample and STDEV.P when you have the entire
population; the n-1 versus n denominator is the only mathematical difference
between them. Both ignore empty cells, text, and logical values inside ranges
but include zeros, and both propagate any error found in the referenced cells.
When text and logical values must count, switch to STDEVA and
STDEVPA, and when you need spread in squared units, reach for
VAR.S and VAR.P. Picking the right variant keeps
your variability measurements accurate and easy to explain.
Author
List all Excel Formulas.