This guide shows how to calculate standard deviation in Excel with the right function, walks through an example you can verify by hand, and covers two things most tutorials skip: finding the spread of one group inside a longer column, and why Excel sometimes returns an error or a result that looks wrong.
Quick Answer: To calculate standard deviation in Excel, type =STDEV.S(A2:A9) when your numbers are a sample, which is the usual case. Use =STDEV.P(A2:A9) only when the range holds every value in the group. For the eight scores below, STDEV.S returns 5.06 and STDEV.P returns 4.74.
Which Function to Use: STDEV.S or STDEV.P
Use STDEV.S when your numbers are a sample drawn from something larger, such as 30 survey replies out of 2,000 customers. Use STDEV.P when the range contains every value you care about, such as all 12 monthly totals for last year. If you are unsure, choose STDEV.S.
The only mathematical difference is the divisor. STDEV.S divides the sum of squared differences by n − 1, which allows for the way a sample tends to understate the spread of the full group. STDEV.P divides by n. As the dataset grows, the two results move closer together.
| Function | Treats your data as | Divides by | Use it when |
|---|---|---|---|
| STDEV.S | A sample | n − 1 | Your numbers are a sample. This is the default choice. |
| STDEV.P | The whole population | n | The range holds every member of the group. |
| STDEV | A sample | n − 1 | You are in an older file. Microsoft lists it as a compatibility function, so use STDEV.S in new work. |
| STDEVP | The whole population | n | Older files only. STDEV.P replaces it. |
| STDEVA, STDEVPA | A sample, the whole population | n − 1, n | You want text and logical values in the range counted instead of ignored. Rarely needed. |
Function behavior here follows Microsoft’s documentation for STDEV.S, STDEV.P and STDEV. Google Sheets accepts the same STDEV.S and STDEV.P names.
How to Calculate Standard Deviation in Excel, Step by Step
- Enter your values in one column. Put the eight scores in
A2:A9and a label inA1. - Click the cell for the result. Any empty cell works, such as
C2. - Type the function for your data. Use
=STDEV.S(A2:A9)for a sample or=STDEV.P(A2:A9)for a full population. - Press Enter and round if needed. Wrap the formula in ROUND, as in
=ROUND(STDEV.S(A2:A9),2), to show two decimals.
// Sample standard deviation for data in A2:A9
=STDEV.S(A2:A9)
// Population standard deviation for data in A2:A9
=STDEV.P(A2:A9)You can also list several ranges or single cells, for example =STDEV.S(A2:A5,A6:A9). Excel accepts up to 255 arguments and treats them as one set of values. Blank cells, text and TRUE or FALSE inside a referenced range are ignored.
A Worked Example You Can Check by Hand
Eight students scored 82, 88, 79, 91, 85, 90, 77 and 86. These are the same scores used in our standard error guide, so the figures match across both pages. The mean is 84.75.
| Student | Score | Score − mean | Squared difference |
|---|---|---|---|
| 1 | 82 | −2.75 | 7.5625 |
| 2 | 88 | +3.25 | 10.5625 |
| 3 | 79 | −5.75 | 33.0625 |
| 4 | 91 | +6.25 | 39.0625 |
| 5 | 85 | +0.25 | 0.0625 |
| 6 | 90 | +5.25 | 27.5625 |
| 7 | 77 | −7.75 | 60.0625 |
| 8 | 86 | +1.25 | 1.5625 |
| Total | 678 | 0 | 179.5 |
Sample: STDEV.S
179.5 ÷ (8 − 1) = 25.64, which is the sample variance. The square root of 25.64 is 5.06.
=STDEV.S(A2:A9)Result: 5.06
Population: STDEV.P
179.5 ÷ 8 = 22.44, which is the population variance. The square root of 22.44 is 4.74.
=STDEV.P(A2:A9)Result: 4.74
Same eight numbers, two answers. The sample figure is about 7% larger because dividing by 7 instead of 8 makes up for spread you would probably see if you could measure the whole group. If these eight students were the entire class, 4.74 is the right figure.
Check Your Own Numbers
Paste a list of numbers to see both results side by side and compare them with your spreadsheet. The math runs in your browser and nothing is uploaded.
Standard deviation checker
Runs in your browserA single value gives #DIV/0! for the sample figures, exactly as Excel does. Press Ctrl+Enter (Cmd+Enter on Mac) to calculate.
What the Result Tells You
Standard deviation is in the same units as your data, so 5.06 means the scores typically sit about five points from the 84.75 average. A low value means the numbers cluster near the mean. A high value means they are spread out.
In the example, 84.75 ± 5.06 gives a range of 79.69 to 89.81, and four of the eight scores fall inside it. That is 50%, below 68%, which is normal with only eight values. The rule describes large, roughly bell-shaped data, not tiny samples.
To compare spread between datasets with different averages, divide the standard deviation by the mean. =STDEV.S(A2:A9)/AVERAGE(A2:A9) returns 0.0598, or about 6%. This ratio is called the coefficient of variation.
Standard Deviation for One Group in a Longer Column
Excel has no conditional version such as STDEVIFS, so the filter goes inside the formula. Put the group label in column A and the score in column B, then wrap the score range in IF.
| Section (column A) | Score (column B) |
|---|---|
| A | 82 |
| A | 88 |
| B | 79 |
| B | 91 |
| A | 85 |
| B | 90 |
| B | 77 |
| A | 86 |
// Section A, labels in A2:A9 and scores in B2:B9
=STDEV.S(IF($A$2:$A$9="A",$B$2:$B$9))IF returns the score when the label matches and FALSE when it does not. STDEV.S ignores the FALSE values, so only the matching rows are used. In Microsoft 365 and Excel 2021 or later, press Enter as usual. In earlier versions, confirm with Ctrl+Shift+Enter.
Excel 2021, 2024 and 365 also let you use FILTER instead:
=STDEV.S(FILTER($B$2:$B$9,$A$2:$A$9="A"))| Section | Scores | Mean | STDEV.S |
|---|---|---|---|
| A | 82, 88, 85, 86 | 85.25 | 2.50 |
| B | 79, 91, 90, 77 | 84.25 | 7.27 |
The section averages are 85.25 and 84.25, almost identical, yet section B is far more spread out. The average alone would hide that, which is why a measure of spread belongs next to the mean in any summary.
Why Excel Returns an Error or an Odd Answer
The #DIV/0! error. STDEV.S needs at least two numbers. With a single value it returns #DIV/0!, while STDEV.P returns 0. Check that your range actually contains the data you meant to select.
Numbers stored as text. Text inside a range is skipped without any error, so the result rests on fewer values than you expect. Compare =COUNT(A2:A9) with =COUNTA(A2:A9). If the counts differ, something in the range is not a number.
Zeros typed for missing data. A blank cell is skipped, but a 0 is a real value that shifts both the mean and the spread. Leave missing entries empty.
Totals inside the range. A subtotal or grand total row counts as one more data point and distorts the result. Select only the raw values.
The population function on a sample. STDEV.P understates the spread of a sample. In the example above it came out about 0.3 points lower than STDEV.S.
If you want to know how precisely a sample average is estimated rather than how spread out the data is, divide by the square root of the count, as shown in how to calculate standard error in Excel. The SEM guide covers error bars and confidence intervals.
To test whether two groups really differ, see how to calculate a p-value in Excel. For a middle value that outliers cannot drag around, see how to calculate the median in Excel.
Need more free calculators and formula guides?
Explore Free ToolsFrequently Asked Questions
What is a good standard deviation?
There is no universal good value, because standard deviation is in the units of your data. Judge it against the mean with the coefficient of variation: =STDEV.S(A2:A9)/AVERAGE(A2:A9). The example scores give about 6%, which is a tight spread around the average.
Is variance the same as standard deviation?
No. Variance is the standard deviation squared, so it is in squared units. Use VAR.S for a sample and VAR.P for a population. In the example, the sample variance is 25.64 and its square root, the standard deviation, is 5.06.
Why does my calculator give a different answer than Excel?
Many calculators have separate keys for sample and population standard deviation. Excel’s equivalents are STDEV.S and STDEV.P. If the answers differ, you are most likely comparing a sample figure with a population figure.
Can I find the standard deviation of several separate ranges?
Yes. List the ranges inside one function, as in =STDEV.S(A2:A9,C2:C9). Excel treats them as a single set of values. Averaging two separate standard deviations does not give the combined spread.
Does Google Sheets use the same function names?
Yes. Google Sheets supports STDEV.S and STDEV.P, plus the older STDEV and STDEVP, with the same sample and population logic. A formula such as =STDEV.S(A2:A9) works unchanged.
Related Excel Statistics Guides From Anchor AI Tools
Accuracy Note: This guide explains standard statistical formulas for planning and educational purposes. For research, clinical, or regulated reporting, confirm your method and results with a qualified statistician.
Content Lead, Anchor AI Tools, California, USA. Spotted an error? Email hello@anchoraitools.com and it will be checked and corrected. See our editorial standards.