How to Calculate Standard Deviation in Excel

A standard deviation Excel formula almost always comes down to a choice between two functions — STDEV.S for a sample and STDEV.P for a complete population — and getting that choice right matters far more than the arithmetic itself, which Excel finishes in under a second. Calculating standard deviation with Excel is fast once you know which function matches your data: STDEV.S divides by n − 1 and is the right default almost every time, while STDEV.P divides by n and only applies when your range holds every member of the group, with no exceptions. The real risk isn’t the formula — it’s a range that quietly reaches past your data (a whole-column reference picking up stray values below it), a hidden or filtered row the function still counts, or a number stored as text, each of which changes the result without throwing an error.

Key takeaways

Point	Details
Two functions cover nearly every case	STDEV.S applies Bessel’s correction and divides by n − 1 for a sample; STDEV.P divides by n for a complete population.
Picking the wrong one is the costliest mistake	STDEV.P always returns a value at or below STDEV.S on the same data, so using it on sample data systematically understates variability.
Blanks, text, and hidden rows behave differently than expected	STDEV.S and STDEV.P ignore blank and text cells, but they still count rows a filter or a manual Hide Rows command is currently hiding.
STDEVA and STDEVPA treat TRUE and FALSE as numbers	TRUE counts as 1, FALSE counts as 0, and a blank cell is ignored — Microsoft is unambiguous on all three. Text is the exception: Microsoft’s own STDEVA page states two different outcomes for it in the same paragraph.
AGGREGATE needs the right options value to skip hidden rows	SUBTOTAL(107, ...) and SUBTOTAL(108, ...) skip hidden rows automatically, but AGGREGATE’s function numbers 7 and 8 only skip them when the options argument is set to 1, 3, 5, or 7 — leaving it at 0 or omitted ignores nested SUBTOTAL/AGGREGATE calls, not hidden rows.

Quick Checklist: How to Calculate Standard Deviation in Excel

Most wrong results trace back to one of these checks being skipped. Run through this list before trusting any standard deviation by Excel formula.

Quick checklist: standard deviation in Excel

• Decide sample or population first If the data is a subset of a larger group, use STDEV.S; if it is the entire population with no exceptions, use STDEV.P.
• Check whole-column references for stray values below the data A reference like A:A keeps reading past your last row, so a note, a total, or a second table further down the column gets silently folded in.
• Confirm blanks are really blank, not zeros A blank cell is excluded; a 0 is included and pulls the result toward zero.
• Watch for numbers stored as text Values imported from another system sometimes look numeric but get skipped by STDEV.S and STDEV.P.
• Decide whether filtered or hidden rows should count STDEV.S and STDEV.P always include them; use SUBTOTAL(107/108, ...) for an automatic fix, or AGGREGATE with function number 7 or 8 and the options argument set to 1, 3, 5, or 7.

Calculate Standard Deviation in Excel

The calculator below runs the same arithmetic as Excel’s STDEV.S and STDEV.P. Enter the eight delivery times used in the worked example further down this page — 18, 22, 19, 25, 21, 30, 20, 17 — to confirm you get the same two results Excel returns.

Calculator

Standard Deviation Calculator

Values

Compute

Enter values and compute the result.

Which Excel Function Should You Use: STDEV.S or STDEV.P?

Use STDEV.S whenever your data is a sample drawn from a larger group, and reserve STDEV.P for the rare case where your range holds every member of a closed, complete population. The NIST/SEMATECH e-Handbook of Statistical Methods defines the standard deviation as “the square root of the variance” and notes that it “restores the units of the spread to the original data units,” unlike the variance, which squares them — a result of 5 minutes means a typical value sits about 5 minutes from the mean, while the variance would report 25 minutes-squared for the same spread. On paper this is the population standard deviation σ or sample standard deviation s; see the standard deviation symbol guide for how that notation maps onto Excel’s two function names.

