STDEV.S vs STDEV.P: Sample or Population?

The short answer to STDEV.S vs STDEV.P: use STDEV.S when your numbers are a sample from a bigger group, and STDEV.P when your numbers are the entire population you want to describe. Both measure standard deviation, which is how spread out values are around the average. This guide shows both on the same data, explains the one-line difference in the maths, and helps you pick the right one in a report or a job test.

STDEV.S vs STDEV.P at a glance#

The two functions take the same arguments and differ only in what they divide by.

Both ignore empty cells, text and TRUE/FALSE values inside a range. Both are typed the same way: =STDEV.S(B2:B7) or =STDEV.P(B2:B7).

Sample or population: what is the difference?#

A population is every member of the group you want to describe. A sample is a part of that group that you use to estimate something about the whole.

Whether your data is a sample depends on the question, not on the data:

• The scores of all six people on your team are a population if you only want to describe that team.
• The same six scores are a sample if you use them to estimate how all 200 people in the company would score.
• Six weeks of sales are a sample if you want to describe sales across the whole year.
• Every invoice issued last month is a population if the report is about last month only.

So ask yourself: "Do I have every value I'm describing, or am I using these to guess about more?" If you are guessing about more, it's a sample.

The same data solved both ways#

Here are test scores for a team of six, in A1:B7:

The average, from =AVERAGE(B2:B7), is 80. Now the two standard deviations:

=STDEV.S(B2:B7)
=STDEV.P(B2:B7)

STDEV.S returns 9.01 and STDEV.P returns 8.23 (rounded to two places). Same data, two answers. To see why, work through the steps by hand:

Both functions add up the squared distances (406). Then they split:

• STDEV.P divides by the count, 6: 406 ÷ 6 = 67.67. The square root is 8.23.
• STDEV.S divides by the count minus 1, 5: 406 ÷ 5 = 81.2. The square root is 9.01.

The number before the square root is the variance. Excel returns it with VAR.P (67.67) and VAR.S (81.2). For more on reading the result, see the guide to standard deviation in Excel.

How big is the gap?#

STDEV.S is larger by a factor of √(n ÷ (n − 1)). The gap matters with small samples and almost disappears with large ones:

So with a few values, picking the wrong one changes your answer noticeably. With hundreds of rows, both give almost the same result.

When to use STDEV.S#

Use STDEV.S whenever your data stands in for a bigger group. That covers most real work:

• Survey results. 150 customers answered, but you want to describe all customers.
• Quality checks. You weighed 20 bags from a production run of 10,000.
• Time periods. A few weeks or months of sales used to describe typical performance.
• Test data in an interview. Unless the question says the data is the full group, the safe answer is STDEV.S.

Why divide by n − 1? A sample usually misses some of the most extreme values in the full group, so its spread tends to come out a little too small. Dividing by a smaller number pushes the result up to correct for that. Statisticians call this Bessel's correction.

When to use STDEV.P#

Use STDEV.P when your data includes every value you are describing, and you don't want to generalise beyond it:

• One team or class. The exam scores of all 25 students in one class, reported for that class.
• A closed period. Every transaction in March, for a March-only report.
• A complete list. The prices of all 40 products in your catalogue.

If someone later uses those numbers to predict next month or another team, they have become a sample, and STDEV.S is the better choice.

Common mistakes with STDEV.S and STDEV.P#

Most wrong answers come from picking the wrong function or from the data itself, not from typing errors.

1. Using STDEV.P for a sample. It understates the spread, most of all with small samples. Default to STDEV.S unless you have the whole group.
2. Numbers stored as text. Both functions skip text in a range, so imported numbers that are really text drop out without warning. Check with =COUNT(B2:B7): it should equal the number of values you expect.
3. Only one value. STDEV.S returns #DIV/0! because it divides by 1 − 1 = 0. STDEV.P returns 0, which can hide a range that is far too small.
4. Mixing up the A versions. STDEVA and STDEVPA count text as 0 and TRUE as 1. That is like the difference between AVERAGE and AVERAGEA, explained in AVERAGE vs AVERAGEA. You rarely want it.
5. Reading the result without the average. A standard deviation of 9 means a lot for scores averaging 20 and little for scores averaging 800. Report it next to the mean, and check the median too: the guide to average vs median shows how one outlier pulls the average.
6. Hidden or filtered rows. STDEV.S includes them. To measure only visible rows, use SUBTOTAL with function number 107 (sample) or 108 (population), as covered in the SUBTOTAL function guide. AGGREGATE with function 7 or 8 can also skip errors.

The older STDEV and STDEVP still work and give the same results, so you can leave them in old files. For new work, the dotted names make your choice clear to anyone reading the formula.

Our recommendation#

Use STDEV.S by default. Most business data, survey data and test data is a sample of something bigger, and STDEV.S is the right estimate for that. Switch to STDEV.P only when you can say "these values are the entire group, and I'm describing only this group." If a job test asks for "the standard deviation" with no other detail, write STDEV.S and, if you can, note your reason in a cell next to it.

Whichever you choose, put it beside the basics. AVERAGE, MIN and MAX tell you the centre and the range; see AVERAGE, MIN and MAX in Excel. MEDIAN and MODE show the typical value; see MEDIAN and MODE in Excel. The AVERAGE function guide is a quick syntax reference.

Practise it#

Standard deviation builds on the average, so start there. Practise AVERAGE, MIN and MAX in a live spreadsheet, then practise MEDIAN and MODE to see how one outlier changes the picture. Each lesson checks your formula instantly. Then try the exercise on this page: work out the population standard deviation by hand and check that you get 8.23. When you're ready for a timed set of questions like these, take the data analyst Excel test.