STDEV.S Function

Estimates the standard deviation of a sample.

Category

Statistical

Available in

Excel 2010+, Microsoft 365

Returns

A number

Taught hands-on in Data Analytics · Descriptive Statistics module

Overview

Standard deviation measures how spread out numbers are around their average — roughly, how far a typical value sits from the mean. A small result means the values are bunched together; a large one means they vary a lot.

STDEV.S treats your numbers as a sample of a bigger group. Use the .S (sample) version when your numbers are a sample of a bigger group — some customers, some days, some products — and the .P (population) version when they are the entire group you care about. The sample version divides by n − 1 instead of n, which makes it slightly larger to allow for the people you didn't measure.

In a range, text, TRUE/FALSE and empty cells are ignored. Numbers, logical values and numbers-as-text typed straight into the argument list are counted.

Syntax

=STDEV.S(number1, [number2], …)

Arguments

ArgumentRequired?What it does
number1YesA number, cell or range from the sample.
number2, …NoUp to 254 more numbers, cells or ranges.

Examples

Example 1 — How much do sales vary?

Seven reps' sales, treated as a sample of what Northstar's reps typically sell.

E2fx=STDEV.S(C2:C8)
ABCDE
1RepRegionSalesMeasureResult
2AnaNorth62000Std deviation10333.87
3BenSouth48500Average53635.71
4CyNorth50000
5DiEast71250
6EdSouth39800
7FayNorth55400
8GusEast48500
=STDEV.S(C2:C8) Explain

A typical rep lands about $10,334 above or below the $53,636 average.

Example 2 — Spread relative to the average

The coefficient of variation compares spread across datasets of different sizes.

=STDEV.S(C2:C8)/AVERAGE(C2:C8) Explain

Returns about 0.19 — sales vary by roughly 19% of the average.

Example 3 — Flag unusually high results

A common rule of thumb: more than two standard deviations above the mean is unusual.

=AVERAGE(C2:C8)+2*STDEV.S(C2:C8) Explain

Returns about 74,303. No rep is above it — Di's 71,250 is high but not unusual.

Common mistakes

  • #DIV/0! There are fewer than two numbers. A sample of one has no spread to estimate.
  • Sample or population? Mixing them up is the most common mistake. If in doubt — your data is a sample of something bigger — use the .S version.
  • Numbers stored as text. Imported values that are really text are skipped. Convert them with VALUE or Data › Text to Columns.

Notes

Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.