May
31

How to calculate standard deviation in Excel

The tutorial explains the essence of the standard deviation and standard error of the mean as well as which formula is best to be used for calculating standard deviation in Excel.

In descriptive statistics, the arithmetic mean (also called the average) and standard deviation and are two closely related concepts. But while the former is well understood by most, the latter is comprehended by few. The aim of this tutorial is shed some light on what the standard deviation actually is and how to calculate it in Excel.

What is standard deviation?

The standard deviation is a measure that indicates how much the values of the set of data deviate (spread out) from the mean. To put it differently, the standard deviation shows whether your data is close to the mean or fluctuates a lot.

The purpose of the standard deviation is to help you understand if the mean really returns a "typical" data. The closer the standard deviation is to zero, the lower the data variability and the more reliable the mean is. The standard deviation equal to 0 indicates that every value in the dataset is exactly equal to the mean. The higher the standard deviation, the more variation there is in the data and the less accurate the mean is.

To get a better idea of how this works, please have a look at the following data:
Standard deviation

For Biology, the standard deviation is 5 (rounded to an integer), which tells us that each student's score is no more than 5 points away from the mean. Is that good? Well, yes, it indicates that the Biology scores of the students are pretty consistent.

For Math, the standard deviation is 23. It shows that there is a huge dispersion (spread) in the scores, meaning that some students performed much better and/or some performed far worse than the average.

In practice, the standard deviation is often used by business analysists as a measure of investment risk - the higher the standard deviation, the higher the volatility of the returns.

Sample standard deviation vs. Population standard deviation

In relation to standard deviation, you may often hear the terms "sample" and "population", which refer to the completeness of the data you are working with. The main difference is as follows:

  • Population includes all of the elements from a data set.
  • Sample is a subset of data that includes one or more elements from the population.

Researchers and analysists operate on the standard deviation of a sample and population in different situations. For example, when summarizing the exam scores of a class of students, a teacher will use the population standard deviation. Statisticians calculating the national SAT average score would use a sample standard deviation because they are presented with the data from a sample only, not from the entire population.

Understanding the standard deviation formula

The reason the nature of the data matters is because the population standard deviation and sample standard deviation are calculated with slightly different formulas:

Sample standard deviation

Population standard deviation

Sample standard deviation formula Population standard deviation formula

Where:

  • xi are individual values in the set of data
  • x is the mean of all x values
  • n is the total number of x values in the data set

Having difficulties with understanding the formulas? Breaking them down into simple steps might help. But first, let us have some sample data to work on:
Sample data

1. Calculate the mean (average)

First, you find the mean of all values in the data set (x in the formulas above). When calculating by hand, you add up the numbers and then divide the sum by the count of those numbers, like this:

(1+2+4+5+6+8+9)/7=5

To find mean in Excel, use the AVERAGE function, e.g. =AVERAGE(A2:G2)

2. For each number, subtract the mean and square the result

This is the part of the standard deviation formula that says: (xi - x)2

To visualize what's actually going on, please have a look at the following images.

In this example, the mean is 5, so we calculate the difference between each data point and 5.
Calculating the standard deviation

Then, you square the differences, turning them all into positive numbers:
Calculating the standard deviation

3. Add up squared differences

To say "sum things up" in mathematics, you use sigma Σ. So, what we do now is add up the squared differences to complete this part of the formula: Σ(xi - x)2

16 + 9 + 1 + 1 + 9 + 16 = 52

4. Divide the total squared differences by the count of values

So far, the sample standard deviation and population standard deviation formulas have been identical. At this point, they are different.

For the sample standard deviation, you get the sample variance by dividing the total squared differences by the sample size minus 1:

52 / (7-1) = 8.67

For the population standard deviation, you find the mean of squared differences by dividing the total squared differences by their count:

52 / 7 = 7.43

Why this difference in the formulas? Because in the sample standard deviation formula, you need to correct the bias in the estimation of a sample mean instead of the true population mean. And you do this by using n - 1 instead of n, which is called Bessel's correction.