Per Microsoft’s documentation for STDEV.S, the function “assumes that its arguments are a sample of the population” and computes the standard deviation “using the n-1 method.” STDEV.P’s documentation states the opposite assumption — that the arguments represent the entire population — and divides by n instead. Both functions accept the same argument shapes: a range like A1:A100, a list of individual values, or a mix of both, via =STDEV.S(number1, [number2], ...) or =STDEV.P(number1, [number2], ...).

The n − 1 denominator exists because a sample systematically underestimates the spread of the full population it was drawn from; dividing by one less than the count corrects that bias. OpenStax’s Introductory Statistics, §2.7 walks through this same relationship between the sample standard deviation s and the population standard deviation σ in more formal terms. Excel is not the only tool built around this distinction: R’s sd() function always “uses denominator n - 1,” matching STDEV.S, while NumPy’s std() function defaults to ddof=0, matching STDEV.P. For the full derivation of why the correction works, see the dedicated standard deviation guide — this page focuses on applying the two functions correctly inside a spreadsheet, not re-deriving the formula.

Microsoft retains STDEV and STDEVP as legacy names: STDEV is mathematically identical to STDEV.S, and STDEVP is identical to STDEV.P. STDEVP’s own documentation confirms it “assumes that its arguments are the entire population.” Both older names still work in current Excel, including Excel 365, but Microsoft recommends the .S/.P names in new workbooks because the suffix makes the sample-versus-population choice explicit at a glance.

How Do STDEVA and STDEVPA Handle Non-Numeric Cells?

STDEVA (sample) and STDEVPA (population) exist for the specific case where a range deliberately mixes numbers with TRUE/FALSE values you want included in the calculation. Microsoft’s documentation for STDEVA is unambiguous on two of the three non-numeric cases: a TRUE inside a cell reference counts as 1, and a FALSE inside that same reference counts as 0 — the remarks state directly that “arguments that contain TRUE evaluate as 1; arguments that contain text or FALSE evaluate as 0.” It’s just as clear that a genuinely blank cell in that reference is ignored rather than counted as 0. Text is the one case the documentation does not settle: the same remarks section goes on to say that “empty cells and text values in the array or reference are ignored” — which flatly contradicts the “text… evaluate as 0” it states earlier in that same paragraph. Rather than pick a side Microsoft’s own page leaves open, treat a text value inside a STDEVA/STDEVPA reference as unresolved and test it directly against your own data before relying on the result. STDEV.S and STDEV.P, by contrast, are unambiguous: they exclude blanks, text, and logical values alike from a reference.

Because folding a FALSE into the calculation as a zero can pull a result toward zero when you didn’t mean to include that cell at all, stick with STDEV.S/STDEV.P unless your range genuinely needs TRUE/FALSE counted as numbers. If you need a dispersion measure that’s less sensitive to how individual cells are coded in the first place, the mean absolute deviation guide covers an alternative that avoids squaring distances from the mean entirely.

How Do You Calculate Standard Deviation for a Subset, Multiple Ranges, or Across Sheets in Excel?

STDEV.S and STDEV.P aren’t limited to one contiguous range: pass several ranges as separate arguments, wrap a condition in FILTER to restrict the calculation to a subset, or write a 3-D reference to span matching cells across several sheets. Each approach suits a different layout, covered one at a time below.

Non-contiguous ranges. Both STDEV.S and STDEV.P accept more than one range as separate arguments:

=STDEV.S(A1:A8, C1:C5)

Excel treats the two ranges as one combined data set — useful when your values are split across non-adjacent columns or separate tables.

Named ranges. Assigning a name to a range (Formulas → Define Name) lets you write a self-documenting formula:

=STDEV.S(DeliveryTimes)

A named range defined as an Excel Table or a dynamic named range also resizes automatically as rows are added, so the formula keeps covering the full data set without manual edits.

Conditional subsets. Excel has no dedicated STDEVIF function, but in Excel 365 and Excel 2021 you can combine STDEV.S with the FILTER function, which “filters an array based on a Boolean (True/False) array”:

=STDEV.S(FILTER(B2:B100, A2:A100="East"))

This computes the standard deviation of only the rows where column A equals “East.” In older Excel versions without FILTER, the same result comes from an array formula entered with Ctrl+Shift+Enter:

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

Excel adds the surrounding braces automatically when you press Ctrl+Shift+Enter — type the formula itself, not the braces.

Across multiple sheets. A 3-D reference spans every sheet between two tab names:

=STDEV.S(Sheet1:Sheet3!A1:A10)

Excel treats the matching cell range on every sheet from Sheet1 through Sheet3 as one combined data set, which only works cleanly when every sheet shares the same layout.

How Do You Add Standard Deviation Error Bars to an Excel Chart?

Select the chart, open Chart Elements, check Error Bars, then pick Standard Deviation — but know what that option actually draws first: it computes a single standard deviation from every plotted point across the whole series and applies that one value to every point uniformly, rather than a separate, per-point standard deviation drawn from each point’s own underlying data. Microsoft’s documentation on error bars spells out the equation it uses: the Standard Deviation option is computed from “M = arithmetic mean” and “ny = total number of data values in all series,” so every error bar on the chart ends up the same length by construction.

1. Click the chart to select it.
2. Click the + (Chart Elements) button that appears to the right of the chart.
3. Check Error Bars, then select More Options.
4. Under Error Amount, pick Standard Deviation for the single, chart-wide value described above, and set the multiplier — 1 is the most common choice.

If you actually need a different standard deviation bar on each point — say, one STDEV.S per group in a bar chart — the built-in option can’t do that. Instead, choose Custom, then Specify Value, and point the Positive and Negative Error Value boxes at a range of cells holding a separate STDEV.S result for each point; Microsoft’s documentation confirms “you can also define error values as a range of cells from the same Excel workbook.” That route gives every bar its own, genuinely computed standard deviation instead of one shared value borrowed from the whole series.

Either way, error bars let a reader judge at a glance whether the spread is large or small relative to the values being compared, without reading the underlying numbers.

How Do You Use STDEV.S Inside Other Excel Formulas?

STDEV.S and STDEV.P nest cleanly inside larger formulas, which is where standard deviation earns its keep in data-quality checks and outlier screening. To count how many values sit more than one standard deviation above the mean:

=COUNTIF(A1:A8, ">"&(AVERAGE(A1:A8)+STDEV.S(A1:A8)))

On the eight delivery times used in the worked example below, AVERAGE returns 21.5 and STDEV.S returns about 4.2426, so the threshold is roughly 25.74 minutes. Exactly one value — 30 minutes — exceeds it, so this formula returns 1.

Excel’s STANDARDIZE function turns the same standard deviation into a z-score directly, “a normalized value from a distribution characterized by mean and standard_dev”:

=STANDARDIZE(30, 21.5, 4.2426)

This returns approximately 2.00 — the 30-minute delivery sits about two sample standard deviations above the mean, which is exactly the kind of flag a data-quality check or outlier screen is built to catch.

A Worked Example: Courier Delivery Times

A regional courier manager wants to know how consistent one driver’s delivery times are before promising customers a delivery window. She pulls 8 delivery times, in minutes, from Tuesday’s route sheet — out of roughly 40 stops the driver made that day, so this is clearly a sample, not the full population of deliveries.

Step 1: Enter the data

Cell	Delivery time (min)
A1	18
A2	22
A3	19
A4	25
A5	21
A6	30
A7	20
A8	17

Step 2: Choose the right function

These 8 times are a sample drawn from a larger day of deliveries, so STDEV.S applies here — not STDEV.P.

Step 3: Enter the formulas and verify by hand

=STDEV.S(A1:A8)   → 4.2426
=STDEV.P(A1:A8)   → 3.9686

Mean:

x̄ = (18 + 22 + 19 + 25 + 21 + 30 + 20 + 17) / 8 = 172 / 8 = 21.5

Squared deviations from the mean:

Value	Deviation	Squared deviation
18	−3.5	12.25
22	0.5	0.25
19	−2.5	6.25
25	3.5	12.25
21	−0.5	0.25
30	8.5	72.25
20	−1.5	2.25
17	−4.5	20.25
Sum		126

