Excel gives you a standard deviation in one cell: =STDEV.S(range) for a sample, =STDEV.P(range) for a full population. The formula you choose matters more than the keystrokes. Pick the wrong one and your result is smaller than it should be, usually by a margin that looks trivial in a classroom and gets expensive in a quality report. This walkthrough covers both, the manual proof that they agree, and the failure cases that produce wrong answers without an error message.

The Five Standard Deviation Functions Excel Still Ships

Excel keeps five standard deviation functions alive, and two of them exist only for files opened from Excel 2007 or earlier. Microsoft's own documentation flags STDEV, STDEVP, STDEVPA and their relatives as "compatibility" functions that are still available but no longer recommended for new work. The modern pair is STDEV.S and STDEV.P; the trailing letter tells you which denominator the function uses.

The distinction is the denominator, and it is the whole story. STDEV.S divides the squared deviations by n βˆ’ 1, the sample size minus one. STDEV.P divides by n, the full count. That single character changes the answer, and it changes it most when n is small. With 10 values, the sample result runs roughly 5.4% higher than the population result from the identical column.

For a quick decision rule: if your column is every member of the group you care about β€” all 12 months of a closed year, all 40 students in one class, every unit produced in a finished batch β€” use STDEV.P. If the column is a subset used to estimate a wider group β€” 30 units pulled from a day's output, one quarter sampled from a longer run β€” use STDEV.S. The next two sections unpack both paths in detail.

Version support is broader than people assume. STDEV.S and STDEV.P arrived in Excel 2010 and work in Excel 2010 through Microsoft 365 on Windows and Mac, plus Excel for the web and the mobile apps. If you open a workbook in Excel 2007 or earlier, those two functions return #NAME? because the engine does not recognize them. Microsoft's compatibility list exists precisely because those older files still circulate.

Sample Standard Deviation With STDEV.S, Step by Step

Use STDEV.S when the data is a sample. The steps below assume a single column of numeric values in A2 through A11 and an empty cell where you want the answer. Exact names and paths are given so you can follow them on screen.

