The fastest way to calculate standard deviation in Excel is to enter your data in a column and type =STDEV.S(A1:A8) into any empty cell. Excel computes the result instantly — no manual squaring or summing required. Knowing which Excel function to choose, and why, is the real skill: STDEV.S is for a sample (the usual case), while STDEV.P is reserved for a complete population. This guide covers both, walks through a fully worked numeric example, and explains the most common mistakes people make when using standard deviation with Excel.
Standard deviation 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 makes this calculation quick, but you still need to understand the underlying formula to interpret the output and choose the right function.
Why Standard Deviation Matters in Excel
Spreadsheets are where most analysts, students, and researchers first apply standard deviation to real data. Whether you are reviewing exam scores, manufacturing tolerances, monthly sales figures, or survey ratings, understanding standard deviation for Excel data tells you immediately whether your numbers are consistent or erratic.
Excel is also a natural environment for communicating variability. Charts support error bars that display one standard deviation above and below the mean, giving audiences a visual sense of spread alongside the average. Pivot tables can calculate standard deviation across groups. Conditional formatting can highlight values that fall beyond a threshold of standard deviations from the mean. In each case, the foundation is knowing how to calculate the right number in the first place.
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 that it is the most widely reported measure of spread because it is expressed in the same units as the original data, making it directly interpretable. A standard deviation of 5 kilograms means that a typical observation is about 5 kilograms away from the mean; a variance of 25 kilograms-squared conveys the same information but in squared units that are harder to picture.
Understanding this definition is what lets you evaluate whether Excel’s number makes sense for your dataset.
Excel’s Standard Deviation Functions Explained
Excel offers several functions for standard deviation. The two you need to know are STDEV.S and STDEV.P. The others are either legacy versions or specialised variants for uncommon data formats.
STDEV.S — The Sample Standard Deviation Function
STDEV.S is the primary excel function standard deviation option for data that 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. By dividing by one less than the sample count, STDEV.S produces an unbiased estimator of the population standard deviation.
When to use STDEV.S: Use it almost every time. Survey responses, test scores, sales data from a subset of your customer base, measurements taken from a sample of products on a production line — in all of these cases the data is a sample drawn from a broader group. 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.
STDEV.P — The Population Standard Deviation Function
STDEV.P divides by N (the full count), giving the exact standard deviation for a complete population:
σ = √( Σ(xᵢ − μ)² / N )
When to use STDEV.P: Only when your data is the entire population — every member with no exceptions. Practical examples include the scores of every student in a single specific class (if you will never generalise beyond that class), all units produced in a closed batch that you measured 100%, or any census-style data where no sampling occurred.
Syntax: =STDEV.P(number1, [number2], ...) — identical argument structure to STDEV.S.
Legacy Functions: STDEV and STDEVP
Excel retains two older functions for backward compatibility:
- STDEV — mathematically identical to
STDEV.S, usingn − 1in the denominator. - STDEVP — mathematically identical to
STDEV.P, usingN.
Both still work in every version of Excel, but Microsoft recommends using the .S and .P versions in new workbooks because the suffixes make the sample-versus-population distinction explicit at a glance. If you open a spreadsheet that uses STDEV, treat it as STDEV.S — the results are the same.
STDEVA and STDEVPA
Excel also offers STDEVA (sample) and STDEVPA (population) variants. These differ in how they handle non-numeric cells: STDEVA and STDEVPA convert TRUE to 1, FALSE to 0, and text to 0, rather than ignoring those cells entirely. For typical numeric data you should never need them. Stick with STDEV.S or STDEV.P unless your range deliberately mixes numbers with logical values.
How to Calculate Standard Deviation in Excel: Step-by-Step
The following worked example uses the dataset 2, 4, 4, 4, 5, 5, 7, 9 — eight values entered in cells A1 through A8. This is a classic reference dataset used across introductory statistics textbooks, so you can verify your Excel result against well-known answers.
Step 1: Enter the Data
In a blank Excel worksheet, type one value per row in column A:
| Cell | Value |
|---|---|
| A1 | 2 |
| A2 | 4 |
| A3 | 4 |
| A4 | 4 |
| A5 | 5 |
| A6 | 5 |
| A7 | 7 |
| A8 | 9 |
Step 2: Choose the Right Function
These eight values are treated as a sample, not a complete population, so use STDEV.S.
Step 3: Enter the Formula
Click cell B1 and type:
=STDEV.S(A1:A8)
Press Enter. Excel returns ≈ 2.1381.
For comparison, enter in cell B2:
=STDEV.P(A1:A8)
Press Enter. Excel returns 2.0000 exactly.
Verifying the Result by Hand
The two results differ because of the denominator. Here is the complete manual calculation:
Mean:
x̄ = (2 + 4 + 4 + 4 + 5 + 5 + 7 + 9) / 8 = 40 / 8 = 5
Squared deviations from the mean:
| Value (xᵢ) | Deviation (xᵢ − 5) | Squared deviation |
|---|---|---|
| 2 | −3 | 9 |
| 4 | −1 | 1 |
| 4 | −1 | 1 |
| 4 | −1 | 1 |
| 5 | 0 | 0 |
| 5 | 0 | 0 |
| 7 | +2 | 4 |
| 9 | +4 | 16 |
| Sum | 32 |
Sample standard deviation (STDEV.S):
s = √(32 / (8 − 1)) = √(32 / 7) = √4.5714 ≈ 2.1381
Population standard deviation (STDEV.P):
σ = √(32 / 8) = √4 = 2.0000
Excel’s results match these hand calculations exactly. The Bessel correction — dividing by n − 1 = 7 rather than n = 8 — makes the sample estimate slightly larger than the population value, which is the intended behaviour: it guards against the systematic underestimation that would occur if you divided by the raw count.
Try the Standard Deviation Calculator
The calculator below applies the same formula as Excel’s STDEV.S. Enter the values 2, 4, 4, 4, 5, 5, 7, 9 and confirm you see the same results — approximately 2.1381 for sample standard deviation and 2.0000 for population standard deviation.
For more options, open the full standard deviation calculator, or explore every available tool on the statistics calculators hub.
Understanding Sample vs. Population Standard Deviation in Excel
The choice between STDEV.S and STDEV.P is the single most important decision when working with standard deviation and Excel, yet many users pick the wrong function 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 what the variability looks like across the full population, not just the values you have.
- You are unsure which to use — STDEV.S is the safer and statistically more appropriate default for almost all practical work.
Use STDEV.P when:
- Your data is the complete population, not a sample.
- The set is inherently closed — for example, the final grades of every student who completed a specific course during a specific semester, or all 200 items in a quality audit where you inspected every item.
- You are computing a purely descriptive measure of that fixed dataset with no intention of generalising to any broader group.
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 σ, explaining why the Bessel correction exists and when each formula is appropriate — the same logic that separates STDEV.S from STDEV.P.
A practical rule of thumb: if your dataset came from a measurement process where you could, in principle, collect more data from the same population, use STDEV.S. If the dataset is definitionally complete, use STDEV.P.
Standard Deviation in Excel: Additional Techniques
Once you know the basics, several techniques help you work more efficiently with excel standard deviation formulas.
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. This is useful when your data is split across non-adjacent columns, separate tables, or even different sheets.
Named Ranges
If you assign a name to your data range (via Formulas → Define Name), you can write:
=STDEV.S(TestScores)
Named ranges make formulas self-documenting, which helps when you share workbooks or return to them months later. They also update automatically when the source range changes in size (if defined as a dynamic named range or a Table).
Conditional Standard Deviation with FILTER
Excel does not have a built-in STDEVIF function, but you can calculate the standard deviation of a subset of values that meet a condition. In Excel 365 and Excel 2021, use FILTER:
=STDEV.S(FILTER(B2:B100, A2:A100="East"))
This computes the standard deviation by Excel for only the rows where column A equals “East”. In older versions of Excel, 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 use Ctrl+Shift+Enter — do not type them manually.
Adding Standard Deviation Error Bars to Charts
Excel lets you display standard deviation visually in charts:
- 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 the most common, showing ±1 SD from each data point’s mean).
Error bars make variability visible alongside the average, a standard representation in scientific and business reporting. They communicate at a glance whether the spread is large or small relative to the values being compared.
Using Standard Deviation in Larger Formulas
You can nest STDEV.S inside other Excel 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 is common in data-quality checks and outlier screening.
Common Mistakes When Calculating Standard Deviation in Excel
Knowing what can go wrong prevents hours of troubleshooting when a result looks unexpected.
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 will underestimate variability — and any confidence intervals, control limits, or hypothesis tests built on that number will be systematically wrong. When in doubt, use STDEV.S.
Including the Header Row in the Range
If you select A1:A101 but A1 contains the label “Score” 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. Always verify that your selected range contains only numeric data, or use an Excel Table (Ctrl+T) so column headers are automatically excluded from any formula references.
Confusing Blank Cells with Zero Values
STDEV.S ignores blank cells — they are excluded from the count and contribute nothing to the calculation. 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 diagnose later.
Text-Formatted Numbers
Numbers imported from external systems sometimes arrive formatted as text — they look like numbers but are stored as strings. STDEV.S ignores them, quietly reducing the effective sample size. If your formula returns a result that corresponds to fewer values than you expect, select the column and check whether Excel shows a small green triangle in the top-left corner of cells (the text-number warning). Use Data → Text to Columns, or multiply the column by 1, to convert the values to true numbers before calculating.
Very Small Samples
Standard deviation by Excel 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 the standard deviation 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 very rough estimate; the same value from n = 200 is much more reliable.
Frequently Asked Questions
What Excel function calculates standard deviation?
The two main excel function standard deviation options 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 without 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 in Excel 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 Excel 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 in Excel?
A result of zero means every value in the range is identical — there is no spread at all. This is mathematically correct but usually signals a data-entry problem (the same value accidentally repeated) or a range that includes only one distinct entry. Check the source data before concluding the variability is truly zero.
Does Excel 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 the latest version of Excel?
Yes. STDEV and STDEVP remain available for backward compatibility in all current versions of Excel, including Excel 365. Microsoft recommends using STDEV.S and STDEV.P in new workbooks for clarity, but existing workbooks using the legacy names will continue to work without modification.
Summary
Calculating standard deviation in Excel reduces to three steps: enter clean numeric data, choose STDEV.S for a sample or STDEV.P for a full population, and select the correct range. The worked example above — using 2, 4, 4, 4, 5, 5, 7, 9 — shows exactly how the formula operates and confirms that Excel’s output (≈ 2.1381 for sample, 2.000 for population) matches the hand-calculated values.
For a deeper understanding of what the notation σ and s mean, and how they map 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.
Standard deviation with Excel is one of the most frequently performed statistical calculations in professional data work. Once you know which function to reach for and what the result represents, you can apply it confidently to spreadsheets of any size — from a classroom dataset of eight values to a business dataset of tens of thousands.