How to Calculate Standard Deviation in Excel
Choose from one of four standard deviation formulas in Excel and always calculate the mean or average when you calculate the standard deviation.
The divisor in the standard deviation formula is different depending on whether you want the standard deviation for a data set that represents the entire population (divide by the number of data elements minus one), or if your data set is a sample of the population, and you want to calculate the standard deviation to generalize your results to the entire population (divide by the number of data elements). As a result, Excel provides the following four functions to calculate standard deviation based on whether you want to calculate the statistic for a population or a sample, and how you want to treat text and logical values in your data set:
- STDEV.P - Calculates the standard deviation for a population and ignores logical and text values.
- STDEV.S - Calculates the standard deviation for a sample and ignores logical and text values.
- STDEVPA - Calculates the standard deviation for a population and converts "False" and logical False to zero and "True" and logical True to 1.
- STDEVA - Calculates the standard deviation for a sample and converts "False" and logical False to zero and "True" and logical True to 1.
The standard deviation represents the distribution of values around the mean, or average value. To provide the relevant context to properly interpret the standard deviation, you should always calculate and display the mean when you calculate the standard deviation.
Launch Excel and either open a workbook that has the data for which you want to calculate statistics or type the data into the blank workbook. For an example, consider a workbook with test scores for a population of 10 students that does not include any text or logical data. The workbook also contains an area in columns D and E to display the calculated statistics.
Click in the cell that will display the mean value for the population and then choose the function selector, fx.
Change the category selection box to Statistical. Scroll through the list of functions, choose AVERAGE and then select OK.
Click the cell selector button to choose the cells that contain your data.
Use your mouse to highlight the range of data cells or type the range into the Function Arguments box, for example, B2:B11. Choose OK to place the calculation in your workbook.
Calculate Standard Deviation
Click the cell that will display the standard deviation and click the function selector, fx.
Choose the STDEV.P formula to calculate the standard deviation for the entire population and to ignore text and logical values. The category selection box defaults to Statistical based on your previous selection. Choose OK to continue.
If you had a large data set of test scores and you selected a random sample to use when calculating the standard deviation as an approximation for the entire data set, you would select the STDEV.S function here instead.
Click the cell selector button to select the range of values.
Select the range of data cells or type the range in the Function Arguments box and choose OK.
Right-click in the cell that displays the standard deviation and choose Format Cells... from the menu.
Select the number of decimal places you want to display and choose a format for negative numbers.
Review the results to ensure they look reasonable given the data set.
Some data sets contain variables that are either True or False, and these variables might use a zero to represent False and a one to represent True. Excel also enables you to indicate True or False with .T. and .F. When you're calculating the standard deviation for these types of variables, use the STDEVPA function for a population or the STDEVA function for a sample of the population to include these values in the calculation.