What Is STDEV.P vs STDEV.S? (Excel Statistics Guide)

STDEV.P calculates spread when your numbers include every member of the group you want to describe, dividing squared deviations by N. STDEV.S estimates spread from a sample, dividing by n−1. Choose by what the data represent, not by their size. Check numeric entries, then report the function, units, and scope with your result.

In 1894, statistician Karl Pearson introduced the term “standard deviation.” Today, Excel can calculate it in seconds, but the key choice is still about what your numbers represent. A quick formula can give a misleading answer if you do not first decide whether your data cover a whole group or only part of one.

STDEV.P vs. STDEV.S: Function Overview and When to Use Each

These functions both measure how far values tend to vary from their mean. The difference is the group you want to describe: use STDEV.P for all members of that group, and STDEV.S when your data are a sample used to estimate a larger group.

A population is the full set of items or people relevant to your question. A sample is only some members of that set. For example, if you record every item in a small shipment, those measurements are a population for that shipment. If you inspect some items to estimate variation across all future shipments, they are a sample.

Dataset size alone does not decide which function to use. A group of three can be a full population if those are all the members you care about. A much larger dataset can still be a sample if it represents a broader group.

Excel Formulas, Denominators, and Data Requirements

The formulas differ in their denominator. STDEV.P divides by the population count, N; STDEV.S divides by one less than the sample count, n−1. Both calculate spread around a mean, and both return a standard deviation in the same units as the input values.

Enter a range in Excel like this:

• Population: =STDEV.P(A2:A5)
• Sample: =STDEV.S(A2:A5)

In the formulas below, (x_i) means an individual value, (\mu) is the population mean, (\bar{x}) is the sample mean, and the summation sign means to add the squared differences.

• Population standard deviation: (\sqrt{\sum (x_i-\mu)^2/N})
• Sample standard deviation: (\sqrt{\sum (x_i-\bar{x})^2/(n-1)})

The sample formula’s use of n−1 is known as Bessel’s correction. It helps correct the estimate of population variance when you calculate from a sample. It does not mean STDEV.S is always more accurate: the right function depends on your question.

Worked calculation: Enter the values 2, 4, 6, and 8 in cells A2 through A5. Their mean is 5, and the squared differences from the mean total 20.

• =STDEV.P(A2:A5) returns about 2.24, calculated as √(20/4).
• =STDEV.S(A2:A5) returns about 2.58, calculated as √(20/3).

The sample result is larger because it divides by 3 rather than 4. That difference is expected; it is not evidence that one formula is broken.

For values in a referenced range, Excel ignores text and logical values when these functions calculate standard deviation. Blank cells are not numeric observations either. Check that this behavior matches your intent: a text note such as “missing” will not count as a measurement, and an omitted number will not be included.

Choosing Correctly: Data Checks, Interpretation, and Common Errors

A reliable choice starts with the target group, not the formula menu. Before calculating, define the group you want to describe, check which observations are included, and decide whether they form the complete group or a sample. Then interpret the result in context.

Use this four-step check before reporting a result:

1. Name the target group. Write down what the analysis is about, such as “all parts in this shipment” or “all parts made this month.”
2. Check coverage. Confirm whether your data include every member of that group or only selected members.
3. Validate the range. Look for accidental omissions, duplicates, blanks, text, and unexpected numeric entries.
4. Choose and label the function. Use STDEV.P for the complete group or STDEV.S for a sample. State the function, units, and group described.

The distinction matters because using STDEV.P on sample data generally produces a smaller estimate of population variability than STDEV.S does. It divides by n instead of n−1. In the other direction, STDEV.S applies a sample correction even when your goal is simply to describe a complete population. That may not answer your question.

Standard deviation measures spread around the mean, or average. It is reported in the original units: if the input is in millimetres, the standard deviation is in millimetres. It is not the same as variance, which uses squared units, or the range, which is the distance between the highest and lowest values.

A useful report gives more than a number. For example: “The standard deviation of all 12 recorded measurements was 2.24 mm, calculated with STDEV.P.” For a sample, say so: “The sample standard deviation was 2.58 mm, calculated with STDEV.S; the sample was used to estimate variation in the larger group.”

Applying Standard Deviation in Coursework and Workplace Analysis

A good example makes the population boundary clear. The same measurements can call for different functions if the question changes. In each scenario below, the numbers are illustrative; the decision comes from the group being described, not from a rule about a particular field.

Example 1: A complete class list. Suppose you have scores for every student in one class and want to describe the spread of scores in that class. If those scores are the whole group of interest, use STDEV.P. If you select some students’ scores to estimate spread across a larger grade or school, use STDEV.S.

Example 2: Quality checks. Suppose a shop measures every item in one completed batch. To describe that batch, use STDEV.P. If staff inspect a subset of a batch to estimate variation among all items, use STDEV.S. Do not label the sample result as a full-batch measurement.

Example 3: A coursework dataset. An assignment may provide all values for a defined group, or it may ask you to use a sample to estimate a wider population. Read the question’s wording. Terms such as “all,” “entire group,” or “complete data” point to a population; “sample,” “selected,” or “estimate” point to a sample.

In spreadsheet reviews, I find it helpful to write the target group beside the formula. That small note can prevent a common mistake: choosing a function because it gives the result someone expects, rather than because it matches the data. If the group boundary is unclear, settle that question before interpreting the output.

To verify a calculation, test it with a small known dataset or calculate the same result independently. For the values 2, 4, 6, and 8, the results should be about 2.24 for STDEV.P and 2.58 for STDEV.S. If Excel gives a different result, check the cell range and the values it includes.

Conclusion and FAQ

The main decision is simple: STDEV.P describes the full group in your data, while STDEV.S uses a sample to estimate a larger group. The functions differ because their denominators differ. Define your target, check the values, and label the result so readers know what it means.

Is STDEV.P or STDEV.S the better function?
Neither is always better. Use STDEV.P for a complete population and STDEV.S for a sample used to estimate a larger population.

What does STDEV.P mean in Excel?
STDEV.P calculates population standard deviation, using N as the denominator in its variance calculation.

What does STDEV.S mean in Excel?
STDEV.S calculates sample standard deviation, using n−1 as the denominator to help estimate population variability from sample data.

Why does STDEV.S usually return a larger number?
For the same values, STDEV.S divides the sum of squared differences by n−1, while STDEV.P divides by n. The smaller denominator usually makes the sample result larger.

Should I use STDEV.P if my dataset is large?
Not just because it is large. Use STDEV.P if the data cover the full group you want to describe; use STDEV.S if they are a sample of a larger group.

What happens if STDEV.P has one numeric value?
It returns 0 because that one value has no spread within the entered population.

Why does STDEV.S return #DIV/0!?
It needs at least two numeric observations. With fewer than two, Excel cannot divide by n−1 to calculate the sample result.

Do text and blank cells count in STDEV.P or STDEV.S?
Text and logical values in referenced ranges are ignored, and blank cells do not count as numeric observations. Check the range to make sure excluded entries should be left out.

Are standard deviation and variance the same?
No. Standard deviation is the square root of variance and uses the input’s original units. Variance uses squared units.

What should I include when I report standard deviation?
State the result, its units, the group it describes, and whether you used STDEV.P or STDEV.S. If you used a sample, identify it as a sample.

(This article was written by one of our staff writers, Nathan Sterling. Visit our Meet the Team page.)