Standard Deviation Calculator

How STDEV.S works

Availability: STDEV.S and STDEV.P were introduced in Excel 2010. The older names STDEV and STDEVP still work in every version, including Microsoft 365, and return identical results. Google Sheets has all four, plus STDEVA and STDEVPA.

Standard deviation measures how far the values in a set typically sit from their own average. A low number means the values cluster; a high number means they are spread out. It is in the same units as the data, which is why it is quoted alongside an average far more often than variance is - a mean order of 22 units with a standard deviation of 4.4 tells a reader something immediately.

The choice between STDEV.S and STDEV.P is a statement about what your range represents, not about how accurate you want to be. STDEV.S treats the numbers as a sample drawn from a larger group and divides the summed squared deviations by n-1. STDEV.P treats them as the entire population and divides by n. Because a sample tends to understate the spread of the group it came from, the n-1 denominator corrects upward, so STDEV.S always returns the larger figure on data that varies at all. Use .P only when the range genuinely is everything: all twelve months of one closed year, every employee on the payroll today. Anything that is a measurement, a survey or an ongoing process is a sample.

Both functions ignore text, TRUE/FALSE and empty cells found inside a range reference, and they count zeros as real numbers. That leniency is a trap rather than a convenience: numbers imported as text are skipped silently, which shrinks n and changes the answer with no error to warn you. Comparing =COUNT(range) with =COUNTA(range) is the fastest check. Values typed directly as arguments behave differently again - =STDEV.S(1, 2, "3", TRUE) counts the text and the logical value, while the same entries inside a range would be ignored.

STDEV.S needs at least two numbers and returns #DIV/0! with fewer. STDEVA and STDEVPA look like convenient variants but count text as 0 and TRUE as 1, which drags the result toward zero and is almost never what anyone intends.

Syntax

More worked examples

Common mistakes

#DIV/0! from a range with fewer than two numbers

STDEV.S cannot divide by n-1 when n is 1, so a single value, or a range where everything else is text or blank, returns #DIV/0!. STDEV.P returns 0 in the same situation, which is arguably worse because it looks like an answer.

Numbers stored as text quietly shrink the sample

Ranges ignore text, so a column pasted from a web page or a PDF can lose half its values without erroring. Check with COUNT against COUNTA, then convert the column to real numbers before trusting the result.

Typed arguments follow different rules from ranges

=STDEV.S(2, 4, "6") counts the text "6" as 6 and returns 2, while putting the same three entries in cells and using =STDEV.S(A2:A4) ignores the text and returns 1.414 from two values. The inconsistency is documented behaviour, not a bug, and it is a good reason to always point the function at a range.

Choosing .P because the numbers look complete

Having every row you collected is not the same as having the whole population. Twelve months of sales are a sample of an ongoing business, not a population, and using STDEV.P there understates the spread - noticeably so on small ranges, where the two functions can differ by several per cent.

Filtered rows are still included

STDEV.S reads every cell in the range whether or not a filter is hiding it, so the figure on screen does not match the visible rows. Use =SUBTOTAL(107, A2:A100) for a sample standard deviation that respects the filter, or 108 for the population version.