How to Calculate a Standard Deviation in Excel for a Sample

Microsoft Excel, Standard Deviation 5 Comments

Microsoft Excel has a wealth of statistical tools you can use in probability and statistics, including formulas to calculate a standard deviation.  The standard deviation is represented by the lowercase sigma (σ) symbol, and shows how much dispersion, or variation, exists in the data from the mean. The larger the standard deviation, the more spread out the data. Computing a standard deviation by hand is complicated and prone to errors. To calculate a standard deviation in Excel, you must first enter your data.

Sample question: Calculate the standard deviation for the following data set: 22,55,37,46,67,37,95,76,99,28

A standard deviation tells you how spread out your data is from a central point (the mean).


Step 1: Enter your data into Excel in single column. For this example, type your data into cells A1 through A10. To calculate a standard deviation in Excel, your entries must be in a single column without gaps.
Step 2: Click a blank cell anywhere on the worksheet and then type “STDEV(A1:A10),” — without quotation marks — where A1:A10 is the location of your data. If you placed your data in another column, make sure you change the formula. For example, if your data is in cells C3 through C13, then change the formula to read “STDDEV(C3:C13)”.
Step 3: Press “Enter.” The standard deviation for this particular data set is 27.189867393734907.
That’s it!

Tip: There is another way you can calculate a standard deviation in Excel. If you have the Data Analysis ToolPak installed, you could run “Descriptive Statistics,” which would calculate the standard deviation.
Tip: You could also type “STDDEV(22,55,37,46,67,37,95,76,99,28)” into a blank cell and get the same result. But if you are planning to do any other calculations with your data, it’s best to input it in cells.
Warning: STDDEV assumes you are working with samples. If your data represents the entire population, use STDEVP instead.

Notes for Mac Users:
For Standard Deviation for a Population use:
STDEV.P(A1:A10)
For Standard Deviation of a Sample use:
STDEV.S(A1:A10)

Feel like Cheating at Statistics? This is the Statistics Handbook that your professor doesn't want you to see. So easy, it's Practically Cheating. Find out more »
Need more help? Ask on our free forums »

You can follow any responses to this entry through the RSS 2.0 feed. Responses are currently closed, but you can trackback from your own site.

5 Responses to “How to Calculate a Standard Deviation in Excel for a Sample”

  1. Joelle said:

    Aug 08, 12 at 8:04 pm

    Thanks so much for this post. It helps tremendously. Just a note…not sure if this is an update for Excel in general or if it is unique to Office 2011 for Mac. The automatic formula for Excel 2011 for Mac for Standard Deviation is as follows:

    Type your numbers into a row the way you’ve outlined above.

    If you are finding Standard Deviation for a Population use:
    STDEV.P(A1:A10)

    For Standard Deviation of a Sample use:
    STDEV.S(A1:A10)

    Thanks so much for this site. It truly is a step by step easy to follow explanation of Statistics. It is really too bad that Statistics is not taught this way in school…instead of being made overly complicated.

    Quick Question on the Handbook…Is it made more for PC users (at least the excel portions)? If so, is there an updated version (or side notes) for MAC users? This will probably not impact whether I buy the book…but if there is something coming down the pike soon – it may impact the timing. If you don’t have the time to update for Mac but would benefit by having the formulas – I’d be happy to be your guinea pig. I am taking statistics first quarter and have a Mac. So no matter what – I’ll be making whatever conversions are necessary to do well in the class.

    Take care,

    Joelle

    Thanks again!

  2. Andale said:

    Aug 11, 12 at 9:51 am

    Joelle,

    Thanks for your compliments on the site — I am glad it helps!

    I don’t have a Mac, and actually haven’t had this question before. I am working on a series of Excel articles as I write this, and I’ll make sure to address any differences I find with the Mac. The book itself only has one or two Excel articles (I’m working on expanding that).

    Regards,
    Stephanie

  3. Andre said:

    Aug 28, 12 at 11:22 am

    I’ve done what you did in the example for my employer. How do I explain to him what it means that we have a 24.6 standard deviation of a sample from our 30 product?

  4. What is Standard Deviation? | said:

    Nov 10, 12 at 9:17 am

    [...] or, calculate the standard deviation in Microsoft Excel [...]

  5. Microsoft Excel for Statistics | said:

    Jan 25, 13 at 8:49 am

    [...] How to Calculate a Standard Deviation in Excel for a Sample [...]