# How to Calculate Standard Deviation in Excel

**Standard deviation** is a number that tells you how far numbers are from their mean.

There are **basically** two type of standard deviation that is being used in **excel:**

- Standard
**deviation**based on samples (STDEV.S). - Standard deviation
**based**on**population**(STDEV.P).

## Standard deviation based on Samples (STDEV.S)

The **STDEV.S** function (the S stands for Sample) in Excel estimates the standard deviation based on a sample. The STDEV.S function uses the following **formula:**

**Maybe you interest:**

Where,

Now let’s **construct** an example, imagine there was a class test of 10 students and we take 5 samples out of the entire 10 students to check **standard deviation.** The class tests marks of those 5 students are 7, 9, 12, 8, and 14. Now firstly we will use the formula and then we will verify the same in excel without using any **formula.**

- In a new
**workbook**of**excel,**write marks of sample students under any heading. You will get something like this

- Now in cell
**A8**we will use our**formula,**to use formula on my worksheet I will use :-

“”*=STDEV.S(A2:A6)*

- Now we will manually
**verify**the**formula**used. For this we will repeat the first step in sheet 2. - Now in cell A8, we will find the average of sample by using the
**following formula:-**

“”*=AVERAGE(A2:A6)*

- In column B we will find xi – x , for this we will subtract marks from
**average.**And in the end we will get a**product**like this:

- Now multiply the difference with itself to get the squared
**value**of**difference**and then have a sum of such value

- Now in next cell we will complete the
**formula**that is**dividing**the sum of square of difference that is 34 with no of sample minus one that is 5-1 = 4 and then square root of any value that come from such division.

**Tips:**

- Steps from 3 to 7 are just for
**manually**checking whether**formula**works fine or not. You need not to repeat step3 to step 7. - In office 2007 or
**earlier versions,**STDEV was used. STDEV will deliver the same result as STDEV.S

## Standard deviation based on population (STDEV.P)

The **STDEV.P** function (the P stands for Population) in Excel **calculates** the standard deviation based on the entire population. The STDEV.P function uses the following **formula:**

Where,

Now let’s **construct** an example, imagine there was a class test of 5 students. The class tests marks of those 5 students are 7, 9, 12, 8, and 14. Now firstly we will use the formula and then we will verify the same in **excel** without using any **formula.**

- In a new workbook of excel, write marks of all
**students**under any**heading.**You will get something like this:

- Now in cell A8 we will use our
**formula,**to use formula on my**worksheet**I will use :-

“”*=STDEV.P(A2:A6)*

- Now we will manually
**verify**the**formula**used. For this we will repeat the first step in sheet 2 - Now in cell A8, we will find the average of sample by using the
**following formula:-**

“”*=AVERAGE(A2:A6)*

- In column B we will find xi – u, for this we will
**subtract**marks from**average.**And in the end we will get a product like this:

- Now multiply the
**difference**with itself to get the squared value of difference and then have a**sum**of**such**value

- Now in next cell we will
**complete**the**formula**that is dividing the sum of square of difference that is 34 with total population and then square root of any value that come from such**division.**

**Tips:**

- Steps from 3 to 7 are just for
**manually checking**whether formula works fine or not. You need not to repeat step3 to step 7. - In office 2007 or earlier versions,
**STDEVP**was used. STDEVP will deliver the same**result**as STDEV.P