Type =STDEV.S(A1:A8) into an empty cell after entering your numbers in A1 through A8, and Excel returns the standard deviation in under a second — that part is trivial. The part that actually causes wrong answers, and the reason a spreadsheet full of correct-looking numbers can still mislead whoever reads it, is picking the right function and the right range: STDEV.S for a sample (the case you’re in almost every time), STDEV.P only when your data is a complete population, and neither one blindly if the range has a stray header cell, hidden rows, or numbers stored as text sitting inside it.
This walkthrough uses a real operational scenario — sampled courier delivery times, not a textbook list of test scores — works the arithmetic by hand so you can check Excel’s output against it, and spends real time on the failure modes that produce a silently wrong number: filtered rows that STDEV.S counts anyway, blank cells it correctly skips, and the STDEVA/STDEVPA variants that treat text and FALSE as zero instead of ignoring them.
Standard deviation itself measures how far a typical data point sits from the mean. A small value means the data clusters tightly; a large value means it spreads widely. Excel handles the arithmetic instantly, but you still need to understand what the formula is doing to interpret the output and pick the right function for your data.
Why This Calculation Matters in a Spreadsheet
Spreadsheets are where most analysts, students, and researchers first apply standard deviation to real data. Whether you’re reviewing delivery times, manufacturing tolerances, monthly sales figures, or survey ratings, the standard deviation tells you immediately whether your numbers are consistent or erratic.
Excel is also a natural place to communicate that variability. Charts support error bars that display one standard deviation above and below the mean, giving an audience a visual sense of spread alongside the average. Pivot tables can calculate it across groups. Conditional formatting can flag values that fall beyond a threshold of standard deviations from the mean. In every case, the foundation is getting the underlying number right first.
The NIST/SEMATECH e-Handbook of Statistical Methods, Measures of Scale defines the standard deviation as the square root of the average squared deviation from the mean — also called the root-mean-square deviation — and notes it’s the most widely reported measure of spread because it’s expressed in the same units as the original data, making it directly interpretable. A standard deviation of 5 minutes means a typical delivery is about 5 minutes off the average; a variance of 25 minutes-squared conveys the same information but in squared units that are harder to picture.
Excel’s Standard Deviation Functions, Compared
Excel offers several functions for standard deviation. Two of them cover almost every situation; the rest are legacy aliases or specialised variants for mixed data types.
STDEV.S — for a Sample
STDEV.S is the function to reach for whenever your data represents a sample — a subset of a larger population. It applies Bessel’s correction, dividing the sum of squared deviations by n − 1 rather than n:
s = √( Σ(xᵢ − x̄)² / (n − 1) )
The n − 1 denominator compensates for the fact that a sample tends to underestimate the variability of the full population it was drawn from. Dividing by one less than the sample count produces an unbiased estimator of the population standard deviation.
When to use it: almost every time. Survey responses, test scores, sales data from a subset of your customer base, measurements from a sample of units on a production line — in each case the data is drawn from a broader group, and STDEV.S is the right tool.
Syntax: =STDEV.S(number1, [number2], ...), where number1 can be a range like A1:A100, a list of individual values, or a mix of both.
STDEV.P — for a Complete Population
STDEV.P divides by N (the full count), giving the exact standard deviation of a complete population:
σ = √( Σ(xᵢ − μ)² / N )
When to use it: only when your data is the entire population, every member, with no exceptions. Practical examples include the scores of every student in one specific class if you’ll never generalise beyond it, every unit produced in a closed batch you inspected 100%, or any census-style dataset with no sampling involved.
Syntax: =STDEV.P(number1, [number2], ...) — the same argument structure as STDEV.S.
The Legacy Aliases: STDEV and STDEVP
Excel retains two older function names for backward compatibility:
- STDEV — mathematically identical to
STDEV.S, dividing byn − 1. - STDEVP — mathematically identical to
STDEV.P, dividing byN.
Both still work in current Excel, but Microsoft recommends the .S/.P names in new workbooks because the suffix makes the sample-versus-population choice explicit at a glance. If you inherit a spreadsheet that uses STDEV, treat it exactly as you would STDEV.S — the result is identical.
STDEVA and STDEVPA — When Cells Aren’t Purely Numeric
STDEVA (sample) and STDEVPA (population) differ from their plain counterparts in how they treat non-numeric cells. Where STDEV.S/STDEV.P simply skip a cell containing text or a logical value, STDEVA/STDEVPA convert TRUE to 1 and convert both FALSE and any text to 0, folding them into the calculation instead of excluding them. Blank cells are the exception: every one of these functions — plain or “A” variant — ignores a truly empty cell. The “A” only changes how text and logicals are handled, not how blanks are handled. Because pulling a text cell into the average as a zero can badly distort the result, stick with STDEV.S/STDEV.P unless your range deliberately mixes numbers with TRUE/FALSE values you want counted.
A Worked Example, Step by Step
Suppose a regional courier manager wants to know how consistent one driver’s delivery times are before promising customers a delivery window. She pulls a sample of 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
In a blank worksheet, enter one value per row in column A:
| 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 is correct here — not STDEV.P.
Step 3: Enter the Formula
Click cell B1 and type:
=STDEV.S(A1:A8)
Press Enter. Excel returns ≈ 4.2426.
For comparison, enter in cell B2:
=STDEV.P(A1:A8)
Press Enter. Excel returns ≈ 3.9686.
Verifying the Result by Hand
The two results differ purely because of the denominator. Here’s the full manual calculation:
Mean:
x̄ = (18 + 22 + 19 + 25 + 21 + 30 + 20 + 17) / 8 = 172 / 8 = 21.5
Squared deviations from the mean:
| Value (xᵢ) | Deviation (xᵢ − 21.5) | 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 |
Sample standard deviation (STDEV.S):
s = √(126 / (8 − 1)) = √(126 / 7) = √18 ≈ 4.2426
Population standard deviation (STDEV.P):
σ = √(126 / 8) = √15.75 ≈ 3.9686
Excel’s output matches these hand calculations. The Bessel correction — dividing by n − 1 = 7 instead of n = 8 — pushes the sample estimate slightly higher than the population value. That’s the intended effect: it corrects for the systematic underestimation you’d otherwise get from treating a sample as if it were the whole story. Notice that neither result is a clean round number here — real operational data almost never is, unlike the tidy textbook sets you’ll see in most tutorials.
Try the Standard Deviation Calculator
The calculator below applies the same formula as Excel’s STDEV.S. Enter the delivery times 18, 22, 19, 25, 21, 30, 20, 17 and confirm you see the same results — approximately 4.2426 for sample standard deviation and 3.9686 for population standard deviation.
For more options, open the full standard deviation calculator, or explore every available tool on the statistics calculators hub.
Sample or Population? How to Tell Which Applies
Choosing between STDEV.S and STDEV.P is the single most consequential decision in this whole process, and it’s the one people get wrong without realising it.
Use STDEV.S when:
- Your data is drawn from a larger group — survey respondents, experimental participants, a sample of products.
- You want to estimate the variability of the full population, not just describe the values in front of you.
- You’re not sure which applies —
STDEV.Sis the safer, statistically more defensible default for almost all practical work.
Use STDEV.P when:
- Your data is the complete population, not a sample of it.
- The set is inherently closed — the final grades of every student who completed a specific course in a specific term, or all 200 items in a quality audit where every item was inspected.
- You’re computing a purely descriptive measure of that fixed dataset, with no intention of generalising beyond it.
The OpenStax Introductory Statistics, §2.7 — Measures of the Spread of the Data covers the mathematical relationship between the sample standard deviation s and the population standard deviation σ, and explains why the Bessel correction exists — the same logic that separates STDEV.S from STDEV.P. For the underlying formula and notation in more depth, see the dedicated standard deviation guide rather than re-deriving it here.
A practical rule of thumb: if the dataset came from a measurement process where you could, in principle, collect more data from the same source, use STDEV.S. If the dataset is definitionally complete, use STDEV.P.
Working Faster: Ranges, Named Ranges, and Conditional Formulas
Once the basics are solid, a handful of techniques make these formulas easier to maintain.
Non-Contiguous Ranges
Both functions accept multiple ranges as separate arguments:
=STDEV.S(A1:A8, C1:C5)
Excel treats the two ranges as one combined dataset — useful when your data is split across non-adjacent columns, separate tables, or different sheets.
Named Ranges
If you assign a name to your data range (Formulas → Define Name), you can write:
=STDEV.S(DeliveryTimes)
Named ranges make formulas self-documenting, which helps when you share a workbook or return to it months later. They also update automatically when the source range changes size, if defined as a dynamic named range or an Excel Table.
Conditional Standard Deviation with FILTER
Excel has no built-in STDEVIF function, but you can calculate the standard deviation of a subset that meets a condition. In Excel 365 and Excel 2021, use FILTER:
=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, enter this as an array formula with Ctrl+Shift+Enter:
{=STDEV.S(IF(A2:A100="East", B2:B100))}
The curly braces appear automatically when you press Ctrl+Shift+Enter — don’t type them yourself.
Error Bars: Showing Standard Deviation on a Chart
- Click the chart to select it.
- Click the + (Chart Elements) button that appears to the right.
- Check Error Bars, then select More Options.
- Under Error Amount, choose Standard Deviation and set the multiplier (1 is most common, showing ±1 SD around each point).
Error bars make variability visible alongside the average — a standard convention in scientific and business reporting. They communicate at a glance whether the spread is large or small relative to the values being compared.
Nesting It Inside Larger Formulas
You can use STDEV.S inside other functions. For example, to count values more than one standard deviation above the mean:
=COUNTIF(A1:A100, ">"&(AVERAGE(A1:A100)+STDEV.S(A1:A100)))
This kind of composite formula shows up often in data-quality checks and outlier screening.
When the Result Looks Wrong: Common Causes
Knowing what can go wrong saves hours of troubleshooting when a number looks off.
Using STDEV.P on a Sample
This is the most consequential mistake. STDEV.P divides by N, so it always returns a smaller value than STDEV.S for the same data. If your data is a sample and you use STDEV.P, you’ll underestimate variability — and any confidence interval, control limit, or hypothesis test built on that number will be systematically off. When in doubt, use STDEV.S.
A Header Row, or an Entire Column, in the Range
If you select A1:A101 but A1 holds the label “Minutes” rather than a number, STDEV.S silently ignores the text cell and calculates over only the 100 numeric rows. The result is correct, but the effective n is smaller than you intended. The same thing happens with a whole-column reference like =STDEV.S(A:A) — Excel skips the header text without complaint, so the formula still returns a defensible number, but you lose the visual confirmation of exactly which rows fed the calculation. Verify the range contains only the numeric data you mean to include, or use an Excel Table (Ctrl+T) so headers are automatically excluded from formula references and the range resizes as data is added.
Confusing Blank Cells With Zero Values
STDEV.S ignores blank cells — they’re excluded from the count and contribute nothing to the result. A cell containing 0 is not blank; Excel includes it, and it pulls the result toward zero. If a missing measurement means “no data,” leave the cell empty. If the measurement is genuinely zero, enter 0. Mixing these up inflates or deflates the standard deviation in ways that are hard to trace later.
Numbers Stored as Text
Values imported from another system sometimes arrive formatted as text — they look like numbers but are stored as strings. STDEV.S ignores them, quietly shrinking the effective sample size. If a formula returns a result that seems to be missing values, select the column and check for the small green triangle in the top-left corner of cells, Excel’s text-number warning. Use Data → Text to Columns, or multiply the column by 1, to convert the values to true numbers before calculating.
Hidden or Filtered Rows Are Still Counted
If you filter a table down to one region’s orders, STDEV.S and STDEV.P don’t notice — they read every value in the range, including rows the filter is currently hiding. The same is true for a row hidden manually with Hide Rows. Apply a filter, glance at a STDEV.S cell above the table, and you can end up looking at a number computed from data that isn’t currently on screen.
SUBTOTAL handles this differently, and the distinction is specifically about filters. SUBTOTAL(7, ...) matches STDEV.S, and SUBTOTAL(8, ...) matches STDEV.P, and both automatically exclude rows a filter is hiding — but they still include rows hidden manually. Add 100 to the function number, SUBTOTAL(107, ...) or SUBTOTAL(108, ...), to exclude manually hidden rows as well. AGGREGATE extends this further: alongside the same standard-deviation function numbers, it takes an option argument that lets you choose to ignore hidden rows, ignore error values in the range, ignore both, or ignore neither — handy when a range might contain a stray #DIV/0! or #N/A that would otherwise make STDEV.S return an error for the whole range.
If a workbook relies on filters to segment data and the summary statistic should always reflect what’s currently visible, build it with SUBTOTAL or AGGREGATE rather than STDEV.S.
Very Small Samples
Standard deviation is reliable when the sample is reasonably large. For very small samples — fewer than five or six values — the estimate is highly sensitive to individual observations. A single extreme value can shift it substantially. In practice, always 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 is a rough estimate; the same value from n = 200 is far more trustworthy.
Frequently Asked Questions
What Excel function calculates standard deviation?
The two functions to know are STDEV.S (sample, uses n − 1) and STDEV.P (population, uses N). For almost all practical work, STDEV.S is the correct choice. Older workbooks may use STDEV (equivalent to STDEV.S) or STDEVP (equivalent to STDEV.P).
What is the difference between STDEV.S and STDEV.P?
STDEV.S applies the Bessel correction, dividing the sum of squared deviations by n − 1. STDEV.P divides by N with no correction. For the same dataset, STDEV.S always returns a value equal to or greater than STDEV.P — the gap shrinks as sample size grows. Use STDEV.S for samples; use STDEV.P only when your data is the entire population.
How do I calculate standard deviation across multiple sheets?
Use a 3-D reference to span sheets with the same structure:
=STDEV.S(Sheet1:Sheet3!A1:A10)
Excel treats the matching cell range on every sheet between Sheet1 and Sheet3 as one combined dataset.
Can I calculate standard deviation for data that meets a condition?
Yes. In Excel 365 and Excel 2021, combine STDEV.S with FILTER:
=STDEV.S(FILTER(B2:B100, A2:A100="Group A"))
In older Excel versions, use an array formula: {=STDEV.S(IF(A2:A100="Group A", B2:B100))} entered with Ctrl+Shift+Enter.
What does a standard deviation of zero mean?
A result of zero means every value in the range is identical — there’s no spread at all. That’s mathematically correct but usually signals a data-entry problem (the same value repeated by accident) or a range that resolves to a single distinct entry. Check the source data before concluding the variability is genuinely zero.
Does Excel’s standard deviation match R, Python, and other tools?
STDEV.S matches the sample standard deviation in R (sd()), Python’s NumPy with ddof=1 (np.std(ddof=1)), and SPSS. STDEV.P matches NumPy’s default (np.std() with ddof=0) and R’s population formula. The formulas are identical; only the function names differ across platforms. Once you understand the n versus n − 1 distinction, you can reproduce any result across software without confusion.
Is STDEV still available in current Excel?
Yes. STDEV and STDEVP remain available for backward compatibility in current versions of Excel, including Excel 365. Microsoft recommends STDEV.S and STDEV.P for new workbooks for clarity, but existing workbooks using the legacy names continue to work without modification.
Summary
Calculating standard deviation in Excel comes down to three decisions: enter clean numeric data, choose STDEV.S for a sample or STDEV.P for a full population, and make sure the range holds exactly the data you intend — no header text, no hidden rows you forgot about, no numbers quietly stored as text. The worked example above — 8 delivery times sampled from a single driver’s day — showed the formula operating end to end and confirmed that Excel’s output, ≈4.2426 for the sample and ≈3.9686 for the population, matches the hand-calculated values.
For a deeper look at the notation σ and s, and how each maps to Excel’s two function names, see The Standard Deviation Symbol (Sigma): Signs and Notation. For the mean calculation that standard deviation depends on, see How to Find the Mean (Average): Formula and Steps.
This calculation is one of the most frequently performed in professional spreadsheet work. Once you know which function to reach for, what the result represents, and which traps silently distort it, you can apply it confidently — from a sample of 8 delivery times to a business dataset of tens of thousands of rows.