Cellwise Try it free

Excel guide · 3 min read

How to calculate standard deviation in Excel

Quick answerUse =STDEV.S(B2:B9) when your numbers are a sample, such as 8 weeks used to judge how a store usually performs. Use =STDEV.P(B2:B9) when the data is the entire population you care about. The result is in the same units as your data and shows how far values typically sit from the average.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

ABC
1WeekStore A ordersStore B orders
2Wk 1412398
3Wk 2438512
4Wk 3405301
5Wk 4426455
6Wk 5419389
7Wk 6431476
8Wk 7409334
9Wk 8424471

Method 1: STDEV.S

Both stores average about 420 orders a week (420.5 and 417). The averages say they are the same. Standard deviation shows they are not.

STDEV.S ignores empty cells and text in a range, like AVERAGE. You need at least two numbers, or it returns #DIV/0!.

=STDEV.S(B2:B9)

Result: 11.376667853

About 11.4 orders: Store A is very steady from week to week. The same formula on C2:C9 gives about 74.0 for Store B.

STDEV.S or STDEV.P: which one to use

STDEV.P treats the numbers as the whole population and divides the squared differences by n. STDEV.S treats them as a sample and divides by n - 1. Dividing by one less makes the sample result slightly bigger, which corrects for the fact that a sample usually underestimates the spread of the bigger group it came from.

Ask what you want to describe. If you want to know how variable Store A is in general, these 8 weeks are a sample of all its weeks: use STDEV.S. If these 8 values are everything (the final scores of all 8 students in a class, and you only care about that class), use STDEV.P. When in doubt, STDEV.S is the safer and more common choice.

With small samples the gap matters. With hundreds of values the two results are almost the same. The formula below is the population calculation written out, which gives the same value STDEV.P returns.

=SQRT(SUMPRODUCT((B2:B9-AVERAGE(B2:B9))^2)/COUNT(B2:B9))

Result: 10.641898327

About 10.64, the population version for Store A, compared with 11.38 from STDEV.S.

Compare spread fairly: coefficient of variation

A standard deviation of 74 orders is huge for a store doing 400 orders a week, but would be tiny for one doing 40,000. Divide by the average to get the spread as a percentage, called the coefficient of variation (CV). Use it to compare stores, products or suppliers of different sizes.

=STDEV.S(C2:C9)/AVERAGE(C2:C9)

Result: 0.17751357889 shows as 17.8% with Percent format

0.1775, Store B swings about 17.8% around its average. Store A is about 2.7%.

Count how many values fall within one standard deviation

A band of average plus or minus one standard deviation shows the normal range. COUNTIFS counts the weeks inside it. For roughly bell-shaped data, about two thirds of values fall inside, so a much smaller share is a hint that the data is lumpy or has outliers.

=COUNTIFS(C2:C9,">="&AVERAGE(C2:C9)-STDEV.S(C2:C9),C2:C9,"<="&AVERAGE(C2:C9)+STDEV.S(C2:C9))

Result: 5

5 of Store B weeks sit between about 343 and 491 orders.

How unusual is one value? The z-score

Subtract the average and divide by the standard deviation. The result says how many standard deviations a value is from normal. Beyond 2 or -2 is worth a look. Excel also has STANDARDIZE(x,mean,standard_dev), which does the same thing.

=(C3-AVERAGE(C2:C9))/STDEV.S(C2:C9)

Result: 1.2833820783

1.28: week 2 at Store B (512 orders) was about 1.3 standard deviations above its average. High, but not extreme.

Older and related functions

STDEV and STDEVP are the pre-2010 names for STDEV.S and STDEV.P and give the same answers. STDEVA and STDEVPA count TRUE as 1 and text as 0, which is rarely what you want. For variance (the standard deviation squared), use VAR.S or VAR.P.

For a full summary table, enable the Analysis ToolPak (File > Options > Add-ins) and use Data > Data Analysis > Descriptive Statistics. Its Standard Deviation row is the sample (STDEV.S) version.

Common mistakes

Picking STDEV.P for a sample

If the data stands for something bigger (future weeks, all customers), use STDEV.S. STDEV.P understates the spread of small samples.

Mixing two groups in one range

=STDEV.S(B2:C9) blends both stores and gives about 51.2, which describes neither. Calculate each group separately.

Zeros typed for missing weeks

A 0 for a week the store was closed adds a huge fake swing. Leave it blank so STDEV.S ignores it.

Comparing standard deviations of different-sized things

Divide by the average (coefficient of variation) before comparing.

FAQ

What is the difference between STDEV.S and STDEV.P?

STDEV.S is for a sample and divides by n - 1. STDEV.P is for a whole population and divides by n. STDEV.S gives a slightly larger result.

Which standard deviation should I use in Excel?

Use STDEV.S unless your data is the complete group you want to describe and you will not generalize from it. Most business and survey data is a sample.

How do I calculate standard deviation with a condition?

In Excel 365 or 2021, filter first: =STDEV.S(FILTER(C2:C20,B2:B20="East")). In older versions, use =STDEV.S(IF(B2:B20="East",C2:C20)) with Ctrl+Shift+Enter.

What does a standard deviation of 0 mean?

Every value is the same. The larger the result, the more the values spread out from the average.

Functions used

Related guides

Updated