How to Calculate Standard Deviation in Excel

To calculate standard deviation in Excel, use =STDEV.S(range) for a sample or =STDEV.P(range) for a whole population. Standard deviation tells you how spread out your numbers are around the average: whether most values sit close to it or swing far above and below. This guide explains which function to pick, what the result means, and how to work it out step by step.

What standard deviation tells you#

Standard deviation measures how far values typically are from the average, in the same units as the data. Two lists can have the same average and very different spreads.

Here are six weeks of unit sales from two shops:

Both shops average 14 units a week. Shop A is steady: its standard deviation is about 0.6 units. Shop B swings from 9 to 18, and its standard deviation is about 3.2 units. The average alone hides that difference. The guide to AVERAGE, MIN and MAX covers the other basic summaries you'd put next to it.

For data with a rough bell shape, about two-thirds of the values fall within one standard deviation of the average. For Shop B, that is between about 10.8 and 17.2.

How to calculate standard deviation in Excel#

Select an empty cell and type STDEV.S with the range of numbers. With Shop B's sales in B2:B7:

=STDEV.S(B2:B7)

The result is 3.16 (3.1623 to four places). That's all you need for most reports.

STDEV.S ignores empty cells, text and TRUE/FALSE values in the range. It needs at least two numbers, or it returns #DIV/0!.

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

Use STDEV.S for a sample and STDEV.P for a complete population. The only difference is what they divide by.

A sample is part of a bigger group, such as six weeks out of a year of sales, or 50 customers out of thousands. A population is every member of the group you're describing, such as all ten people on one team. Dividing by n − 1 makes the sample result slightly larger, to allow for the values you didn't see. When in doubt, use STDEV.S: most business and test data is a sample.

You may also meet these related functions:

• STDEV and STDEVP are the older names (before Excel 2010). They give the same results as STDEV.S and STDEV.P and still work.
• STDEVA and STDEVPA count text as 0 and TRUE as 1. You rarely want that.
• VAR.S and VAR.P return the variance, which is the standard deviation squared: 10 and 8.33 for Shop B.

How to calculate standard deviation by hand#

Doing it once by hand makes the result easy to understand. For Shop B:

1. Find the average with the AVERAGE function: 14.
2. Subtract the average from each value and square the result, so negative distances don't cancel positive ones.
3. Add the squares: 50.
4. Divide by n − 1 for a sample (50 / 5 = 10) or by n for a population (50 / 6 = 8.33). This is the variance.
5. Take the square root: √10 = 3.16 for a sample, √8.33 = 2.89 for a population.

One formula does all five steps:

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

It returns 3.16, the same as STDEV.S. Remove the -1 and it matches STDEV.P.

Compare spread with the coefficient of variation#

A standard deviation of 3 means a lot when the average is 14 and very little when it is 1,400. To compare groups with different averages, divide the standard deviation by the average:

=STDEV.S(B2:B7)/AVERAGE(B2:B7)

This is the coefficient of variation. For Shop B it is 0.226, or about 23% when formatted as a percentage. Shop A is about 5%. Lower means steadier.

Standard deviation is sensitive to outliers, because squaring makes big distances count heavily. If one huge value skews your data, look at the median as well. The guide to AVERAGE vs MEDIAN explains when the middle value is the better summary, and MEDIAN and MODE shows how to calculate both.

Standard deviation with conditions or filters#

Excel has no STDEVIFS function, but you can still get the spread for one group.

For one category in Excel 2021 or Microsoft 365, put IF inside STDEV.S. If column A holds regions and column C holds sales:

=STDEV.S(IF(A2:A50="North",C2:C50))

IF keeps the North sales and turns the rest into FALSE, which STDEV.S ignores. In Excel 2019 and earlier, confirm it as an array formula with Ctrl+Shift+Enter (Mac: ⌘+Shift+Return).

For a filtered list, use SUBTOTAL so hidden rows are left out. Function number 7 is the sample standard deviation and 8 is the population one:

=SUBTOTAL(7,C2:C50)

With rows hidden by a filter, only the visible ones are used. The guide to the SUBTOTAL function lists all the function numbers. AGGREGATE works the same way and can also skip error values.

To measure how precise an average is, divide by the square root of the count. This is the standard error: =STDEV.S(B2:B7)/SQRT(COUNT(B2:B7)) returns 1.29 for Shop B.

Common standard deviation mistakes#

These are the slips that give wrong results most often:

1. Using STDEV.P for a sample. It gives a result that's too small. Use STDEV.S unless you have every value.
2. Including the total row. A range like B2:B8 that takes in a total in B8 adds one huge value.
3. Numbers stored as text. They're ignored, so the count is lower than you think. Check with COUNT.
4. Comparing raw standard deviations. Groups with different averages need the coefficient of variation.
5. Weighting. STDEV.S treats every value equally. If rows represent different sizes, the plain result is misleading, much like a plain average versus a weighted average.

Practise it#

Standard deviation builds on the average, so make the basics automatic first. Practise AVERAGE, MIN and MAX in a live spreadsheet: you type each formula and the sheet checks it instantly. Then compare middle values in the MEDIAN and MODE lesson, and work out a standard deviation by hand in the exercise on this page.