Standard deviation and variance in Excel
An average tells you where your numbers sit. The standard deviation tells you how spread out they are: whether every delivery takes about the same time, or some arrive fast and others very late.
In short: =STDEV.S(B2:B9) is the standard deviation of a sample, =STDEV.P(B2:B9) the standard deviation of a whole population. Variance is its square: =VAR.S(B2:B9) and =VAR.P(B2:B9). When unsure, STDEV.S is the safe choice.
Before you start: AVERAGE. No statistics background needed, the terms are explained here.
Try it now
A courier timed eight trips. How much do the delivery times vary? Click the yellow cell and type the formula.
Task A courier timed eight deliveries. These eight trips are a sample of all deliveries. In E2, calculate the standard deviation of the delivery times.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Delivery | Minutes | |||
| 2 | 1 | 34 | Standard deviation | ||
| 3 | 2 | 41 | |||
| 4 | 3 | 29 | |||
| 5 | 4 | 38 | |||
| 6 | 5 | 45 | |||
| 7 | 6 | 31 | |||
| 8 | 7 | 36 | |||
| 9 | 8 | 42 |
The standard deviation formula in Excel
Both functions take a range or a list of numbers and do the same thing: average the squared distances from the mean, then take the square root. The only difference is what they divide the sum of squares by.
| Function | What it returns | In the example |
|---|---|---|
STDEV.S(number1,…) | Sample standard deviation, divides by n-1 | 5.55 minutes |
STDEV.P(number1,…) | Population standard deviation, divides by n | 5.20 minutes |
VAR.S(number1,…) | Sample variance, the square of STDEV.S | 30.86 |
VAR.P(number1,…) | Population variance, the square of STDEV.P | 27 |
Variance comes in awkward units (minutes squared here), so for reporting you nearly always quote the standard deviation, which is in the same units as the data. Variance matters mostly as a building block, for example in regression or ANOVA.
Both functions skip empty cells, text and TRUE/FALSE inside a range. A zero, however, is a real value and counts. A 0 typed in for "not measured" will distort the result.
STDEV.S or STDEV.P: sample or population?
What matters is not how many values you have but what they represent. The eight trips are a selection from all deliveries you want to say something about, so they are a sample. STDEV.S divides by n-1 to make up for the fact that a sample tends to understate the true spread. If you really have every value, say the salaries of all twelve people on a team and you only want to describe that team, you have the population and STDEV.P fits.
With large data sets the two results get very close. With eight values the gap here is about 0.36 minutes.
How to read a standard deviation
The trips average 37 minutes with a standard deviation of 5.55. If the data are roughly normally distributed, about two thirds of values fall within one standard deviation of the mean, here between about 31 and 43 minutes. That rule of thumb does not hold for skewed data with outliers. To express a single value as a number of standard deviations from the mean, calculate its z-score: =(B2-AVERAGE(B$2:B$9))/STDEV.S(B$2:B$9), or use STANDARDIZE.
Mean, median and mode
A measure of spread always goes with a measure of the center. Excel offers three:
=AVERAGE(B2:B9)is the arithmetic mean; the AVERAGE lesson covers it in detail.=MEDIAN(B2:B9)is the middle value of the sorted list. Half the values are below it, half above.=MODE.SNGL(B2:B9)is the most frequent value. When no value appears twice it returns #N/A, which is exactly what happens with the eight delivery times.
For the delivery times, mean and median are both 37. Order amounts behave differently: one big order pulls the mean up while the median stays put. In the next exercise the mean is 160.63 but the median only 135.
One more exercise: quartiles and the interquartile range
Quartiles split the sorted values into four equal parts. The distance from the first to the third quartile covers the middle half of the data, a measure of spread that a single outlier barely moves. In Excel, =QUARTILE.INC(range,number) returns a quartile: 1 gives the first, 2 the median and 3 the third.
Task In E2, calculate the interquartile range of the order amounts: the third quartile minus the first quartile. Use QUARTILE.INC.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order | Amount $ | |||
| 2 | A-201 | 120 | Interquartile range | ||
| 3 | A-202 | 85 | |||
| 4 | A-203 | 240 | |||
| 5 | A-204 | 95 | |||
| 6 | A-205 | 150 | |||
| 7 | A-206 | 310 | |||
| 8 | A-207 | 110 | |||
| 9 | A-208 | 175 |
This exercise is part of Premium.
Premium unlocks every exercise in every lesson, unlimited practice with fresh tasks and the final test with certificate.
See Premium Already Premium? Log in
The first exercise in this lesson is free.
| Formula | Result |
|---|---|
=QUARTILE.INC(B2:B9,1) | 106.25 (a quarter of the orders are below) |
=QUARTILE.INC(B2:B9,2) | 135, the same as MEDIAN |
=QUARTILE.INC(B2:B9,3) | 191.25 |
=QUARTILE.EXC(B2:B9,1) and ,3) | 98.75 and 223.75 |
QUARTILE.INC and QUARTILE.EXC use different interpolation rules, and with small data sets the results differ noticeably, as the table shows. Neither is wrong; just stick to one method within a report. For any other share use PERCENTILE.INC, for instance =PERCENTILE.INC(B2:B9,0.9) for the 90th percentile. To build a frequency distribution, FREQUENCY counts how many values fall into each bin, and LARGE, SMALL and RANK pick out single positions.
Common mistakes
STDEV.P on a sample
If you are drawing conclusions about a bigger group from a selection, STDEV.P understates the spread. Use STDEV.S unless you are sure you have every value.
Zeros instead of blanks
A 0 is a measurement. Leave missing values blank, or the standard deviation will shoot up.
Mixing up variance and standard deviation
"Spread of 30.86 minutes" is the variance with the wrong unit. The standard deviation is its square root: 5.55 minutes.
#DIV/0! with a single value
STDEV.S divides by n-1, which is 0 with one value, so Excel returns #DIV/0!.
Standard deviation in German Excel
| English | German |
|---|---|
=STDEV.S(B2:B9) | =STABW.S(B2:B9) |
| STDEV.P, VAR.S, VAR.P | STABW.N, VAR.S, VAR.P |
| AVERAGE, MEDIAN, MODE.SNGL | MITTELWERT, MEDIAN, MODUS.EINF |
=QUARTILE.INC(B2:B9,1) | =QUARTILE.INKL(B2:B9;1) |
| STANDARDIZE, FREQUENCY | STANDARDISIERUNG, HÄUFIGKEIT |
Note the population version: STDEV.P in English is STABW.N in German, while the variance functions keep their names. German Excel separates arguments with semicolons.
Which Excel versions have these functions?
Microsoft lists STDEV.S, STDEV.P, VAR.S, VAR.P, MODE.SNGL and the .INC and .EXC quartile functions as new in Excel 2010, and documents them for Microsoft 365, Excel 2024, 2021, 2019 and 2016, on Windows and Mac. The older STDEV, STDEVP, VAR and VARP still work but are now listed as compatibility functions. AVERAGE and MEDIAN exist in every version.
FAQ
What is the difference between STDEV and STDEV.S?
They calculate the same thing, both for a sample. STDEV is the older function that Microsoft keeps for compatibility. Use STDEV.S in new workbooks.
How do I calculate variance in Excel?
Use =VAR.S(range) for a sample or =VAR.P(range) for a population. Variance is the standard deviation squared.
What counts as a high standard deviation?
It depends on the mean. A spread of 5 minutes means one thing on a 37 minute trip and another on a 300 minute one. The coefficient of variation makes them comparable: =STDEV.S(B2:B9)/AVERAGE(B2:B9), about 15% here.
How do I calculate a z-score in Excel?
Subtract the mean and divide by the standard deviation: =STANDARDIZE(B2,AVERAGE(B$2:B$9),STDEV.S(B$2:B$9)). A z-score of 2 means the value sits two standard deviations above the mean.
How do I make a frequency distribution in Excel?
Type the upper limits of your bins in a column, say 30, 35, 40 and 45 in D2:D5, then enter =FREQUENCY(B2:B9,D2:D5). It returns one count per bin plus a final count for everything above the last limit. For the delivery times that is 1, 2, 2, 3 and 0. In Microsoft 365 the results spill automatically; in older versions select five cells first and confirm with Ctrl+Shift+Enter.
How do I add standard deviation error bars to a chart?
Select the chart, open Chart Elements with the plus button and tick Error Bars. In the error bar options choose a custom value and point it to the cell holding STDEV.S.