5. Take the square root

Finally, take the square root of the above numbers, and you will get your standard deviation (in the below equations, rounded to 2 decimal places):

Sample standard deviation Population standard deviation
8.67 = 2.94  √7.43 = 2.73

In Microsoft Excel, standard deviation is computed in the same way, but all of the above calculations are performed behind the scene. The key thing for you is to choose a proper standard deviation function, about which the following section will give you some clues.

How to calculate standard deviation in Excel

Overall, there are six different functions to find standard deviation in Excel. Which one to use depends primarily on the nature of the data you are working with - whether it is the entire population or a sample.

Functions to calculate sample standard deviation in Excel

To calculate standard deviation based on a sample, use one of the following formulas (all of them are based on the "n-1" method described above).

Excel STDEV function

STDEV(number1,[number2],…) is the oldest Excel function to estimates standard deviation based on a sample, and it is available in all versions of Excel 2003 to 2016.

Excel STDEV can accept up to 255 arguments that can be represented by numbers, arrays, named ranges or references to cells containing numbers.

Logical values and text representations of numbers supplied directly in the list of arguments are counted. In arrays and references, only numbers are counted; empty cells, logical values of TRUE and FALSE, text and error values are ignored.

Note. Excel STDEV is an outdated function, which is kept in the newer versions of Excel for backward compatibility only. However, Microsoft makes no promises regarding the future versions. So, in Excel 2010 and later, it is recommended to use STDEV.S instead of STDEV.

Excel STDEV.S function

STDEV.S(number1,[number2],…) is an improved version of STDEV, introduced in Excel 2010.

Like STDEV, the STDEV.S function calculates the sample standard deviation of a set of values based on the classic sample standard deviation formula discussed in the previous section.

Note. For a STDEV or STDEV.S formula to work correctly, the supplied arguments must contain at least two numeric values, otherwise the #DIV/0! error is returned.

Excel STDEVA function

STDEVA(value1, [value2], …) is another function to calculate standard deviation of a sample in Excel. It differs from the above two only in the way it handles logical and text values:

  • All logical values are counted, whether they are contained within arrays or references, or typed directly into the list of arguments (TRUE evaluates as 1, FALSE evaluate as 0).
  • Text values within arrays or reference arguments are counted as 0, including empty strings (""), text representations of numbers, and any other text. Text representations of numbers supplied directly in the list of arguments are counted as the numbers they represent (here's a formula example).
  • Empty cells are ignored.

Functions to calculate population standard deviation in Excel

If you are dealing with the entire population, use one of the following function to do standard deviation in Excel. These functions are based on the "n" method.

Excel STDEVP function

STDEVP(number1,[number2],…) is the old Excel function to find standard deviation of a population.

In the new versions of Excel 2010, 2013 and 2016, it is replaced with the improved STDEV.P function, but is still kept for backward compatibility.

Excel STDEV.P function

STDEV.P(number1,[number2],…) is the modern version of the STDEVP function that provides an improved accuracy. It is available in Excel 2010 and later versions.

Like their sample standard deviation counterparts, within arrays or reference arguments, the STDEVP and STDEV.P functions count only numbers. In the list of arguments, they also count logical values and text representations of numbers.

Excel STDEVPA function

STDEVPA(value1, [value2], …) calculates standard deviation of a population, including text and logical values. With regard to non-numeric values,  STDEVPA works exactly like the STDEVA function does.

Note. Whichever Excel standard deviation formula you use, it will return an error if one or more arguments contain an error value returned by another function or text that cannot be interpreted as a number.

Which Excel standard deviation function to use?

A variety of standard deviation functions in Excel can definitely cause a mess, especially to unexperienced users. To choose the correct standard deviation formula for a particular task, just answer the following 3 questions:

  • Do you calculate standard deviation of a sample or population?
  • What Excel version do you use?
  • Does your data set include only numbers or logical values and text as well?

To calculate standard deviation based on a numeric sample, use the STDEV.S function in Excel 2010 and later; STDEV in Excel 2007 and earlier.

To find standard deviation of a population, use the STDEV.P function in Excel 2010 and later; STDEVP in Excel 2007 and earlier.

If you want logical or text values to be included in the calculation, use either STDEVA (sample standard deviation) or STDEVPA (population standard deviation). While I can't think of any scenario in which either function can be useful on its own, they may come in handy in bigger formulas, where one or more arguments are returned by other functions as logical values or text representations of numbers.

To help you decide which of the Excel standard deviation functions is best suited for your needs, please review the following table that summarizes the information you've already learned.

  STDEV STDEV.S STDEVP STDEV.P STDEVA STDEVPA
Excel version 2003 - 2016 2010 - 2016 2003 - 2016 2010 - 2016 2003 - 2016 2003 - 2016
Sample      
Population      
Logical values in arrays or references Ignored Counted
(TRUE=1, FALSE=0)
Text in arrays or references Ignored Counted as zero
Logical values and "text-numbers" in the list of arguments Counted
(TRUE=1, FALSE=0)
Empty cells Ignored

Excel standard deviation formula examples

Once you have chosen the function that corresponds to your data type, there should be no difficulties in writing the formula - the syntax is so plain and transparent that it leaves no room for errors :) The following examples demonstrate a couple of Excel standard deviation formulas in action.

Calculating standard deviation of a sample and population

Depending on the nature of your data, use one of the following formulas in Excel 2010, Excel 2013, and Excel 2016:

  • To calculate standard deviation based on the entire population, i.e. the full list of values (B2:B50 in this example), use the STDEV.P function:

    =STDEV.P(B2:B50)

  • To find standard deviation based on a sample that constitutes a part, or subset, of the population (B2:B10 in this example), use the STDEV.S function:

    =STDEV.S(B2:B10)

As you can see in the screenshot below, the formulas return slightly different numbers (the smaller a sample, the bigger a difference):
Calculating the standard deviation of a sample and entire population in Excel

In Excel 2007 and lower, you'd use STDEVP and STDEV functions instead:

  • To get population standard deviation:

    =STDEVP(B2:B50)

  • To calculate sample standard deviation:

    =STDEV(B2:B10)

Calculating standard deviation for text representations of numbers

When discussing different functions to calculate standard deviation in Excel, we sometimes mentioned "text representations of numbers" and you might be curious to know what that actually means.

In this context, "text representations of numbers" are simply numbers formatted as text. How can such numbers appear in your worksheets? Most often, they are exported from external sources. Or, returned by so-called Text functions that are designed to manipulate text strings, e.g. TEXT, MID, RIGHT, LEFT, etc. Some of those functions can work with numbers too, but their output is always text, even if it looks much like a number.

To better illustrate the point, please consider the following example. Supposing you have a column of product codes like "Jeans-105" where the digits after a hyphen denote the quantity. Your goal is to extract the quantity of each item, and then find the standard deviation of the extracted numbers.

Pulling the quantity to another column is not a problem:

=RIGHT(A2,LEN(A2)-SEARCH("-",A2,1))

The problem is that using an Excel standard deviation formula on the extracted numbers returns either #DIV/0! or 0 like shown in the screenshot below:
Standard deviation formulas not working

Why such weird results? As mentioned above, the output of the RIGHT function is always a text string. But neither STDEV.S nor STDEVA can handle numbers formatted as text in references (the former simply ignores them while the latter counts as zeros). To get the standard deviation of such "text-numbers", you need to supply them directly to the list of arguments, which can be done by embedding all RIGHT functions into your STDEV.S or STDEVA formula:

=STDEV.S(RIGHT(A2,LEN(A2)-SEARCH("-",A2,1)), RIGHT(A3,LEN(A3)-SEARCH("-",A3,1)), RIGHT(A4,LEN(A4)-SEARCH("-",A4,1)), RIGHT(A5,LEN(A5)-SEARCH("-",A5,1)))

