Spreadsheet How-To
How to Calculate Standard Deviation in Excel and Google Sheets
Type =STDEV.S(A1:A5) for a sample, or =STDEV.P(A1:A5) when your values are the entire population. Both functions work the same way in Excel and Google Sheets. For the values 4, 8, 6, 5 and 12, STDEV.S returns 3.1623 and STDEV.P returns 2.8284: they differ only in dividing the squared deviations by n − 1 = 4 or by n = 5.
Which function to type
| Function | Use it for | Divides by | Result for 4, 8, 6, 5, 12 |
|---|---|---|---|
| STDEV.S | A sample taken from a larger group | n − 1 | 3.1623 |
| STDEV.P | Every member of the population | n | 2.8284 |
| VAR.S | Sample variance | n − 1 | 10 |
| VAR.P | Population variance | n | 8 |
| STDEV | Older name of STDEV.S | n − 1 | 3.1623 |
| STDEVP | Older name of STDEV.P | n | 2.8284 |
All six functions exist in both Excel and Google Sheets. The sample versions are the right default: real data are almost always a sample from a larger group that you want to describe, and dividing by n − 1 corrects the tendency of a sample to understate the spread (see why sample variance divides by n − 1).
Step by step
- Enter the values in one column, for example 4, 8, 6, 5 and 12 in cells A1 to A5.
- Click an empty cell and type
=STDEV.S(A1:A5), then press Enter. The cell shows 3.1623. - For the population version type
=STDEV.P(A1:A5). The cell shows 2.8284. - For the variance, type
=VAR.S(A1:A5)(10) or=VAR.P(A1:A5)(8). The variance is the square of the standard deviation.
The functions take a range, several ranges or single numbers, and ignore empty cells and text inside a range. They return the standard deviation in the same units as the data.
Check the answer by hand
Mean = (4 + 8 + 6 + 5 + 12) / 5 = 7
Squared deviations = 9, 1, 1, 4, 25, which sum to 40
Sample: s = √(40 / 4) = √10 = 3.1623
Population: σ = √(40 / 5) = √8 = 2.8284
To see the same steps for your own numbers, use the standard deviation calculator.
Related quantities to calculate next
| Quantity | Formula (Excel and Google Sheets) | Result for the same data |
|---|---|---|
| Standard error of the mean | =STDEV.S(A1:A5)/SQRT(COUNT(A1:A5)) | 1.4142 |
| Coefficient of variation | =STDEV.S(A1:A5)/AVERAGE(A1:A5) | 0.4518 |
| Mean | =AVERAGE(A1:A5) | 7 |
| Sum of squared deviations | =DEVSQ(A1:A5) | 40 |
The standard error of the mean is the standard deviation divided by √n, and it is the quantity behind confidence intervals and t-tests; see confidence intervals in Excel.
Errors and surprises
- #DIV/0! means the range holds fewer than two numbers. A sample needs at least two values, since one value has no spread.
- Text and blanks are skipped. A number stored as text (left-aligned, sometimes with a green corner in Excel) is ignored inside a range, which quietly changes the count. Convert it to a number first.
- Logical values in a range are ignored by STDEV.S and STDEV.P. Excel's STDEVA and STDEVPA include them.
- Do not mix the two versions in one report. Use the sample version everywhere unless the data really are the whole population, and say which one you used.
Try the Standard Deviation Calculator
Check the sample and population standard deviation of your data, with every step shown.
Try the Variance Calculator
Get the sample and population variance, mean and sum of squares for any list of numbers.
Frequently Asked Questions
What is the difference between STDEV, STDEV.S and STDEV.P?
STDEV and STDEV.S are the same calculation, the sample standard deviation with n − 1 in the denominator; STDEV is the older name kept for compatibility. STDEV.P, like the older STDEVP, divides by n and is for data that contain every member of the population. Use STDEV.S unless you truly have the whole population.
Why does Excel give a different standard deviation from my calculator?
Almost always because one of them divides by n and the other by n − 1. Scientific calculators label the two keys σn and σn−1, or σx and Sx. For the values 4, 8, 6, 5 and 12 the two answers are 2.8284 and 3.1623. Match STDEV.P to the population key and STDEV.S to the sample key.
How do I calculate the standard deviation for one group only?
Filter the values first. In Google Sheets and in Excel 2021 or Microsoft 365, =STDEV.S(FILTER(B2:B20,A2:A20="Group A")) uses only the rows whose label in column A is Group A. Older versions of Excel need the array formula STDEV.S(IF(A2:A20="Group A",B2:B20)) entered with Ctrl+Shift+Enter.
How do I find the standard deviation of a frequency table?
Neither program has a function for grouped data, but SUMPRODUCT does the work. With class midpoints in B2:B7 and frequencies in C2:C7, put the mean in E1 with =SUMPRODUCT(B2:B7,C2:C7)/SUM(C2:C7). The sample standard deviation is then =SQRT(SUMPRODUCT(C2:C7,(B2:B7-E1)^2)/(SUM(C2:C7)-1)). It is an estimate, because the original values inside each class are lost.
What is a good standard deviation?
There is no universal good value, because it is measured in the units of the data. Judge it against the mean using the coefficient of variation, =STDEV.S(A1:A5)/AVERAGE(A1:A5), which is 0.4518 (45%) for 4, 8, 6, 5 and 12, or against the spread you expect from a similar data set.
Can I calculate one standard deviation from two separate ranges?
Yes, list both ranges as arguments: =STDEV.S(A1:A10,C1:C10). The function pools the values from the two ranges into one sample and returns a single standard deviation for all of them. It does not calculate one standard deviation per range or combine two separate ones, so use it only when the two ranges belong to the same sample.