Standard Deviation is calculated to find out the deviation amongst a sample data taken from a large data set in regression analysis, for excel 2007 and older versions we had an inbuilt formula for standard deviation as STDEV but in excel 2010 and newer versions we have inbuilt formulas STDEV.P which calculates standard deviation based on a population or STDEV.S which calculates Standard deviation based on a sample.

**Standard Deviation Formula in excel (Table of Contents)**

## Excel Standard Deviation Formula

Standard deviation calculation and its formula helps us to visualize how much the data is spread from the mean itself and from the data. For example, four children have apples with them. A has two apples B has three C has four and D have six apples. The mean or the average for the apples is 3.75 apples each person but the standard deviation for the above set of data is 1.7 which means the deviation in the data is 1.7. Standard Deviation formula in Excel is used to see whether the data we have is standard or unusual.

### Explanation of Standard Deviation Formula in Excel

There are types of standard deviation,

**#1 – Low Standard Deviation:** It means data is very much closely spread around the average of the data.

**#2 – High Standard Deviation:** It means the data is spread wider throughout the data.

In excel there are two formulas provided by excel for standard deviation:

**p(Set of Data):**This formula is used to calculate the standard deviation formula in excel over the whole set of data. Here P stands for the population. Population means the total set of data.**s(Set of Data):**This formula is used to calculate the standard deviation formula in excel over a sample of data. Here S stands for the sample. Sample means a sample taken from any data set.

In the above screenshot where we saw the arithmetic formula for standard deviation, the formula is for Standard deviation for a sample where the values are divided by N-1. In Standard Deviation for Entire population, the standard deviation is calculated by division of only N. Why we divide by n-1 because it gives us a better view of the deviation in the data.

Now let us understand what is the main difference is in between STDEV.P and STDEV.S in standard deviation. For example, we have marks for five students, Anand, Aran, Vikas, Neeraj, and Rahul. And these are the total number of students in my class so the standard deviation is calculated over the whole population i.e. the whole set of data we have. But if we have a very large number of students and have marks of only these five students then the standard deviation is calculated by considering it a sample as we have only a sample amount of data.

Standard deviation helps us to determine what will be the spread of the data in a given data set.

**How to Use Standard Deviation Formula in Excel?**

To use standard Deviation Formula in Excel we need to have a data set first.

Now follow the following steps to calculate the standard deviation formula in excel.

1. If the data set we have is the total data set we have then use the formula STDEV.P and select the data set as seen in the screenshot below.

4.9 (1,353 ratings)

2. If the data set we have is a sample of the total data set then we use the formula for sample i.e. STDEV.S and select the data set as seen in the screenshot below.

The above two formulas are the formulas in excel to calculate the standard deviation of a data set.

Now as we have an explanation of what a standard deviation is let us try to use these formulas in excel with few examples.

### Example #1

Let us consider the very first example we took above which was A has two apples B has three apples where C has four apples and D has six apples. The data set for this statement look like the screenshot below:

We will use both the formulas STDEV.P and STEDEV.S considering once that it is our total data and once that it is a sample set of data.

**Step #1 –** In cell D2 write the formula below,

**Step #2 –** Select the cell range B2:B5 for the data set.

**Step #3 –** When we press enter we can see that the standard deviation for the population is calculated.

**Step #4 –** Now let us calculate the standard deviation for the sample in cell D2. Write the following formula.

**Step #5 –** Select the cell range B2:B5 for the sample data set.

**Step #6 –** When we press enter we can see that the standard deviation for the sample set of data is calculated.

### Example #2

Now let us use the second example we took for the total marks of students to calculate the standard deviation. Anand scored a total of 90 marks where Aran scored 95 Vikas scored 80 Neeraj scored 75 and Rahul scored 50. The data set for the above example is as follows:

We will use both the formulas STDEV.P and STEDEV.S considering once that it is our total data and once that it is a sample set of data.

**Step #1 –** In cell D2 write the following formula,

**Step #2 –** Select the data set we have i.e. cell B2:B6.

**Step #3 –** When we press enter we can see that the standard deviation for this data is calculated.

**Step #4 –** Now in cell D3, we will calculate the standard deviation for the sample. Write the following formula,

**Step #5 –** Select the cell range B2:B6 for the sample set of data.

**Step #6 –** When we press enter we can see that the standard deviation for the sample amount of data is calculated.

**Things to Remember about Standard Deviation Formula in Excel**

- There are two types of formulas for standard deviation STDEV.P and STDEV.S. STDEV.P is used to calculate the standard deviation for the entire population whereas STDEV.S is used to calculate the standard deviation for a sample set of data.
- There are two types of standard deviation, High and Low. High means the deviation is spread through the average whereas low means the deviation is near the average.
- Standard Deviation for Sample or STDEV.S is considered the most accurate way to calculate the standard deviation.

### Recommended Articles

This has been a guide to Standard Deviation Formula in Excel. Here we discuss how to use the standard deviation formula (STDEV.P and STDEV.S) in Excel along with practical examples and downloadable excel template. You may learn more about excel from the following articles –

- Examples to Create Formula in Excel
- Standard Deviation Formula
- ROUND Formula in Excel (Using Roundown)
- Create a Standard Deviation Graph in Excel
- Variance vs Standard Deviation – Compare
- Portfolio Standard Deviation Calculation
- Use NORMDIST in Excel

- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion