How to Calculate Standard Deviation in Excel? 2 Simple Ways

(Note: This guide on how to calculate standard deviation in Excel is suitable for all Excel versions including Office 365)

We are familiar with Excel’s potential to solve complex mathematical and statistical problems. One such common statistical concept that is widely used in real-time is the standard deviation. From finance to trading and investments, calculating standard deviation has its uses.

In this article, you’ll learn how to calculate standard deviation in Excel using 2 simple ways.

You’ll Learn:

• What Is Standard Deviation? (With Example)
• What Is Standard Deviation in Excel?
• How to Calculate Standard Deviation in Excel?
  • By Entering the Formula
  • By Using the Formula Main Menu

Related Reads:

How to Count Cells with Text in Excel? 3 Different Use Cases

How to Calculate Percentile in Excel? 3 Useful Formulas

How to Calculate Factorial in Excel? Along with 2 Easy Examples

What Is Standard Deviation? (With Example)

Before we learn how to calculate standard deviation in Excel, let us first look at the concept of standard deviation.

You might have heard the term “Standard Deviation” from your high school math, but what does it mean? Standard Deviation is the measure of how much the value deviates from the average of the total values. 

Let me explain it in simpler terms.

Consider an example, where you have a list of 20 students who have scored certain marks in an exam. 

In the list of 20 students, the average mark scored is 91.4 and the standard deviation is 5.00. This means that most of the students have scored 5 marks more or less than the average, i.e., between 86.4 to 96.4.

The standard deviation is always expressed as ∓ (minus or plus). When the standard deviation value is smaller, the values in the list will be closer to the average. And, if the standard deviation is large, the values in the list pan out from the average. 

What Is Standard Deviation in Excel?

It is easy to calculate the average, standard deviation, and mean using mathematical formulas when there are few values and samples. When there is a lot of data, Excel has a set of built-in functions to help you arrive at the standard deviation easily.

There are 8 functions in Excel which help you calculate the standard deviation of the given data set.

• STDEV.P – calculates the standard deviation for the whole population of values without considering logical and text values.
• STDEV.S – calculates the standard deviation for the given sample of data without considering logical and text values.
• STDEVA – calculates the standard deviation for the given sample of data by taking logical and text values into account.
• STDEVPA – calculates the standard deviation for the whole population of data including the text and logical values.
• STDEV – this function is only used for reverse compatibility purposes with Excel 2007 and older versions. This function also calculates the standard deviation in the sample data ignoring the text and logical values.
• STDEVP – this function is also used for reverse compatibility purposes. This function calculates the standard deviation in the whole sample of data ignoring the text and logical values.
• DSTDEV – calculates the standard deviation for the entire population of data based on the selected database entries.
• DSTDEVP – calculates the standard deviation for the given sample of data based on the selected database entries.

The functions can be broadly classified into two: the formulas STDEV.S, STDEVA, STDEV, and DSTDEV calculate the standard deviation for the selected data sample. Whereas, the formulas STDEV.P, STDEVPA, STDEVP, and DSTDEVP calculate the standard deviation for the whole data population. 

The term “population” refers to the total dataset, i.e., the function takes all the data points (N) in the data set. The term “sample” refers to a part of the population and the function takes N-1 data points when it calculates the standard deviation. You can use either of the formulas to calculate the standard deviation of the population or the sample. 

Suggested Reads:

How to Use CONVERT Function in Excel? A Step-by-Step Guide

How to Use the Excel TREND Function? A Step-by-Step Guide

How to Calculate SUBTOTAL in Excel? 2 Useful Methods

How to Calculate Standard Deviation in Excel?

There are two ways to calculate standard deviation in Excel. One is by entering the formula in the cells and the other is by using the formula main menu. Let us see each method in detail.

By Entering the Formula

This is a very common method where you can enter the formula in the destination cell and get the results.

Syntax

First, let us see the syntax.

All the formulas to calculate standard deviation in Excel has the same syntax. Let us see the syntax for the STDEV.S function.

=STDEV.S(num1,[num2]…)

Where num1 is the first element of the sample or population. It can be a constant, variable referring to a cell, a cell range, or an array. It is a mandatory argument in the function.

The num2 variable is an optional argument. It can also be a constant, an array, or a variable that denotes another cell. And, the function can take up to 254 additional arguments. 

• First, select the destination cell to use the standard deviation formula in real-time.

• Enter the formula =STD.

• Once you enter the initials of the formula, Excel shows you a dropdown from where you can choose the formula you want. 

• Depending on your choice, you can either the formula by double-clicking on the desired formula or by using the keyboard arrow keys to navigate and press the Tab key.

• Then, you can enter the arguments. Remember, only the first argument is mandatory and the rest of the arguments are optional. In this case, let us pass the first argument as the cell range B4 to B23 as it contains the data set for which we want to calculate the standard deviation. 

• Press Enter.

• This gives the standard deviation of the data in the given range of cells.

Note: The functions STDEV.S and STDEV.P give different results even though you take the same range and values.

By Using the Formula Main Menu

Excel has a built-in function that you can select and use for calculating standard deviation.

• First, select a destination cell. 

• Navigate to Formulas in the main menu. Under the Function Library section, click on the dropdown from More Functions. Under Statistical formulas, scroll down and select the standard deviation formula to use.

• In this case, let us select the STDEV.S function. 

• This opens up a Functions Argument dialog box. In the dialog box, pass the arguments.

• In this case, select the cells which contain the data B4:B23 or enter them in the dialog box.  

• You can see the details of the arguments and the preview of the result in the Function Arguments dialog box. 

• Click OK.

• This gives you the standard deviation for the selected set of data.

Also Read:

How to Use Cell Styles in Excel: A Step-by-Step Guide

How to Create a Funnel Chart in Excel? 2 Useful Ways

How to Insert a Hyperlink in Excel? 3 Easy Ways

Frequently Asked Questions

Closing Thoughts

Calculating the standard deviation gives you an estimate of how much the values deviate from the average. This will be especially helpful when the data are at large.

In this article, we saw how to calculate standard deviation in Excel in 2 different ways along with the different formulas and their functionalities. Depending on your purpose, you can choose the formula and calculate the standard deviation.

If you need more high-quality Excel guides, please check out our free Excel resources center. Simon Sez IT has been teaching Excel for over ten years. For a low, monthly fee you can get access to 140+ IT training courses. Click here for advanced Excel courses with in-depth training modules.

Simon Calder

Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA.He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!