The standard deviation is one of the most useful single numbers a spreadsheet can produce. It measures how spread out a set of values is around their average — a small standard deviation means the values are clustered tightly near the mean, a large one means they are scattered widely.
Excel ships with several functions that calculate it, but the workhorse pair are STDEV.S and STDEV.P. STDEV.S handles sample data, dividing by n minus one in the underlying formula. STDEV.P handles population data, dividing by n. Knowing which one to call is the single most important decision when working with standard deviation in Excel.
This guide walks through every reliable approach to calculating standard deviation in Excel — the modern STDEV.S and STDEV.P functions, the older STDEV and STDEVP that remain for backwards compatibility, the STDEVA and STDEVPA variants that handle text and logicals, and the practical use cases where the answer matters. The aim is to turn the standard deviation from a half-remembered statistics class formula into a tool you can confidently apply to quality control reports, grade analyses, financial returns, A/B test outcomes and any dataset where understanding spread matters as much as understanding the average.
Standard deviation has a long history in mathematics and statistics, but its widespread use in business spreadsheets is a relatively recent phenomenon. Modern Excel makes it accessible enough that anyone with a few hours of training can compute and interpret it. The cost is that many users apply the formula without understanding the underlying assumptions, which can produce misleading conclusions when the data is heavily skewed, contains significant outliers or violates the basic normality assumption that the 68-95-99.7 rule depends on.
Sample standard deviation: =STDEV.S(range) — divides by n minus one. Population standard deviation: =STDEV.P(range) — divides by n. Use sample when your data is a subset; use population when you have every data point. Older equivalents STDEV and STDEVP are still supported. Standard deviation has the same units as the data. Pair with AVERAGE for the spread-around-mean interpretation. The 68-95-99.7 rule maps standard deviation onto normal-distribution intuition.
The decision between STDEV.S and STDEV.P comes down to whether your data represents a sample drawn from a larger population or the entire population itself. STDEV.S — the sample form — divides by n minus one rather than n. The reason is statistical: when you only have a sample, dividing by n minus one corrects for the slight underestimation that would otherwise occur, producing an unbiased estimate of the underlying population's standard deviation. This adjustment is called Bessel's correction.
STDEV.P assumes you have every data point in the population. Dividing by n produces the exact standard deviation of the data you have. Use STDEV.P when your dataset literally contains every observation — every employee in a department, every transaction in a month, every test result from a single batch.
Use STDEV.S when your data is a sample from a larger universe — a survey of 200 customers from a customer base of 50,000, or this week's call durations as a sample of all call durations across the year. The numerical difference is small for large datasets and meaningful for small ones, but the statistical interpretation is different in either case.
One useful intuition for the difference comes from the divisor itself. Dividing the sum of squared deviations by n gives you the average squared deviation. Dividing by n minus one gives you a slightly larger number, which corrects for the fact that you computed the deviations from a sample mean rather than the true population mean. The sample mean is mathematically guaranteed to minimise the squared deviations within the sample, which slightly understates the true variability. Bessel's correction undoes this systematic understatement.
Sample standard deviation. Divides by n-1 (Bessel correction). Use when your data is a sample drawn from a larger population. Replaces the older STDEV function. Most common choice in business and research analysis.
Population standard deviation. Divides by n. Use when you have every data point in the population. Replaces older STDEVP. Useful for closed datasets like all sales in a quarter or all employees in a department.
Original sample standard deviation function. Identical to STDEV.S in result. Excel keeps it for backwards compatibility with older workbooks. New work should use STDEV.S to match Microsoft's modern function naming.
Original population standard deviation function. Identical to STDEV.P. Backwards compatibility only. Use STDEV.P in new workbooks for clarity and forward compatibility with future Excel updates.
Sample standard deviation that includes text and logical values in the calculation. Text counts as zero, TRUE counts as 1, FALSE counts as 0. Rarely the right answer because mixing types usually distorts the underlying statistic.
Population variant of STDEVA. Same text and logical handling as STDEVA but population-style division by n. Like STDEVA, rarely the right choice — almost always better to clean the data first and use STDEV.S or STDEV.P.
Standard deviation has the same units as the underlying data. If your data is daily sales in dollars, the standard deviation is also in dollars and tells you how many dollars the typical day deviates from the average day. If your data is grades on a 100-point scale, the standard deviation is in points. This unit equivalence makes standard deviation directly interpretable, unlike variance (which has the squared units of the original data). Pair the standard deviation with the mean to communicate both centre and spread in a single sentence: "daily sales average $12,400 with a standard deviation of $1,800".
The 68-95-99.7 rule is the easiest way to translate standard deviation into intuitive distance language. For data that follows a roughly normal distribution, about 68 percent of values fall within one standard deviation of the mean, about 95 percent fall within two, and about 99.7 percent fall within three. This rule of thumb lets you say something concrete like "daily sales are normally between $10,600 and $14,200" simply by adding and subtracting one standard deviation from the mean. The rule fails when data is heavily skewed or has outliers, but it is a useful first approximation.
An interesting consequence of the 68-95-99.7 rule is that values more than three standard deviations from the mean are extremely rare in normal data. About one in 370 observations would lie beyond plus three standard deviations on a strictly normal distribution. This is why control chart limits are typically set at plus or minus three standard deviations — points outside indicate something unusual that warrants investigation rather than a routine fluctuation. The same threshold underlies many outlier detection rules in data analysis software.
=STDEV.S(A2:A100) returns the sample standard deviation of the values in A2 through A100. The most common form in business analysis. Empty cells are skipped; text cells produce a #VALUE! error. Use this when your data represents a sample of a larger population.
=STDEV.P(A2:A100) returns the population standard deviation. Use when the range contains every observation in your dataset. The result will be slightly smaller than STDEV.S on the same data because population division uses n rather than n-1.
Excel does not have a built-in STDEVIF, but you can use array formulas or filtered ranges. =STDEV.S(IF(B2:B100="East",A2:A100)) entered as an array formula returns the sample standard deviation of A values where B equals "East". In Microsoft 365, dynamic array support handles this without explicit array entry.
=STDEV.S(A2:A100,C2:C100,E2:E100) calculates standard deviation across multiple non-adjacent ranges. The function flattens all numeric values from the listed ranges into a single dataset for the calculation. Useful when data is split across several columns or sheets.
Pair STDEV.S with AVERAGE to produce the standard descriptive statistics summary. =AVERAGE(A2:A100) gives the mean; =STDEV.S(A2:A100) gives the spread. Display them together in a small summary block so readers can interpret both measures simultaneously rather than separately.
Z-score = (value - mean) / standard deviation. =(A2-AVERAGE($A$2:$A$100))/STDEV.S($A$2:$A$100) returns how many standard deviations a specific value is from the mean. Useful for outlier detection — values with absolute z-scores above 2 or 3 are commonly flagged as unusual.
Quality control is one of the most common applications of standard deviation in business. Manufacturing processes use control charts that plot measurements against the process mean and the upper and lower control limits set at the mean plus and minus three standard deviations. Points outside the limits suggest the process has drifted out of control. Six Sigma methodology takes this idea further, targeting processes whose standard deviation is small enough that defects are extremely rare. The same calculation supports tolerance analysis, supplier quality monitoring and any process where consistency matters as much as average performance.
Education and grading use standard deviation to interpret test results. A class with an average of 75 percent and a standard deviation of 5 points has tightly clustered grades, suggesting either consistent student performance or a test that did not differentiate well. The same average with a standard deviation of 18 points indicates wide spread, suggesting larger differences in student performance. Educators sometimes curve grades by computing z-scores and assigning letter grades based on standard-deviation distance from the class mean rather than on absolute percentage thresholds.
Finance and investing rely heavily on standard deviation as a measure of volatility. The standard deviation of monthly returns is the simplest definition of risk in modern portfolio theory. Two investments with the same average return but different standard deviations are not equivalent — the higher-standard-deviation investment carries more uncertainty about any single year's outcome. Risk-adjusted return measures like the Sharpe ratio explicitly divide return by standard deviation to compare investments on a fair basis.
Risk management in healthcare also relies heavily on standard deviation. Time-to-event analyses, length-of-stay distributions, infection rate trends and patient throughput metrics all use standard deviation as a primary spread measure. A hospital with a low standard deviation in surgical recovery time has a more predictable workflow than one with a high standard deviation, even when the average recovery time is the same. The implications cascade through staffing decisions, scheduling and patient communication.
Standard deviation calculations are sensitive to data quality, and a few minutes of preparation prevents most surprises. Remove header rows from the calculation range — STDEV.S(A1:A100) where A1 is the column header returns #VALUE! because the function rejects text. Confirm that all values in the range are numbers; cells formatted as text but containing numeric-looking content trip up the calculation silently in some cases.
Decide explicitly how to handle blank cells. STDEV.S skips empty cells automatically but treats zeros as data, so a column with many missing observations recorded as zero produces a smaller standard deviation than the same data with the missing observations as actual blanks.
Outliers deserve specific attention. A single extreme value can dramatically inflate the standard deviation, making the rest of the data look more variable than it really is. Decide before you calculate whether outliers should be included or excluded. Some workflows trim the top and bottom one or two percent of values before computing standard deviation; others keep every observation but flag the outliers separately. The right choice depends on the question you are trying to answer — for inferential statistics on a stable population, trimming is often appropriate; for quality control, outliers are usually exactly what you want to detect.
Pivot tables provide one easy path to running standard deviation calculations on grouped data. Drop the field of interest into the Values area, then click the field name and choose Value Field Settings, then change the calculation from Sum to StdDev (sample) or StdDevp (population). The pivot table now shows the standard deviation per group rather than the sum, which is often the most useful breakdown for business analysis. The same approach works for variance, average, count and other built-in pivot summary functions.
Variance is the squared standard deviation. Excel offers VAR.S for sample variance and VAR.P for population variance, with the same n minus one versus n distinction. Variance is rarely the right number to display in a report because it has squared units that are hard to interpret intuitively. It is, however, the foundation for further statistical calculation — analysis of variance (ANOVA), regression residual analysis, hypothesis testing — because many statistical formulas combine variances arithmetically in ways that would be awkward with standard deviations directly.
For confidence intervals, the related function CONFIDENCE.NORM (or its older cousin CONFIDENCE) returns the half-width of a confidence interval given the alpha level, standard deviation and sample size. Combined with AVERAGE, this lets you compute the lower and upper bounds of a 95 percent confidence interval for the mean. NORM.DIST and NORM.INV translate between specific values and percentiles in a normal distribution, which combine with standard deviation to evaluate questions like "what percentage of observations are likely below this threshold". These functions form the practical statistical toolkit that builds on standard deviation as the basic spread measure.
Bessel's correction applies in variance functions just as it does in standard deviation functions. VAR.S divides by n minus one; VAR.P divides by n. The square root relationship between variance and standard deviation means that taking the square root of VAR.S gives the same result as STDEV.S directly, and the same for VAR.P and STDEV.P. The two pairs of functions provide identical information in different units, and the choice between them depends only on the downstream use case.
Excel charts can visualise standard deviation through error bars, which graphically show the spread around each data point. To add error bars to a chart, click the chart, click the plus sign that appears, check Error Bars and choose More Options to specify the standard-deviation source. Error bars based on standard deviation give readers an immediate visual sense of how reliable each plotted average is — narrow error bars mean tight clustering, wide error bars mean wider variability. This is particularly useful in presentations where the audience may not read the underlying numbers carefully.
Beyond error bars, control charts and box plots are standard visualisations that depend on standard deviation. Excel does not have a built-in control chart wizard but constructing one is straightforward — plot the time-ordered data, add horizontal lines at the mean and at plus or minus three standard deviations, and label points outside the limits. Box plots show the median, quartiles and outliers and are available as a built-in chart type in Excel 2016 and later. Both visualisations turn descriptive statistics into shareable insight that text and tables alone do not produce.
Standard deviation also feeds confidence interval visualisations on time series charts, where shaded bands around the data line indicate plus or minus one or two standard deviations. This visualisation pattern is widely used in financial analysis, scientific publication and dashboard reporting. The shaded band immediately conveys to the reader where future observations are likely to fall if the underlying process remains stable, which is one of the most actionable visual interpretations of standard deviation.
Control charts plot data against mean ± 3 SD limits. Out-of-limit points indicate process drift. Six Sigma methodology targets very low standard deviation relative to specification limits, treating consistency as a primary process quality metric.
Grade distributions characterised by mean and standard deviation. Z-scores translate raw scores into distance-from-mean comparisons. Curving and grading-on-a-curve methods rely on standard deviation directly to set letter-grade boundaries.
Monthly or daily return standard deviation is the simplest investment risk metric. Sharpe ratio divides excess return by standard deviation to compare risk-adjusted performance across investments.
Standard deviation feeds the t-test or z-test that determines whether two treatments differ significantly. Larger sample standard deviations require larger sample sizes to detect the same effect, which feeds power calculations directly.
Process capability indices like Cp and Cpk explicitly divide specification width by standard deviation. Tighter standard deviations relative to the specification window indicate stronger process capability.
Repeated measurement of a physical quantity yields standard deviation as the precision indicator. Reporting a measurement as the mean ± one or two standard deviations is the common scientific convention for communicating precision.
Standard deviation is not the only way to measure spread. Mean absolute deviation (MAD) is an alternative that takes the average of the absolute deviations from the mean rather than squaring them. MAD is more intuitive than standard deviation in some respects — it has the same units as the data, and the calculation is easier to explain because it does not involve squaring. MAD is also more robust to outliers because squaring inflates the influence of extreme values disproportionately.
So why is standard deviation the dominant measure? Standard deviation has cleaner mathematical properties that make it more useful in inferential statistics. The squared deviations underlying variance combine arithmetically — the variance of independent variables can be added, while mean absolute deviations cannot — which makes standard deviation the natural building block for hypothesis testing, regression analysis and most of statistical theory. For descriptive purposes, MAD is sometimes preferable, but for any analysis that connects to inferential statistics or modelling, standard deviation remains the right answer.
Median absolute deviation (MAD, with a different acronym expansion than mean absolute deviation) is a third spread measure used widely in robust statistics. It computes the median of the absolute deviations from the median rather than the mean, making it extremely resistant to outliers. Excel does not have a built-in MAD function but constructing one with MEDIAN, ABS and ARRAYFORMULA is straightforward. For datasets with significant outliers or non-normal distributions, MAD often produces a more honest description of the spread than standard deviation alone.