Standard Deviation (Spread)

All versionsSTDEV.SSTDEV.P

Standard deviation measures how spread out your numbers are — low means tightly clustered, high means scattered. The only real decision is whether your data is a sample (use STDEV.S) or the whole population (STDEV.P).

Quick formula: for the standard deviation of a sample:

=STDEV.S(B2:B100)

Use STDEV.S when the data is a sample of a larger group; STDEV.P when it’s the entire population.

Functions used (tap for the full reference guide):

STDEV AVERAGE

The example

Two columns with the same average but different spread.

	A	B
1	Tight	Spread
2	49	20
3	50	50
4	51	80
5	StdDev: 1.0	StdDev: 30.0

The formula

Both columns average 50, but their spread is very different:

=STDEV.S(A2:A4) → 1.0 =STDEV.S(B2:B4) → 30.0

How it works

Standard deviation summarizes distance from the mean:

It measures, roughly, how far the typical value sits from the average.
The “Tight” column (49, 50, 51) barely moves from its mean of 50 — standard deviation ~1.
The “Spread” column (20, 50, 80) ranges widely around the same mean — standard deviation ~30.
Choose STDEV.S for sample data (the common case) or STDEV.P when you truly have every member of the population.

Sample vs population in one line: STDEV.S divides by n−1 (corrects for sampling); STDEV.P divides by n. For most real-world data — which is a sample — use STDEV.S.

Try it: interactive demo

Live demo

Type numbers (commas); see the average and sample standard deviation.

Values

Average: StdDev (sample):

Variations

Population standard deviation

When the data is the whole population:

=STDEV.P(B2:B100)

Variance

Standard deviation squared:

=VAR.S(B2:B100)

Coefficient of variation

Spread relative to the mean (comparable across scales):

=STDEV.S(B2:B100) / AVERAGE(B2:B100)

Pitfalls & errors

Sample vs population is the key choice. Using STDEV.P on sample data understates the spread. When in doubt, your data is a sample — use STDEV.S.

Old names still work. STDEV = STDEV.S and STDEVP = STDEV.P, kept for compatibility.

Outliers inflate it. One extreme value swings standard deviation a lot — check for outliers (and consider the median/IQR) if the spread looks surprising.

Practice workbook

📊

Download the free Standard Deviation (Spread) practice workbook

Tight vs spread columns with live STDEV.S/STDEV.P, variance and coefficient-of-variation, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I calculate standard deviation in Excel?

Use =STDEV.S(range) for sample data (the usual case) or =STDEV.P(range) when the range is the entire population. The result shows how spread out the values are around the mean.

What's the difference between STDEV.S and STDEV.P?

STDEV.S divides by n-1 to correct for sampling and is for samples; STDEV.P divides by n and is for a complete population. Most real data is a sample, so use STDEV.S.

How do I compare spread between datasets with different scales?

Use the coefficient of variation: =STDEV.S(range)/AVERAGE(range), which expresses spread relative to the mean.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Function references: STDEV · AVERAGE