STDEV.S: s = √(126 / (8 − 1)) = √(126 / 7) = √18 ≈ 4.2426
STDEV.P: σ = √(126 / 8) = √15.75 ≈ 3.9686

Excel’s output matches the hand calculation exactly. The Bessel correction — dividing by n − 1 = 7 instead of n = 8 — pushes the sample estimate above the population value, which is the intended effect of correcting for a sample’s tendency to underestimate spread.

Decision. The manager should report the sample standard deviation — about 4.24 minutes — when estimating how consistent this driver’s deliveries are likely to be going forward, since 8 stops out of roughly 40 is a sample, not the whole day. Paste these same eight values into the calculator above to confirm both results without redoing the arithmetic by hand.

What Causes a Wrong Standard Deviation Result in Excel?

Knowing what can go wrong saves hours of troubleshooting when a number looks off.

• Using STDEV.P on sample data. This is the costliest mistake, because STDEV.P always returns a value at or below STDEV.S on the same numbers. Treating a sample as if it were the whole population understates variability, and any confidence interval or control limit built on that understated number inherits the error.
• A whole-column reference picking up values you didn’t mean to include. A text header like “Minutes” in A1 isn’t actually the risk — STDEV.S and STDEV.P ignore text in a reference and calculate over exactly the numeric rows you intended. The real risk is a reference like =STDEV.S(A:A): it keeps reading all the way down the column, so a note typed below the data, a running total, or the start of a second table further down the sheet gets silently folded into the result. An Excel Table (Ctrl+T) resizes its own references automatically, which avoids this without you having to track the last row by hand.
• Confusing a blank cell with a zero. STDEV.S and STDEV.P exclude a genuinely blank cell from both the count and the result; a cell containing 0 is included and pulls the result toward zero. If a missing measurement means “no data,” leave it empty — entering 0 changes the answer.
• Numbers stored as text. Values imported from another system sometimes arrive as text that merely looks numeric. STDEV.S and STDEV.P ignore them, quietly shrinking the effective sample size. The small green triangle in a cell’s top-left corner is Excel’s warning sign; Data → Text to Columns, or multiplying the column by 1, converts the values to true numbers.
• Hidden or filtered rows that still get counted. Filtering a table down to one region’s orders doesn’t stop STDEV.S or STDEV.P from reading every row in the range, including the ones currently hidden by the filter. The same applies to a row hidden manually with Hide Rows. SUBTOTAL(7, ...) matches STDEV.S and SUBTOTAL(8, ...) matches STDEV.P; add 100 (SUBTOTAL(107, ...) or SUBTOTAL(108, ...)) to also exclude manually hidden rows. AGGREGATE’s documentation uses the same two function numbers — 7 for STDEV.S, 8 for STDEV.P — but the function number by itself does not skip hidden rows. AGGREGATE’s options argument has to be set to 1, 3, 5, or 7 to ignore hidden rows, for example =AGGREGATE(7, 5, A1:A100). Leave options at 0 or omit it, as in =AGGREGATE(7, 0, A1:A100), and the function only ignores nested SUBTOTAL/AGGREGATE calls — every hidden row in the range still gets counted.
• Treating a tiny sample’s result as precise. With fewer than five or six values, a single extreme observation can shift the standard deviation substantially. Report the sample size n alongside the standard deviation so a reader can judge how stable the estimate is — a standard deviation of 3.5 from n = 4 carries far less weight than the same value from n = 200.

1-11 includes manually-hidden rows, while 101-111 excludes them; filtered-out cells are always excluded.

SUBTOTAL function, Microsoft Support

Statohub’s Take on Standard Deviation in Excel

Statohub’s position is that the function choice, not the arithmetic, is where this calculation actually goes wrong in practice. Spend the extra few seconds confirming sample versus population and scanning the range for a stray value past the data, a blank-versus-zero mixup, or a hidden row before trusting the number — Excel will compute a confident-looking result from flawed inputs without ever flagging the problem. When a quick, disposable first pass is all you need, the range is faster to check by eye; reach for the standard deviation once that first look says the data is worth a more careful read.

