How to Use the STDEV.S and STDEV.P Functions in Microsoft Excel to Calculate Standard Deviation

Analyzing datasets in Microsoft Excel requires measuring not just average values (the mean), but understanding the spread, variability, and volatility of the data points. A high average salary or high test score mean might mask extreme inequality or volatile spikes if the dispersion is high. The standard statistical measure of dispersion is standard deviation. In Microsoft Excel, calculating this accurately requires choosing between two dedicated statistical functions: STDEV.S and STDEV.P.

Understanding the mathematical and practical difference between sample standard deviation (STDEV.S) and population standard deviation (STDEV.P) is crucial to avoid statistical bias and skewed reporting in financial modeling and scientific research.

Sample vs Population: Which Function Should You Use?

The choice between these two functions hinges on whether your spreadsheet data represents a partial sample or the entire population:

• STDEV.S (Sample Standard Deviation): Use when your data represents a sample subset of a larger population (e.g. testing 50 manufactured lightbulbs out of a factory batch of 10,000, or surveying 200 customers). It applies Bessel’s correction, dividing by n - 1 to prevent underestimating variance.
• STDEV.P (Population Standard Deviation): Use when your dataset encompasses the entire complete population under analysis (e.g. all 12 monthly sales figures for a specific financial year, or the test scores of all 25 students enrolled in a private classroom). It divides by N.

Syntax and Behaviour of the Functions

Both functions accept cell ranges, arrays, or comma-separated numbers:

Syntax for Sample Standard Deviation:

=STDEV.S(number1, [number2], ...)

Syntax for Population Standard Deviation:

=STDEV.P(number1, [number2], ...)

Argument Guidelines:

• number1, [number2]: 1 to 255 arguments containing numbers, cell ranges (e.g. A2:A100), or arrays.
• Ignored Data: Text cells, empty cells, and logical boolean values (TRUE/FALSE) in referenced ranges are automatically ignored.
• Minimum Requirement: STDEV.S requires at least two numeric values (otherwise it returns a #DIV/0! error due to n - 1 division). STDEV.P requires at least one number.

Practical Excel Examples

Example 1: Quality Assurance Testing (Sample)

A manufacturing engineer tests the tensile strength (in MPa) of 10 metal rods selected at random from an assembly line of thousands. The measurements reside in cells B2:B11:

=STDEV.S(B2:B11)

Because the engineer is extrapolating factory-wide quality from a random sample, STDEV.S provides the unbiased estimate of population standard deviation.

Example 2: Annual Corporate Performance (Population)

A financial director analyzes the monthly revenue totals across all 12 completed months of 2025 in cells C2:C13:

=STDEV.P(C2:C13)

Because there are exactly 12 months in the year and all 12 are present in the table, the dataset represents the complete population for that fiscal year.

Example 3: Building a Two-Sigma (95% Confidence) Interval

In quality control and finance, combining standard deviation with the mean establishes natural upper and lower control bounds:

• Average: =AVERAGE(A2:A100) (Stored in cell D2)
• Standard Deviation: =STDEV.S(A2:A100) (Stored in cell D3)
• Upper Limit (+2σ): =D2 + (2 * D3)
• Lower Limit (-2σ): =D2 - (2 * D3)

Any data points falling outside this range represent statistically significant outliers warranting inspection.

STDEV.S vs STDEV.P Comparison Table

Comparison Factor	STDEV.S	STDEV.P
Scope	Sample subset of a population	Complete, exhaustive population
Mathematical Denominator	Divides by n - 1 (Bessel’s correction)	Divides by N
Output Magnitude	Slightly larger value to account for sample variance	Slightly smaller, exact variance figure
Legacy Alternative	Replaces deprecated STDEV	Replaces deprecated STDEVP
Typical Use Cases	Surveys, medical clinical trials, batch testing	Census records, full fiscal years, complete class rosters

Troubleshooting Common Errors

• #DIV/0! Error: Occurs in STDEV.S when the referenced range contains fewer than two numerical values, or when all cells contain text. Ensure your range includes at least two numeric cells.
• #VALUE! Error: Occurs when you type text strings directly into the formula arguments (e.g. =STDEV.S(10, "Ten", 20)) rather than referencing cells containing text (which Excel ignores gracefully).
• Handling Text as Numbers (STDEVA / STDEVPA): If your dataset intentionally uses text or boolean values that represent numbers (where TRUE=1 and FALSE=0), use the companion functions STDEVA or STDEVPA instead.