How To Calculate The Standard Deviation With Excel

Lesson Outcomes

In a nutshell, here is what we are going to cover in this lesson:

• How to estimate the population and sample standard deviation in a worksheet using the various sample standard deviation Excel formulas.
• Other related Excel functions to extract deviation for particular cases.

Population Standard Deviation With Excel

Two functions are available to calculate the population standard deviation with Excel, respectively: STDEV.P and STDEVPA.

The population standard deviation is used to measure the spread of a group of values relative to the mean in a dataset. In this case, it is assumed that a dataset contains values representative of the whole population (N) therefore the population mean (x̄) can be calculated.

In statistics, the symbol used for population standard deviation is the lower-case Greek letter Sigma (σ) and the formula is derived from the square root of the population variance (√σ²). Here is what the population sample formula looks like:

\sigma=\sqrt{\frac{\sum\left(x_{i}-\mu\right)^{2}}{N}}

Where:

σ = symbol for population standard deviation

Σ = sum of the following terms

x_i = every point in the dataset (observation or member of the population).

μ= population mean

N = the number of values in the population

Next, let’s learn how to use the STDEV.P and STDEVPA standard deviation functions for the population in Excel.

• The STDEV.P Function

As the name implies, the STDEV.P function in Excel is an acronym for standard deviation (STDEV) and population (.P). Quite easy to remember right? 

The STDEV.P Excel standard deviation formula for population (N) selects only the values in our data array and ignores the text or other logical values. The STDEV.P Excel syntax looks like this:

=STDEV.P(data array)

Assuming we want to calculate the population standard deviation for the set of values 3, 4, 9, 14, 22.5, 1, 3.2, 0.3, 2, and 19 in Excel, do the following:

1. Select an empty cell in the Excel worksheet and click the Insert Function (fx) icon: 

2. On the Insert Function window, search for the STDEV.P function, select it, click Go then the OK buttons.

3. On the Functions Arguments window, select the range of cells containing the values you want to apply the STDEV.P formula. The population standard deviation result will be displayed in the same Function Arguments window as pointed by the green arrows below. Click OK.

Finally, you can modify the values in the selected cells as you like and the STDEV.P will be applied instantly. 

Alternatively, you can insert the STDEV.P formula by simply selecting an empty cell, typing the =STDV.P followed by your cells selection (e.g., C2:C11) directly in the Insert Function field in Excel.

• The STDEVPA Function

The second standard deviation Excel formula for the population is STDEVPA and has the syntax: 

=STDEVPA(data array)

To use the STDEVPA function in Excel follow the same steps as for the STDEV.P formula above but in the Insert Function window, search for the STDEVPA function instead. 

As you see, the population standard deviation result for the numbers 3, 4, 9, 14, 22.5, 1, 3.2, 0.3, 2, and 19 using STDEV.P and STDEVPA Excel functions is identical, respectively 7.59.

Sample Standard Deviation With Excel

Excel offers us two functions to calculate the sample standard deviation, respectively STDEV.S and STDEVA. 

As with most cases in research, collecting data from an entire population would not only be expensive but extremely tedious. Fortunately for us, we can sample a large population and use the following formula to extract the sample standard deviation:

s=\sqrt{\frac{\sum\left(x_{i}-\bar{x}\right)^{2}}{N-1}}

Where:

s = symbol for sample standard deviation

Σ = sum of the following terms

x_i = every point in the dataset (observation or member of the population).

x̄ = sample mean

N-1 = the number of values in the sample minus 1

At a glance, we can observe that the population and sample standard deviation equations have quite a bit in common, the exceptions being the symbol used for sample standard deviation (s), the mean of the sample (x̄), and the number of members in the sample (N – 1).

Next, let us see how we can find the sample standard deviation for the set of numbers 3, 4, 9, 14, 22.5, 1, 3.2, 0.3, 2, and 19 using the STDEV.S and STDEVA functions in Excel.

• The STDEV.S Function

The STDEV.S sample standard deviation formula in Excel has the following syntax: 

=STDEV.S(data array)

1. Click on an empty cell in the Excel worksheet and select the Insert Function (fx) indicator: 

2. Search for the STDEV.S function in the Insert Function window, select it then click the Go button. Finally, press OK. 

3. On the Functions Arguments dialog box, select a range of cells in your worksheet (e.g., C2:C11). The sample standard deviation result will be displayed in the same dialog box. Finally, click the OK button to close the window.

There you go! The sample standard deviation for the values 3, 4, 9, 14, 22.5, 1, 3.2, 0.3, 2, and 19 using the STDEV.S function is 8.00.

• The STDEVA Function

The second sample standard deviation function in Excel is STDEVA and has the syntax:

=STDEVA(data array)

To use the STDEVA formula in Excel follow the same steps as with the STDEV.S function above. However, on the Insert Function window, search for the STDEVA function instead. 

As you can observe, both STDEV.S and STDEVA sample standard deviation Excel formula have the same output: 8.00

If you want to cut some corners and speed up the analysis process a bit, you can simply type one of the functions directly into the Insert Function filed, e.g., =STDVA(C2:C11). Replace the data array (C2:C11) with your own cell range.

One important thing to keep in mind is that when you analyze the standard deviation for a sample, you also need to keep an eye on the standard error. This is to make sure your sample is representative of the population under investigation. 

Here is a good read on how to find the standard error in Excel, SPSS, and R in a few simple steps. 

Or if you feel like exploring, here is how to calculate the standard deviation on R for statistics and data science purposes. I am sure you will enjoy this one!

Related Deviation Functions

There are thee functions in Excel that can be considered to be related to standard deviation, though their use is somehow less frequent. These functions are DEVSQ and AVEDEV

1. DEVSQ is an Excel function that just extracts the sum of deviations from the mean. In some rare cases, we might need to find the root square deviation for a sample without dividing it to N or N-1. The Excel syntax for DEVSQ functions is 

=DEVSQ(data array).

As with previous examples, you can find the root square deviation from the mean function in Excel by clicking on the Insert Function (fx) icon and searching for DEVSQ. Once selected, all you need to do is to select the range of cells you want to analyze.

For instance, for the set of numbers, we used in this lesson, the root square deviation is 576.18 as seen in the picture below.

2. AVEDEV stands for Absolute Average Deviation and another Excel function that handles deviation in a different way than squaring and has the following syntax:

=AVEDEV(data array)

So far, in each standard deviation Excel formula we covered squared negative deviations before averaging them to get positive results. The AVEDEV function ignores the minus sign before calculating the mean hence the name average absolute deviation. 

Similar to standard deviation and variance, a large absolute deviation shows a high spread around the mean, while a low absolute deviation shows low dispersion. 

In Excel, you can find this function by clicking on the Insert Function (fx) icon and searching for the AVEDEV function. For example, the numbers 3, 4, 9, 14, 22.5, 1, 3.2, 0.3, 2, and 19 have an average absolute deviation of 6.66.

You are now equipped with everything it needs to calculate the standard deviation with Excel. Great job!