Standard deviation and z-scores

Measure how spread out a sample is, then use z-scores to spot the values that really stand out. Advanced · 6 min · STDEV, STDEV.P, VAR, z-scores, ABS

The standard deviation measures how far values typically sit from the mean. A small one means the data is tightly bunched; a large one means it is spread out.

Excel has two versions, and the difference is about what your data is:

STDEV (also written STDEV.S) when your data is a sample from something bigger
STDEV.P when you have the whole population, every value there is

A z-score turns any value into "how many standard deviations from the mean": (value − mean) / standard deviation. Anything beyond ±2 is unusual.

Steps

Twelve sourdough loaves were pulled from today's bake for a weight check. In G2, find their mean weight. Then, in G3, measure the spread with =STDEV(B2:B13).
STDEV treats these 12 loaves as a sample of a bigger bake. If they were the entire batch, you would use STDEV.P instead. Try it in G4 and compare.
The variance is the standard deviation before its square root is taken, measured in squared grams. In G5, find the sample variance with =VAR(B2:B13).
A z-score says how many standard deviations a loaf is from the mean. In C2, enter =(B2-$G$2)/$G$3 to lock the mean and the sample standard deviation, then fill it down to C13.
A loaf more than 2 standard deviations from the mean, in either direction, is unusual. In D2:D13, show "Check" when the z-score is beyond ±2 and leave the cell blank otherwise: =IF(ABS(C2)>2, "Check", "")

Remember

STDEV (STDEV.S) measures the spread of a sample; STDEV.P is for a whole population.
VAR is the standard deviation squared, in squared units.
A z-score is =(value - mean) / sd. Lock the summary cells with $ before filling down.
ABS(z) > 2 flags values that are unusual in either direction.

Previous: Percentiles and quartiles · Next: Spotting problems with IS functions