In this article, youâ€™ll learn how to calculate standard deviation using formula in Excel. Weâ€™ll calculate both the sample standard deviation and the population standard deviation. For that purpose, weâ€™ll use 6 built-in functions that are **STDEV**, **STDEV.S**, **STDEVA**, **STDEVP**, **STDEV.P** and **STDEVPA** functions.

Youâ€™ll also learn how to calculate variance using different formulas in Excel. Variance is the square of the standard deviation. Weâ€™ll calculate both the sample variance and the population variance. Here, weâ€™ll also use 6 built-in functions that are **VAR**, **VAR.S**, **VARA**, **VARP**, **VAR.P** and **VARPA** functions.

In the last part of this article, youâ€™ll see how to insert the standard deviation error bars in Excel. These bars will represent the standard deviation margin in a chart.

Standard deviation measures the variation of a dataset. You can compare similarities or dissimilarities of data using standard deviation in Excel.

Weâ€™ll use the Excel 365 version in this article. You can also use other versions of Excel for the same purpose.

**Table of Contents**Expand

## Why Use Standard Deviation Formula in Excel?

You can use standard deviation formulas in Excel to calculate the standard deviation. Business analysts frequently utilize the standard deviation as an estimate for investment risk in reality. If the standard deviation is high, itâ€™ll return a volatile result.

**Download Practice Workbook**

## What is Standard Deviation in Excel?

The standard deviation measures how much the values of a set of data spread out from their average value. When the standard deviation is closer to zero, the data variability is lower. When the standard deviation is higher, there is more variation in the data.

There are 2 types of standard deviation calculation. Population standard deviation includes the entire contents of a data set. The sample standard deviation includes only an element or elements from the whole set. The population standard deviation is what a teacher will use to summarize the exam results of a class of students. We use the sample standard deviation to determine the average SAT score for a country.

## What Are the Formulas for Sample and Population Standard Deviation and How do They Work?

## Why Calculating Standard Deviation Is Useful in Excel?

- The standard deviation can give important information about how concentrated the data is. It displays whether or not a datasetâ€™s numbers are dispersed or grouped together.
- A high standard deviation indicates that the data points are dispersed throughout a larger range. A low standard deviation indicates that the data points are all closer to the average.
- If you have to calculate the standard deviation manually, it might take a while. But you can do it fast and effectively by using the formulas in Excel.
- You can link your dataset to the functions to compute the standard deviation in Excel. So, when you change your data, the calculation changes automatically.

## What Are the Uses of the Standard Deviation in Excel?

- Marketers use the standard deviation to predict the range of revenues theyâ€™ll get in the future.
- Weather forecasters use standard deviation to calculate the margin of error in their forecasts.
- Financial analysts use the standard deviation to analyze investments and portfolios.
- Other professionals like statisticians, market researchers, insurance analysts, teachers, and real estate agents use the standard deviation in their respective fields of work.

**How Many Methods Are There to Calculate Standard Deviation Using Formula in Excel?**

There are 2 methods to calculate standard deviation using formula in Excel. You can calculate the sample standard deviation using:

1. **STDEV**,

2. **STDEV.S **and**
**3.

**STDEVA**functions.

You can determine the population standard deviation using:

1.

**STDEVP**,

2.

**STDEV.P**and

3.

**STDEVPA**functions.

**1. Calculate Sample Standard Deviation Using STDEV, STDEV.S and STDEVA Functions**

Here, we calculate the sample standard deviation using the **STDEV**, **STDEV.S** and **STDEVA** functions.

- Write the following formulas into cells
**C16**,**C17**and**C18**respectively:

`=STDEV(C5:C14)`

`=STDEV.S(C5:C14)`

`=STDEVA(C5:C14)`

**Note**

- The older versions of Excel still provide the out-of-date
**STDEV**function only for backward compatibility. So, if you are using Excel 2010 and later you should use the**STDEV.S**function instead of the**STDEV**function. - The
**STDEVA**function includes text and logical values in the calculation. But the**STDEV**and**STDEV.S**functions exclude them. The**STDEVA**function takes FALSE as 0, and TRUE as 1. It takes textual values as 0. - In Excel 2007 and later, the STDEV function can take up to 255 arguments. In Excel 2003, the function can take up to 30 arguments.
- The supplied arguments in the functions must contain at least two numeric values, otherwise, youâ€™ll get the
**#DIV/0!**error.

**2. Calculate Population Standard Deviation Using STDEVP, STDEV.P and STDEVPA Functions?**

Here, we calculate the population standard deviation using the **STDEVP**, **STDEV.P** and **STDEVPA** functions.

- Type the formulas into cells
**C16**,**C17**and**C18**respectively:

`=STDEVP(C5:C14)`

`=STDEV.P(C5:C14)`

`=STDEVPA(C5:C14)`

**Note**

- The
**STDEVP**function is out of date. Therefore, you should use the**STDEV.P**function rather than the**STDEVP**function if you are using Excel 2010 or later. - The
**STDEVPA**function includes text and logical values in the calculation. However, the**STDEVP**or**STDEV.P**functions do not include them.Â The**STDEVPA**function takes FALSE as 0, and TRUE as 1. It takes textual values as 0.

**Read More:** Calculate Population Standard Deviation

## Which Standard Deviation Function to Use in Excel?

**How Many Methods Are There to Calculate**** Variance Using Formula in Excel?**

There are 2 methods to calculate variance using formula in Excel. You can calculate the sample variance using:

