In this lesson, we are going to learn **how to calculate the standard deviation with Excel** step-by-step. It is important to remember that in statistics two formulas are used to measure the standard deviation in a given dataset depending if the data represents a population or a sample.

If you struggle to understand standard deviation or don’t quite yet get the difference between ** population and sample standard deviation** and how is calculated, I recommend you take a moment and read the article linked above.

## 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.

If you want to follow the examples in this lesson along, you can download the sample Excel worksheet ** HERE**. Alternatively, you can generate your own

**if you feel like experimenting more.**

*Excel dataset with random numbers*As you can see, we have quite a bit of ground to cover in this lesson, but I promise you, it will be fun and easy!

## 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 (

**) can be calculated.**

*x̄*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:

^{2}*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:

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

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

- On the
window, select the range of cells containing the values you want to apply the*Functions Arguments***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***)**

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

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

- On the
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*Functions Arguments***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**

**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.

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

**=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

**have an**

*3, 4, 9, 14, 22.5, 1, 3.2, 0.3, 2, and 19**average absolute deviation*of

**.**

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

## Key Takeaways

Standard deviation is an important statistical measure that helps us measure the spread of data around the mean in a given dataset. In Excel, we can find the standard deviation by using the following formulas:

- For population standard deviation:
**STDEV.P**and**STDEVPA** - For sample standard deviation:
**STDEV.S**and**STDEVA** **DEVSQ**is an Excel**AVEDEV**(*Average Absolute Deviation*) is a quick way to measure the spread around the mean by ignoring the negative sign.

**Cite this article on your website or research paper:**

[citationic]