How to Calculate Standard Deviation in Excel


Home >> How to Use Excel Tutorial >> Excel Calculations >> How to Calculate Standard Deviation in Excel



In this tutorial, you will learn about how to calculate standard deviation in excel. The way of how to calculate standard deviation in excel is by using manual way with the help of various kinds of arithmetic operational signs in your keyboard or by using all of the formulas available in your spreadsheet software. There are some formulas that can be used to practice standard deviation formula in excel with each has different function depending on what is your need when wanting to get the result of the calculation process.


Why We Need to Learn About How to Calculate Standard Deviation in Excel?


In processing data for statistical needs related with numbers, often we need to know the data spread and also compare how far each of them is to the data average to know the data group fluctuation. This knowledge is important to have so we might understand: how can we trust the average as the representation of the data group or how wide is the spread of the group compared to the other groups?

To calculate the spread of the group consists of many numbers all at once, we can utilize the automatic calculation process on spreadsheet software. If we remember the formula well, maybe we can directly write standard deviation formula in excel for those numbers manually. But, if we want to know that statistic indicator faster and in a more practical way, then we can use the built-in functions available in this spreadsheet tool. If you often do number processing in spreadsheet tool and need to know your data spread for some needs, then it is very important to know how to calculate standard deviation in excel by utilizing standard deviation formula in excel, by writing it manually or using built-in formulas.


What is Standard Deviation Formula in Excel?


Standard deviation formula in excel is a calculation process to determine quantitatively the spread of a data group that we have on this spreadsheet software. To practice how to calculate standard deviation in excel, we need to differentiate first whether our data group is sample or coming from whole population. This is needed because of the different calculation process for each.


How to Calculate Standard Deviation in Excel?


The following will explain how to calculate standard deviation in excel. As explained earlier, we need to ensure first whether our data collection is just a sample or coming from whole population. The excel formula for them is different one from another. In relation to this, we will explain all of the formulas which can be used to practice how to calculate standard deviation in excel for samples and populations:
  • STDEV, standard deviation formula in excel to calculate from sample data collection by ignoring logical values or text if any
  • STDEV.S, standard deviation formula in excel to calculate from sample data collection by ignoring logical values or text if any. This is the new version of STDEV. Actually, they have the same usage objective although officially it is more recommended to use STDEV.S
  • STDEVP, standard deviation formula in excel to calculate from population data collection by ignoring logical values or text if any
  • STDEV.P, standard deviation formula in excel to calculate from population data collection by ignoring logical values or text if any This is the new version of STDEVP. Actually, they have the same usage objective although officially it is more recommended to use STDEV.P
  • STDEVA, standard deviation formula in excel to calculate from sample data collection by also processing logical values or text if any
  • STDEVPA, standard deviation formula in excel to calculate from population data collection by also processing logical values or text if any

All of those formulas’ writings are the same. They only need numbers or cell range inputs containing number to calculate their standard deviation. For the manual method and the usage of all functions above in detail will be explained step by step next.