=STDEVA(RIGHT(A2,LEN(A2)-SEARCH("-",A2,1)), RIGHT(A3,LEN(A3)-SEARCH("-",A3,1)), RIGHT(A4,LEN(A4)-SEARCH("-",A4,1)), RIGHT(A5,LEN(A5)-SEARCH("-",A5,1)))

Calculating standard deviation for text representations of numbers

The formulas are a bit cumbersome, but that might be a working solution for a small sample. For a bigger one, not to mention the entire population, it is definitely not an option. In this case, a more elegant solution would be having the VALUE function convert "text-numbers" to numbers that any standard deviation formula can understand (please notice the right-aligned numbers in the screenshot below as opposed to the left-aligned text strings on the screenshot above):

How to calculate standard error of mean in Excel

In statistics, there is one more measure for estimating the variability in data - standard error of mean, which is sometimes shortened (though, incorrectly) to just "standard error". The standard deviation and standard error of the mean are two closely related concepts, but not the same.

While the standard deviation measures the variability of a data set from the mean, the standard error of the mean (SEM) estimates how far the sample mean is likely to be from the true population mean. Said another way - if you took multiple samples from the same population, the standard error of the mean would show the dispersion between those sample means. Because usually we calculate just one mean for a set of data, not multiple means, the standard error of the mean is estimated rather than measured.

In mathematics, the standard error of mean is calculated with this formula:

The standard error of mean formula

Where SD is the standard deviation, and n is the sample size (the number of values in the sample).

In your Excel worksheets, you can use the COUNT function to get the number of values in a sample, SQRT to take a square root of that number, and STDEV.S to calculate standard deviation of a sample.

Putting all this together, you get the standard error of the mean formula in Excel:

STDEV.S(range)/SQRT(COUNT(range))

Assuming the sample data are in B2:B10, our SEM formula would go as follows:

=STDEV.S(B2:B10)/SQRT(COUNT(B2:B10))

And the result might be similar to this:
Calculating the standard error of the mean in Excel

How to add standard deviation bars in Excel

To visually display a margin of the standard deviation, you can add standard deviation bars to your Excel chart. Here's how:

  1. Create a graph in the usual way (Insert tab > Charts group).
  2. Click anywhere on the graph to select it, then click the Chart Elements button and check the Error Bars box.

    Or, go to the Design tab > Chart Layouts group, click the Add Chart Element dropdown button, and select Error Bars from there.

  3. Click the arrow next to Error Bars, and pick a desired option:
    • Standard Error - Displays the standard error of the mean for all values.
    • Percentage - The default error range is 5%. To change it, click Error bars > More Options..., and then type the desired number in the Percentage box under Error Amount.
    • Standard Deviation- Shows the standard deviation error amount. The resulting standard deviation bars will be the same size for all values.

Adding standard deviation bars in Excel

This is how to do standard deviation on Excel. I hope you will find this information helpful. Anyway, I thank you for reading and hope to see you on our blog next week.

You may also be interested in:

5 Responses to "How to calculate standard deviation in Excel"

  1. Benard Mosira says:

    How to graph the normal distribution curve in excel 2013

  2. Imran Masud says:

    New topics, new learn. Thanks Svetlana..

  3. David says:

    Hi, I'm trying to come up with a formula that copies contents of a cell only if the value of that cell being copied is -1 or less, then converts copied negative number to a positive value. A Here's a formula I tried =if(Q2>=0,"",Q2)*-1
    (P2 is copying data from Q2) but instead if leaving cell blank it displays a 0. How do you think I should change the formula to have a cell stay blank instead of populating it with 0?

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Excel add-ins and Outlook tools - Ablebits.com
Ultimate Suite for Excel Professionals
 
 
50+ professional tools for Excel 2016-2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard