In Excel for Microsoft 365, use STDEV.S to calculate standard deviation from a sample, or STDEV.P when your data includes the entire population you want to describe. For example, if your measurements are in A2:A6, enter =STDEV.S(A2:A6) for a sample or =STDEV.P(A2:A6) for a population.
The Excel formulas and what they calculate
Standard deviation describes how spread out a set of numerical values is around its mean, or average. A small standard deviation means values tend to be close to the mean; a larger one means they tend to be more dispersed. Excel returns the result in the same units as the original data, which makes it easier to interpret than variance, the related measure expressed in squared units.
For a sample, the formula assumes your recorded values are a subset of a larger group and estimates the spread of that larger group. With sample measurements in A2:A6, enter =STDEV.S(A2:A6). For a complete population, where the listed values are all the members you want to describe, use =STDEV.P(A2:A6).
Both functions accept a range, individual numbers, or multiple ranges as arguments. A range is usually the clearest choice because it stays understandable as a worksheet changes. Excel recalculates the result when a referenced numeric value changes.
Sample versus population: choosing the right function
The distinction is about what your data represents, not the number of rows you have. Five observations could be a sample of a much larger process, or they could be the complete population if those five observations are every item you intend to analyze.
- Use STDEV.S when your data is a sample used to estimate variation in a wider group. Examples include measurements from selected products in a production run or a survey of some customers.
- Use STDEV.P when the data contains every member of the population of interest. For example, if you are describing the scores of every student in a particular small class, those scores form that class’s complete population.
The formulas differ in how they calculate variance before taking its square root. The sample calculation divides the sum of squared differences from the sample mean by n minus 1. The population calculation divides by n, the number of values. Using n minus 1 is a standard adjustment when a sample is used to estimate population variability; it is not a rule that the sample should contain a particular percentage of the population.
If you are unsure, define the question first: are you describing only the values collected, or using them to infer the variability of a larger group? Choose the function that matches that purpose. The fact that your spreadsheet contains all available records does not automatically make them a population; the records may still be a sample of a broader process or time period.
Worked example with a small data set
Suppose A2:A6 contains the values 2, 4, 4, 4, and 6. Their mean is 4. The squared differences from the mean are 4, 0, 0, 0, and 4, which add to 8. This makes the difference between the two Excel functions visible:
- For a sample, the variance is 8 divided by 4, or 2. Its square root is about 1.4142.
- For the full population, the variance is 8 divided by 5, or 1.6. Its square root is about 1.2649.
With those five values in A2:A6, enter =STDEV.S(A2:A6); the result is approximately 1.41421356. With the same values in A2:A6, enter =STDEV.P(A2:A6); the result is approximately 1.26491106.
The different answers do not mean one formula is inherently more accurate. They answer different statistical questions. If the five values are a sample of a larger group, the sample result is appropriate for estimating its spread. If they are the entire group under consideration, the population result describes that group directly.
How Excel treats cells in the range
For STDEV.S and STDEV.P, numbers in referenced cells are included, while blank cells, text, and logical values in a referenced range are ignored. A numeric zero is included and can affect the result. This means a blank cell is not interchangeable with a cell containing zero: a blank is omitted, while zero is treated as a measurement.
These functions are intended for numeric data. If a range contains a number stored as text, Excel may not treat it as a number when it is referenced by these functions. Check imported data if the result seems unexpected: values that look numeric can be stored as text, especially after copying data from another system. Converting those entries to actual numbers can resolve the issue.
Errors in the referenced cells can also cause the calculation to return an error rather than a standard deviation. Consider checking the source cells and cleaning or correcting the data, rather than suppressing errors without understanding what they represent. Also confirm that the selected range contains exactly the observations you intend to analyze; accidentally including a total, heading, or unrelated value can change the result.
Interpreting the result in context
Standard deviation is expressed in the same units as the observations. If a set of lengths is measured in centimeters, its standard deviation is in centimeters. It is not a percentage and does not mean that every value lies within that distance of the mean. It summarizes overall spread; individual observations can be farther away.
Compare standard deviations carefully. A value of 5 may represent substantial variation for a process whose measurements are usually near 10, but relatively little for one whose measurements are around 10,000. When comparing different scales, a coefficient of variation may be useful, but it is a separate statistic and is not appropriate in every situation, particularly when the mean is near zero or the data can be negative.
Standard deviation is sensitive to unusually high or low observations because it squares each observation’s distance from the mean. A single outlier can therefore increase the result considerably. That does not automatically make the result wrong: first check whether the unusual value is a data-entry mistake or a legitimate observation. For heavily skewed data or data with important outliers, the median and interquartile range can provide complementary information about typical values and spread.
It is also common to assume that standard deviation itself tells you the percentage of values within a certain distance of the mean. That interpretation depends on the shape of the distribution. For a roughly normal, bell-shaped distribution, familiar rules of thumb relate distances of one, two, and three standard deviations to approximate proportions. Those rules should not be applied automatically to every data set.
Common errors and troubleshooting
If Excel returns #DIV/0! for STDEV.S, check whether the range contains at least two numeric observations. A sample standard deviation cannot be calculated from fewer than two numeric values. STDEV.P needs at least one numeric value; a range with no numeric values also produces a division-by-zero error.
- The result is larger or smaller than expected: verify whether you need a sample or population calculation, then inspect the range for zeros, outliers, extra rows, or omitted observations.
- The formula appears as text: check whether the cell is formatted as Text or whether an apostrophe precedes the equals sign. Change the format to General and re-enter the formula if needed.
- The function name is not recognized: check spelling and make sure the formula begins with an equals sign. In older workbooks, compatibility function names may appear instead.
- The result changes after adding rows: a fixed range such as A2:A6 does not automatically expand to include a new value in A7. Update the range or use an Excel table column reference so the calculation can include new table rows.
Excel for Microsoft 365 on Windows and Mac supports these functions, and they are also available in Excel for the web. Formula argument separators can depend on regional settings; in some installations, a semicolon is used where examples here show a comma. The single-range examples above do not require an argument separator between multiple inputs.
Older function names and related alternatives
Older Excel workbooks may contain STDEV or STDEVP. These are compatibility function names: STDEV corresponds to the sample calculation, and STDEVP corresponds to the population calculation. For new formulas, STDEV.S and STDEV.P make the intended choice clearer. If a workbook already uses the older names, understand their meaning before replacing them; do not change a sample calculation to a population calculation simply to modernize the formula.
Excel also has functions with an A suffix, including STDEVA and STDEVPA, for calculations that treat logical values and text differently from STDEV.S and STDEV.P. They are intended for data where those entries should be included under those functions’ rules. For ordinary numeric measurements, use STDEV.S or STDEV.P instead. If a dataset includes text labels or TRUE/FALSE values, decide explicitly how those should be represented rather than choosing an A-suffix function by guesswork.
If your goal is to compare two groups, calculate the standard deviation for each group using the correct sample or population function, and keep the same interpretation for both. If your goal is to describe uncertainty in an estimated mean, standard deviation is not the same thing as standard error. Standard error concerns the precision of an estimate and depends on the sample size; confusing the two can lead to an incorrect interpretation.
Frequently Asked Questions
Should I use STDEV.S or STDEV.P in Excel?
Use STDEV.S when your values are a sample used to estimate a larger population’s variability. Use STDEV.P when your data includes the complete population you want to describe.
What is the standard deviation formula for a sample in Excel?
With sample values in A2:A20, enter =STDEV.S(A2:A20). Replace the range with the cells containing your numeric observations.
What is the formula for population standard deviation?
With the complete population in A2:A20, enter =STDEV.P(A2:A20). The population function divides by the number of numeric values rather than using the sample adjustment.
Why does STDEV.S return #DIV/0!?
The referenced range may contain fewer than two numeric values. Check for blanks, text stored as numbers, or an incorrectly selected range; STDEV.S requires at least two numeric observations.
Does standard deviation include zero values?
Yes. A numeric zero in a referenced range is included in the calculation. Blank cells and text in the range are ignored, so check whether a missing observation has been entered as zero by mistake.
Related
- Python pandas Excel Formatting: A Practical Guide to Styles
- Correlation Table in Excel: Create and Read One
- Python Pandas Excel Export: Methods and Examples
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel MID Function: Extract Text From the Middle of TextExcel’s MID function extracts a chosen number of characters from the middle of a text value. With the…
- Excel IFS for Blank Cells: Formulas and Common FixesTo make Excel’s IFS function return a result when a cell is blank, test for a blank first:…
- Excel IFS in Conditional Formatting: How to Use ItYes. In Excel for Microsoft 365 on Windows, you can use the IFS function in a conditional-formatting formula,…
- XLOOKUP Example: Step-by-Step Formula Guide for ExcelTo find a product’s price by its ID in Excel, enter =XLOOKUP(E2,$A$2:$A$6,$C$2:$C$6,"Not found") in the result cell. If…
- Excel Shortcut to Pull Down a Formula: Fill Down FastIn Excel for Microsoft 365 on Windows, select the formula cell and the cells below where you want…
- Excel Text Comparison: Formulas, Case, Spaces, and ErrorsWith the text values in A2 and B2, compare them in Excel using =A2=B2 for a case-insensitive check…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.