1. **VAR**,

2. **VAR.S **and**
**3.

**VARA**functions.

You can determine the population variance using:

1.

**VARP**,

2.

**VAR.P**and

3.

**VARPA**functions.

**1. Calculate Sample Variance Using VAR, VAR.S and VARA Functions?**

Here, we calculate the sample variance using the **VAR**, **VAR.S** and **VARA** functions.

- Enter the formulas in cells
**C16**,**C17**, and**C18**, accordingly:

`=VAR(C5:C14)`

`=VAR.S(C5:C14)`

`=VARA(C5:C14)`

**Note**

- Use the
**VAR.S**function rather than the**VAR**function if you are using Excel 2010 or later. - The
**VARA**functionâ€™s calculation takes both textual and logical data into account. However, neither**VAR**nor**VAR.S**functions include them.

**Read More:** How to Calculate Mean and Standard Deviation in Excel

**2. Calculate Population Variance Using VARP, VAR.P and VARPA Functions?**

Here, we calculate the population variance using the **VARP**, **VAR.P** and **VARPA** functions.

- In cells
**C16**,**C17**, and**C18**, respectively, enter the formulas:

`=VARP(C5:C14)`

`=VAR.P(C5:C14)`

`=VARPA(C5:C14)`

**Note**

- If you are using Excel 2010 or later, use the
**VAR.P**function instead of the**VARP**function. - The calculation of the
**VARPA**function considers both logical and textual data. However, the**VARP**or**VAR.P**functions do not include them.

**Read More:** How to Calculate Standard Deviation of a Frequency Distribution in Excel

**How to Insert Standard Deviation Error Bars in Excel?**

You can insert standard deviation error bars in Excel easily using a Line chart.

- Select the whole dataset and go to
**Insert**>**Insert Line or Area Chart**. - Choose the chart titled
**Line with Markers**.

- Youâ€™ll see a
**line chart**in your Excel sheet. - Select the
**line**on the chart and click on the**plus**icon beside the chart. - Youâ€™ll find the
**Chart Elements**pan. Select the**pointer**icon beside the**Error Bars**option. - Then, choose
**Standard Deviation**. - Your chart now has the standard deviation error bars present.

- We formatted the chart so that the standard deviation error bars look nice and tidy.

**Which Things You Have to Remember?**

- The supplied arguments from a dataset must have at least two numerical values for the standard deviation formulas to function properly. Otherwise, youâ€™ll see a
**#DIV/0!**error. - In Excel 2010 and later, use the
**STDEV.S**function to determine the standard deviation of a sample. - In Excel 2007 and earlier, use the
**STDEV**function to determine the standard deviation of a sample. - Use the
**STDEV.P**function in Excel 2010 and later to determine the standard deviation of a population. - Use the
**STDEVP**function in Excel 2007 and previous, to determine the standard deviation of a population. - Use the
**STDEVA**function (sample standard deviation) or the**STDEVPA**function (population standard deviation) if you want to use logical or text values in the calculation.

**Frequently Asked Questions**

### 1. What are the thresholds for standard deviation?

Each standard deviation reflects a particular proportion of the data. It is called a normal distribution. The percentage representation of the data for each standard deviation is:

- One standard deviation reflects about 68% of the data.
- Two standard deviations reflect about 95% of the data.
- Three standard deviations reflect about 99.7% of the data.

### 2. Can you find a confidence interval in Excel?

Yes, you can find a confidence interval in Excel using the **CONFIDENCE.T** function. The syntax of the formula is:

**=CONFIDENCE.T(alpha,standard_dev,size)**

Here,

alpha is the significance level used to compute the confidence level. standard_dev is the standard deviation and size is the sample size of the data.

### 3. Should I use STDEV.S or STDEV.P in Excel?

**Answer:** When you are calculating the standard deviation of a sample you should use the **STDEV.S** function. But when you are using an entire population as your dataset then you should use the **STDEV.P** function.

### 4. Which standard deviation is better?

**Answer:** A low standard deviation implies that the data are grouped around the mean, whereas a large standard deviation shows that the data are more dispersed. In contrast, a high or low standard deviation indicates that the data points are, respectively, above or below the mean. A standard deviation that is close to zero implies that the data points are close to the mean. So, low standard deviation is better.

### 5. What is the difference between standard deviation and variance?

**Answer:** The standard deviation calculates the variance of the data setâ€™s numbers. In contrast, variance assigns an exact value to how far the data setâ€™s numbers deviate from the mean.

**Conclusion**

In conclusion, you have learned toÂ calculate standard deviation using formula for both population and sample data in Excel through this article. For this, we use the **STDEV**, **STDEV.S**, **STDEVA**, **STDEVP**, **STDEV.P** and **STDEVPA** functions. You can calculate variance using the **VAR**, **VAR.S**, **VARA**, **VARP**, **VAR.P** and **VARPA** functions in Excel. You can insert the standard deviation error bars also. In a variety of disciplines, including business and statistics, these computations are crucial for data analysis and risk assessment. When you examine a complete dataset, you can use the population standard deviation. When only a subset of the population is available, you can use the sample standard deviation.

**Standard Deviation Formula in Excel: Knowledge Hub**

- Calculate Standard Deviation with IF Conditions
- Calculate Percentile from Mean and Standard Deviation
- How to Calculate Uncertainty in Excel
- How to Calculate Standard Deviation of y Intercept in Excel

**<< Go Back toÂ Excel for Statistics**

**Â |Â Learn Excel**