1: Click the destination cell, for example B2. 2: Type =STDEV.S( and then drag-select A2:A11, or type A2:A11 directly. 3: Close the parenthesis and press Enter. The cell shows the sample standard deviation. 4: Verify the count with =COUNT(A2:A11) in a nearby cell. It should read 10.

That is the whole mechanical procedure. The output for a sample of 10 values is a number in the same units as your data β€” dollars if the column holds dollars, millimeters if it holds measurements.

The formula bar equivalent is =STDEV.S(A2:A11), and Excel's function wizard lists it under Statistical with the description "Estimates standard deviation based on a sample." Ignore the legacy STDEV unless you inherited a file that already uses it; it returns the identical sample result, so there is no numerical penalty, only a consistency one.

Failure handling matters here. If the range holds fewer than two numeric cells, STDEV.S returns #DIV/0! because n βˆ’ 1 equals zero. That is not a bug; it is Excel telling you a single observation cannot estimate spread. Text and blank cells inside the range are ignored, so a column of nine numbers and one "N/A" text entry still computes on nine values, which quietly changes your denominator.

Population Standard Deviation With STDEV.P and the Manual Proof

Use STDEV.P when the column is the complete set. Type =STDEV.P(A2:A11) and press Enter. The same 10 values that produced a sample result now produce a smaller population result, because the denominator is 10 instead of 9.

The manual calculation proves the two agree with Excel and shows where the difference comes from. Take five values: 4, 8, 6, 5, 3. The mean is 5.2. The squared deviations are 1.44, 7.84, 0.64, 0.04, and 4.84, which sum to 14.8. Divide 14.8 by 5 for the population variance, 2.96, and the square root is about 1.7205. Divide 14.8 by 4 for the sample variance, 3.7, and the square root is about 1.9235.

Run both functions on those five cells and Excel returns 1.720465… for STDEV.P and 1.923538… for STDEV.S, matching the hand arithmetic. That is your audit trail. If Excel disagrees with your manual figures, the range reference is wrong or the column contains hidden text.

The n βˆ’ 1 correction is not arbitrary bookkeeping. A sample mean sits closer to its own data points than the true population mean does, so raw sample deviations understate real spread. Dividing by n βˆ’ 1, known as Bessel's correction, inflates the estimate to compensate. The correction shrinks as the sample grows: at n = 100 the gap between the two functions is about 0.5%, at n = 1,000 it is roughly 0.05%. Small samples carry the visible difference.

STDEV.S, STDEV.P, and STDEVPA: Which Function Fits Your Data

The three functions people actually weigh against each other behave differently on the same column. The table uses a 10-value column for the count and a mixed column to show the text-handling split.

Function Denominator Handles text in range Typical use Excel 2007 support
STDEV.S n βˆ’ 1 Ignores text and blanks Sample estimate #NAME? error
STDEV.P n Ignores text and blanks Complete population #NAME? error
STDEVPA n Treats text as 0, TRUE as 1 Legacy full-population work Available
STDEV n βˆ’ 1 Ignores text and blanks Legacy sample files Available
STDEVP n Ignores text and blanks Legacy population files Available

The verdict splits by data type, not by preference. Numeric columns with no text β†’ use STDEV.S or STDEV.P and never look back. Columns that mix labels with numbers and where a label should count as zero β†’ STDEVPA is the only function that will do it, and you should ask why a zero belongs there before trusting the output. Inherited workbooks using STDEV β†’ leave them alone unless you are rebuilding the file, because switching functions mid-stream creates two definitions of spread in one report.

One real drawback of STDEVPA: it silently converts the logical value TRUE to 1 and FALSE to 0, so a column of checkboxes or logical tests that slipped into a numeric range inflates the variance with values you never intended to measure. Verify the column type before committing.

A 12-Month Worked Example You Can Rebuild

Here is a computation built from stated numbers, so you can check every step. Monthly sales in thousands for a closed year: 42, 38, 51, 47, 44, 39, 55, 48, 41, 46, 50, 43. That is 12 values, and because the year is closed, the full set is known β€” population territory.

The mean is 544 divided by 12, which is 45.333. Summing the squared deviations from that mean gives 322.667. Divide by 12 for the population variance, 26.889, and the square root is 5.185. Divide by 11 for the sample variance, 29.333, and the square root is 5.416. Excel returns 5.185… for STDEV.P and 5.416… for STDEV.S on those cells.

Now the decision has a number attached. The difference between the two answers is 0.231 thousand, or about $231 in monthly sales swing on a base near $45,000. For a closed year reported internally, the population figure is correct; quoting the sample figure overstates variability by roughly 4.5%.

The same logic runs through a quality scenario. If 30 units are pulled from a day's production of 4,000 and measured, the 30 form a sample and STDEV.S is the right call, because the day's full output was never measured. If all 4,000 units are measured, STDEV.P applies. The physical act of measuring everything is what promotes a column from sample to population. The function does not know your intent.

Why Your Result Is #DIV/0!, #VALUE!, or Quietly Wrong

Excel's error messages cover most failures, but the dangerous ones arrive without a message. Start with the loud ones. #DIV/0! means fewer than two numeric values reached the function, so n βˆ’ 1 hit zero. #VALUE! means a direct argument β€” not a cell reference β€” contained text the function could not coerce, such as =STDEV.S("abc", 4).

The quiet failures deserve more attention. Numbers stored as text, often pasted from another system or exported from a database, are skipped by STDEV.S and STDEV.P, so the count is lower than the column looks. Verify with =COUNT(range) against =COUNTA(range): if COUNT is smaller, text-format numbers are hiding in the column. Fix them with Data β†’ Text to Columns β†’ Finish, which coerces the column to numeric in place.

Hidden rows inside the range still count. A filter that hides three outliers does not remove them from the calculation, because the function reads the whole range. If you want visible cells only, use =STDEV.S on a helper column or compute with SUBTOTAL, which has its own standard deviation variant, but confirm the behavior on your Excel version because SUBTOTAL's function numbers 1 and 2 correspond to AVERAGE and COUNT, not STDEV.

The third silent failure is the wrong function, which we covered, and the fourth is a range that includes a totals row. A grand total sitting beneath 12 monthly figures enters the calculation as a 13th value and drags the standard deviation upward dramatically. Check the range boundaries on screen before pressing Enter.

Standard Deviation vs. Standard Error in Excel

Standard deviation and standard error are different quantities, and Excel has no built-in standard error function, which surprises people who go looking for one. The standard error of the mean is the standard deviation divided by the square root of n, so for the 12-month example the sample standard deviation of 5.416 divided by the square root of 12 gives 1.563.

Write it as =STDEV.S(A2:A13)/SQRT(COUNT(A2:A13)) and label the cell clearly. The two numbers answer different questions: standard deviation describes how spread out the individual observations are, while standard error describes how precisely the mean itself is pinned down. Reporting one where the other is expected is a common error in summary tables, and the labels often get shortened to "SD" and "SE" with no definition anywhere in the document.

The confusion has a cost in interpretation. A standard error of 1.563 on a mean of 45.333 invites a confidence interval near 45.333 Β± 3.06 for roughly 95% coverage using the 1.96 multiplier, but that approximation assumes a large sample. With n = 12 the correct multiplier comes from the t-distribution, about 2.201 for 11 degrees of freedom, so the interval widens to roughly Β±3.44. Excel's T.INV.2T function supplies that multiplier.

If the report's audience includes non-statisticians, show both figures with one-line definitions rather than choosing silently. Deciding which to show is a judgment call, and the safe default for a methods appendix is to print the standard deviation, the count, and the standard error together so a reader can reconstruct any of the three.

Array Formulas, Dynamic Arrays, and Grouped Standard Deviation

Modern Excel changes the mechanics without changing the math. The dynamic-array engine in Microsoft 365 spills a single result from =STDEV.S(A2:A11) into one cell exactly as older versions did, but related functions like =STDEV.S(FILTER(A2:A100, B2:B100="North")) compute a standard deviation over a filtered subset in one formula, no helper column required. FILTER requires Microsoft 365 or Excel 2021; on older versions it returns #NAME?.

Before dynamic arrays, conditional standard deviation required an array formula entered with Ctrl+Shift+Enter, something like =STDEV.S(IF(B2:B100="North", A2:A100)). That pattern still works in current Excel and is worth recognizing because you will meet it in inherited workbooks. In Microsoft 365 the same formula now enters normally and spills; in Excel 2019 and earlier it needs the special keystroke, and pressing Enter alone returns a wrong single-cell result or an error.

For grouped work, a PivotTable has no standard deviation field by default, but you can add one through the calculated-field path only in limited scenarios; the cleaner route is a helper column or the FILTER approach per group. Confirm the available options on your Excel build, because PivotTable capabilities differ across versions and between Windows and Mac.

Version-specific trap worth naming: Excel for the web supports STDEV.S, STDEV.P and the compatibility functions, and dynamic arrays work there, but some legacy array-formula entry behaviors differ from desktop. If a formula behaves oddly after a browser session, test it in desktop Excel before rewriting it.

Excel vs. a Calculator: Where the Tool Introduces Error

A scientific calculator and Excel compute the same standard deviation from the same numbers, so the tool is rarely the source of a wrong answer. The source is usually the data passed to it: extra rows, text-format numbers, hidden observations, or a totals line. When Excel and your hand calculation disagree, trust the hand calculation until you find the range difference.

Here is a comparison of the realistic failure surface, which the top how-to pages on this keyword rarely build out:

Source of error Shows an error message? Typical symptom Fix
Fewer than 2 numeric cells Yes, #DIV/0! Cell shows error text Extend range or remove blanks
Numbers stored as text No Result too small, count low Text to Columns, Finish
Hidden filtered rows No Outlier still influences result Compute on visible cells deliberately
Totals row in range No Result too large Trim the range
Wrong function (sample vs. population) No Result off by the n βˆ’ 1 ratio Pick by data scope
Direct text argument Yes, #VALUE! Cell shows error text Pass cell references, not strings

The arithmetic check is the defense. Compute the sum of squared deviations by hand on a small subset, divide by the right denominator, take the square root, and compare. On the 12-month example that check takes about three minutes and catches every silent failure above except the function-choice one, which you catch by asking whether the data is complete.

Cost of getting it wrong in a business document: the 12-month example shows a 4.5% overstatement from picking the sample function on complete data. Multiply that through a control chart's limits and the bands widen enough to miss a real out-of-spec signal. The formula is one character; the consequence is a decision.

FAQ

Does Excel have a single standard deviation formula I can always use?

No. STDEV.S and STDEV.P return different numbers on identical data, and the gap is largest for small samples β€” about 5.4% at n = 10 in the worked example above. There is no universal choice. Decide by whether the column is a sample or the full population before you type the function name.

Why does STDEV.S return #DIV/0! when my column clearly has numbers?

The function sees fewer than two numeric values after ignoring text, blanks, and logical values. Run =COUNT(range) beside =COUNTA(range); if COUNT is 0 or 1, the numbers are stored as text or the range is misaligned. Text to Columns with Finish coerces pasted text-numbers back to numeric in place.

Is standard deviation the same as standard error in Excel?

No, and Excel has no standard error function at all. Standard error equals the standard deviation divided by the square root of the count, written as =STDEV.S(range)/SQRT(COUNT(range)). For n = 12 with a sample standard deviation of 5.416, the standard error is 1.563 β€” a measure of the mean's precision, not the data's spread.

Should I use STDEV or STDEV.S in an old workbook?

They return the identical sample result, so leaving STDEV alone in an inherited file changes nothing numerically. Microsoft lists STDEV as a compatibility function no longer recommended for new work, so use STDEV.S in anything you build now and note the choice in the file's documentation.

What happens if my range includes a totals row?

The total enters as an extra value and inflates the result, usually by a wide margin, with no error message. This is the most common silent failure in monthly reports. Select only the observation rows β€” 12 monthly figures, not 13 cells β€” and confirm the range in the formula bar before pressing Enter.

Does filtering rows change the standard deviation?

No. STDEV.S and STDEV.P read the whole range, including rows hidden by a filter. To compute on visible rows only you need a deliberate construction such as a FILTER-based formula in Microsoft 365, and you should verify the result against a manual count because the two approaches answer different questions.

Can I calculate standard deviation for a subset without a helper column?

Yes, in Microsoft 365 and Excel 2021 with =STDEV.S(FILTER(value_range, criteria_range="North")). On Excel 2019 and earlier, FILTER returns #NAME?, so use the array-formula pattern =STDEV.S(IF(criteria_range="North", value_range)) entered with Ctrl+Shift+Enter instead, and check which entry method your version expects.

Why do two people get different standard deviations from the same spreadsheet?

The usual causes are a different function choice, a range that includes one extra cell, or one column containing text-format numbers that the other person's copy had already converted. Compare the COUNT output and the function name in the formula bar on both copies; those two checks resolve nearly every disagreement before you touch the data.