How to Calculate Standard Deviation in Excel

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.

Calculate Mean

Step

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.

Excel Workbook With Sample Data
credit: Steve McDonnell/Demand Media

Step

Click in the cell that will display the mean value for the population and then choose the function selector, fx.

Choose Function Selector
credit: Steve McDonnell/Demand Media

Step

Change the category selection box to Statistical. Scroll through the list of functions, choose AVERAGE and then select OK.

Select AVERAGE Function
credit: Steve McDonnell/Demand Media

Step

Click the cell selector button to choose the cells that contain your data.

Click Cell Selector Button
credit: Steve McDonnell/Demand Media

Step

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.

Choose Data Cells
credit: Steve McDonnell/Demand Media

Calculate Standard Deviation

Step

Click the cell that will display the standard deviation and click the function selector, fx.

Choose Function Selector
credit: Steve McDonnell/Demand Media

Step

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.

Select Standard Deviation Function
credit: Steve McDonnell/Demand Media

Step

Click the cell selector button to select the range of values.

Select Data Cells
credit: Steve McDonnell/Demand Media

Step

Select the range of data cells or type the range in the Function Arguments box and choose OK.

Select Data Cells
credit: Steve McDonnell/Demand Media

Step

Right-click in the cell that displays the standard deviation and choose Format Cells... from the menu.

Choose Format Cells
credit: Steve McDonnell/Demand Media

Step

Select the number of decimal places you want to display and choose a format for negative numbers.

Format Standard Deviation
credit: Steve McDonnell/Demand Media

Step

Review the results to ensure they look reasonable given the data set.

Review Results
credit: Steve McDonnell/Demand Media