Put Standard Deviation in Excel to Work With Statohub’s Tools

The calculator above applies the exact STDEV.S/STDEV.P arithmetic covered throughout this guide — for a standalone version, open the full standard deviation calculator. For the underlying formula and notation this page assumes you already know, see the standard deviation guide, and for the closely related measure expressed in squared units, see the variance guide. The Descriptive Statistics hub walks through mean, median, range, variance, and standard deviation side by side, and the calculators hub lists every tool statohub publishes.

Sources

Sources

1. NIST/SEMATECH e-Handbook of Statistical Methods — 1.3.5.6. Measures of Scale NIST
2. OpenStax — 2.7 Measures of the Spread of the Data, Introductory Statistics OpenStax
3. Microsoft Support — STDEV.S function Microsoft
4. Microsoft Support — STDEV.P function Microsoft
5. Microsoft Support — STDEVA function Microsoft
6. Microsoft Support — STDEVP function Microsoft
7. Microsoft Support — FILTER function Microsoft
8. Microsoft Support — SUBTOTAL function Microsoft
9. Microsoft Support — AGGREGATE function Microsoft
10. Microsoft Support — STANDARDIZE function Microsoft
11. Microsoft Support — Add, change, or remove error bars in a chart Microsoft
12. NumPy — numpy.std NumPy
13. R Documentation — sd (Standard Deviation) The R Project

FAQ

Frequently asked questions

What Excel Function Calculates Standard Deviation?

The two functions to know are STDEV.S (sample, divides by n − 1) and STDEV.P (population, divides by n). For almost all practical work, the excel function standard deviation calls for is STDEV.S. Older workbooks may instead use STDEV (equivalent to STDEV.S) or STDEVP (equivalent to STDEV.P) — both still work in current Excel.

What Is the Difference Between STDEV.S and STDEV.P?

STDEV.S applies Bessel’s correction, dividing the sum of squared deviations by n − 1. STDEV.P divides by n with no correction. On the same data set, STDEV.S always returns a value equal to or greater than STDEV.P, and the gap between them shrinks as the sample size grows. Use STDEV.S for a sample and STDEV.P only when the data is the full population.

Does Excel Standard Deviation Match R, Python, and Other Software?

STDEV.S matches R’s sd() function, which its documentation says always "uses denominator n - 1," and matches Python’s NumPy with ddof=1 (np.std(ddof=1)). STDEV.P matches NumPy’s default (np.std() with ddof=0). R has no separate built-in population-standard-deviation function; to reproduce STDEV.P’s result in R, multiply sd(x) by sqrt((n-1)/n). The underlying arithmetic is identical across every tool — only the denominator each one defaults to, and whether a population version ships built in, differs.

What Does a Standard Deviation of Zero Mean in Excel?

A result of zero means every value in the range is identical, so there is no spread at all. That is mathematically correct, but it usually signals a data-entry problem — the same value repeated by accident — or a range that has collapsed to a single distinct entry. Check the source data before concluding the variability is genuinely zero.

Is STDEV Still Available in Current Excel?

Yes. STDEV and STDEVP remain available in current Excel, including Excel 365, for backward compatibility with older workbooks. Microsoft recommends STDEV.S and STDEV.P for new work because the suffix makes the sample-versus-population choice explicit, but an existing workbook using the legacy names continues to calculate correctly without any changes.

Why Does My Standard Deviation by Excel Formula Ignore Some of My Rows?

STDEV.S and STDEV.P skip blank cells, text, and logical values sitting inside a range reference — a text header is ignored correctly, but a text-formatted number you meant to include gets silently dropped too, shrinking the effective count without an error appearing. They do not, however, skip rows a filter or a manual Hide Rows command is currently hiding — use SUBTOTAL or AGGREGATE instead if those rows should be excluded too.

Recommended

• The Standard Deviation Symbol (Sigma): Signs & Notation
• How to Find the Mean (Average): Formula & Steps
• How to Find the Range in Statistics
• Mean Absolute Deviation Explained