Using How to Calculate Standard Deviation in Excel in Manual Method

  1. Prepare your numeral data to be processed in one row/column

    How to Calculate Standard Deviation in Excel - Screenshot of Step 1-1

  2. Calculate each digit deviation by:
    1. Type equal sign ( = ) in the cell besides first row/column where you put all the digits

      How to Calculate Standard Deviation in Excel - Screenshot of Step 1-2-1

    2. Type cell coordinate where you put the first number then give minus sign

      How to Calculate Standard Deviation in Excel - Screenshot of Step 1-2-2

    3. Type AVERAGE (can be with large and small letters) and open bracket sign

      How to Calculate Standard Deviation in Excel - Screenshot of Step 1-2-3

    4. Drag cell cursor from the first cell coordinate to the end of your digit row/column

      How to Calculate Standard Deviation in Excel - Screenshot of Step 1-2-4

    5. Make that cell range input to an absolute reference

      How to Calculate Standard Deviation in Excel - Screenshot of Step 1-2-5

    6. Type close bracket sign

      How to Calculate Standard Deviation in Excel - Screenshot of Step 1-2-6

    7. Press Enter
    8. Copy your formula result to its whole row/column

      How to Calculate Standard Deviation in Excel - Screenshot of Step 1-2-8

  3. Calculate squared deviation of each digit by:
    1. Type equal sign ( = ) in the cell besides first row/column where you put all the deviations of digits

      How to Calculate Standard Deviation in Excel - Screenshot of Step 1-3-1

    2. Type open bracket sign, then type cell coordinate where you put the first deviation ,then type close bracket sign

      How to Calculate Standard Deviation in Excel - Screenshot of Step 1-3-2

    3. Type top pointed arrow sign ( ^ )

      How to Calculate Standard Deviation in Excel - Screenshot of Step 1-3-3

    4. Type 2 to represent that you will square the cell coordinate content input

      How to Calculate Standard Deviation in Excel - Screenshot of Step 1-3-4

    5. Press Enter
    6. Copy your formula result to your squared deviation row/column

      How to Calculate Standard Deviation in Excel - Screenshot of Step 1-3-6

  4. On the cell where you want to put the result, type:
    1. Type equal sign ( = ) in the cell where you want to put the digits calculation process

      How to Calculate Standard Deviation in Excel - Screenshot of Step 1-4-1

    2. Type SQRT (can be with large and small letters) and open bracket sign after =

      How to Calculate Standard Deviation in Excel - Screenshot of Step 1-4-2

    3. Type SUM (can be with large and small letters) and open bracket sign

      How to Calculate Standard Deviation in Excel - Screenshot of Step 1-4-3

    4. Drag cell cursor from the first cell coordinate to the end of your squared deviation row/column then type close bracket sign

      How to Calculate Standard Deviation in Excel - Screenshot of Step 1-4-4

    5. Type slash sign ( / )

      How to Calculate Standard Deviation in Excel - Screenshot of Step 1-4-5

    6. Type COUNT (can be with large and small letters) and open bracket sign

      How to Calculate Standard Deviation in Excel - Screenshot of Step 1-4-6

    7. Drag cell cursor from the first cell coordinate to the end of your squared deviation row/column then type close bracket sign twice

      How to Calculate Standard Deviation in Excel - Screenshot of Step 1-4-7

    8. If you calculate a group of sample data, not population: you must subtract your COUNT result with 1 in line with the calculation formula for sample data collection

      How to Calculate Standard Deviation in Excel - Screenshot of Step 1-4-8

    9. Press Enter
  5. The process of how to calculate standard deviation in excel manually is done!

    How to Calculate Standard Deviation in Excel - Screenshot of Step 1-5



Standard Deviation Formula in Excel

After you see the relatively complicated way of how to calculate standard deviation in excel using the manual method, then it is probably better that you understand how to use the standard deviation formula in excel to do the calculation. The way to use all standard deviation formula in excel is the same with the difference is only on the usage function. That is why we combine their explanation into this one

  1. Type equal sign ( = ) in the cell where you want to put the calculation result

    How to Calculate Standard Deviation in Excel - Screenshot of Step 2-1

  2. Type STDEV/STDEV.S/STDEVP/STDEV.P/STDEVA/STDEVPA (can be with large and small letters) and open bracket sign after =

    How to Calculate Standard Deviation in Excel - Screenshot of Step 2-2

  3. Type the numbers or cell coordinates where you put the numbers to be processed after open bracket sign with comma sign ( , ) between the numbers or cell coordinates or drag cell cursor from the most top-left cell coordinate to the most bottom right if the number cells you want to process are close to each other

    How to Calculate Standard Deviation in Excel - Screenshot of Step 2-3

  4. Type close bracket sign after all the numbers or cell coordinates filled with numbers have already been inputted

    How to Calculate Standard Deviation in Excel - Screenshot of Step 2-4

  5. Press Enter
  6. The process of standard deviation formula in excel is done!

    How to Calculate Standard Deviation in Excel - Screenshot of Step 2-6



Exercise


After you learn the tutorial of how to calculate standard deviation in excel, you can practice your understanding to be more adept in using it by doing this exercise!

Download the file from the following link and answer the questions below. Please download the answer if you have done the calculation process and sure about the result!

Link to download exercise file:
Download here

Questions

Practice how to calculate standard deviation in excel as being told in the tutorial to get all of the answers
  1. What is the score spread if the data is sample?
  2. hat is the score spread if the data is from the whole population?
  3. What is the score spread if the data is sample and you also need to include text and logical value in the process?

Link to download how to calculate standard deviation in excel exercise answers:
Download here


Additional Note


For STDEVA and STDEVPA, every text and FALSE logical value are calculated as 0 and TRUE logical value is calculated as 1.



Want to Learn More About Excel?


Get updated excel info from Compute Expert by registering your email. It's free!


Want to Learn More About Excel?


Get updated excel info from Compute Expert by registering your email. It's free!



Follow our tutorial content also on


CONTACT US PRIVACY POLICY TERMS AND CONDITION AFFILIATE DISCLOSURE @Compute Expert