standarddeviationcalculator.net

Updated

Learn

How to calculate standard deviation in Excel

In one line: put your numbers in a column and type =STDEV.S(A1:A20). The rest of this page covers which function to use when, the errors that trip people up, and the cases Excel has no built-in function for.

The short version

  1. Put your values in a column, say A1 to A20 — one number per cell, nothing else in the range.
  2. Click an empty cell.
  3. Type =STDEV.S(A1:A20) and press Enter.

That gives the sample standard deviation, which is what you want unless those twenty rows are the complete group you are describing. If they are, use =STDEV.P(A1:A20) instead.

Which function to use

FunctionDivides byUse forNotes
STDEV.Sn − 1SampleThe usual choice
STDEV.PnPopulationOnly when nothing is left out
STDEVn − 1SampleLegacy name for STDEV.S
STDEVPnPopulationLegacy name for STDEV.P
STDEVAn − 1SampleCounts text as 0, TRUE as 1
STDEVPAnPopulationSame, population form
VAR.S / VAR.Pn − 1 / nVarianceThe value before the square root

Avoid STDEVA and STDEVPA unless you specifically want text counted as zero. That behaviour turns a stray label into a data point of 0, which drags the mean down and inflates the spread — a silent and hard-to-spot error.

Standard deviation with a condition

There is no STDEVIF. To get the standard deviation of only the rows matching a criterion, wrap an IF inside STDEV.S:

=STDEV.S(IF(B2:B100="North", A2:A100))

In Microsoft 365 and Excel 2021 that works as typed, because those versions spill array results automatically. In Excel 2019 and earlier you must confirm it with Ctrl+Shift+Enter, which wraps it in braces.

For more than one condition, multiply the tests together:

=STDEV.S(IF((B2:B100="North")*(C2:C100>50), A2:A100))

Things Excel has no function for

You wantBuild it as
Relative SD (%RSD)=STDEV.S(A1:A20)/AVERAGE(A1:A20)*100
Coefficient of variation=STDEV.S(A1:A20)/AVERAGE(A1:A20)
Standard error of the mean=STDEV.S(A1:A20)/SQRT(COUNT(A1:A20))
Weighted mean=SUMPRODUCT(A2:A20,B2:B20)/SUM(B2:B20)
Ignoring error cells=AGGREGATE(7,6,A1:A20)
95% margin of error=CONFIDENCE.T(0.05,STDEV.S(A1:A20),COUNT(A1:A20))

Errors and what causes them

SymptomCauseFix
#DIV/0!Fewer than 2 numeric cells for STDEV.SCheck the numbers are not stored as text
#VALUE!A direct argument is non-numericReference a range rather than typing values
#NAME?Function misspelled, or STDEV.S in a pre-2010 fileUse STDEV for old versions
Returns 0Every value is identicalCorrect — zero spread
Number looks too smallBlank or text cells being skippedCompare COUNT against COUNTA

The text-stored-as-number trap is the big one. Data pasted from a website or imported from CSV often arrives as text. Excel silently omits those cells from STDEV.S, so you get an answer — just one computed from a fraction of your rows. Check by comparing =COUNT(A1:A20) (numbers only) with =COUNTA(A1:A20) (anything non-empty). If they disagree, some rows are text.

Google Sheets

Every function above works identically in Google Sheets, including the dotted names. STDEV, STDEVP, STDEV.S and STDEV.P all behave the same way. The one difference worth knowing is that Sheets requires ARRAYFORMULA() around the conditional pattern rather than Ctrl+Shift+Enter:

=ARRAYFORMULA(STDEV(IF(B2:B100="North", A2:A100)))

Check your spreadsheet against this calculator

Copy the column out of your sheet and paste it below. The result should match your STDEV.S exactly — and if it does not, the count reported here will usually show why, because it tells you how many values were actually read.

Separate numbers with commas, spaces or new lines, or paste a spreadsheet column. Decimals and negatives are fine; write 10:3 for a value that occurs 3 times.

Try:
Calculation type

Not sure which? How to choose sample or population

Standard deviation (sample)

3.04432

Your values typically sit about 3.04 above or below their mean of 15.88, in the same units as your data. 5 of 8 values (63%) fall between 12.83 and 18.92, within one standard deviation of the mean; for normally distributed data about 68% would.

Population SD (σ): 2.8477, if these values are the whole group.

Count (n)
8
Mean (x̄)
15.875
Variance (s²)
9.26786
Standard error
1.07633
Minimum
12
Q1 (25%)
13.75
Median
15.5
Q3 (75%)
17.5
Maximum
21
Range
9
More statistics (5)
Relative SD (%RSD)
19.1768%
Coefficient of variation
0.191768
Sum (Σx)
127
Sum of squares, Σ(x − x̄)²
64.875
IQR (Q3 − Q1)
3.75

Data distribution

10 15 20 25 mean 15.88 −1 SD +1 SD 12 — 1.27 SD below the mean15 — 0.287 SD below the mean17 — 0.37 SD above the mean14 — 0.616 SD below the mean19 — 1.03 SD above the mean21 — 1.68 SD above the mean16 — 0.0411 SD above the mean13 — 0.944 SD below the mean Value

Shaded bands mark ±1, ±2 and ±3 SD from the mean. 5 of 8 values (63%) fall within ±1 SD.

Chart as text

Mean 15.875, sample standard deviation s = 3.04432, from 8 values between 12 and 21.

  • Within ±1 SD (12.83 to 18.92): 5 of 8 values (63%). About 68% for normal data.
  • Within ±2 SD: 8 (100%). About 95% for normal data.
  • Within ±3 SD: 8 (100%). About 99.7% for normal data.
Show the working, step by step
Standard deviation in Excel: the worked example on this page, with its result and chart
Standard deviation in Excel: the worked example above, at a glance.

Common questions

What is the formula for standard deviation in Excel?

=STDEV.S(A1:A20) for a sample, =STDEV.P(A1:A20) for a population. Replace the range with your own cells.

What is the difference between STDEV.S and STDEV.P?

STDEV.S divides by n − 1 and is for sample data. STDEV.P divides by n and is for a complete population. Use STDEV.S unless your rows genuinely are the entire group. The full comparison is here.

Is STDEV the same as STDEV.S?

Yes. STDEV() is the pre-2010 name and still works for backward compatibility; it is the sample version. STDEVP() is likewise the old name for STDEV.P. New workbooks should use the dotted forms, which are clearer about what they do.

Why does my Excel standard deviation return #DIV/0!

Almost always because STDEV.S received fewer than two numeric cells. It needs at least two, since it divides by n − 1.

The usual cause is that the numbers are stored as text — they will be left-aligned in the cell, and Excel skips them. Select the range, click the warning triangle, and choose "Convert to Number".

How do I calculate standard deviation ignoring blanks and errors?

STDEV.S already ignores blank cells and text. To ignore error values too, use =AGGREGATE(7, 6, A1:A20) — 7 selects STDEV.S and 6 tells it to skip errors.

Written and reviewed by our editorial team. Last updated . Method and sources: how these numbers are computed.