In this Excel tutorial, you will find ways to calculate Standard Deviation using different arithmetic formulas in Excel. Here, we’ll discuss how you can calculate both Sample Standard Deviation and Population Standard Deviation. To calculate Sample Standard Deviation you can use the **STDEV**, **STDEV.S** or **STDEVA **function and for Population Standard Deviation use the **STDEVP**, **STDEV.P** or **STDEVPA** function. In addition, you can also learn how to calculate Mean and Standard Error in Excel from this article.

Here, we’ll use **Microsoft** **365** in this article.

Calculating deviation in Excel will give you an idea about how the data points are spread around a reference point. It will help you to make predictions of certain results and estimate the consistency of the data.

**Download Practice Workbook**

**Table of Contents**Expand

## What Is Deviation?

Deviation is the difference between an observed or measured value and a reference or expected value. In statistics, there are a few common types of deviation. Standard deviation is one of them.

Standard deviation is a measure of how spread out the values in a dataset are around the mean (average).

## What Is Sample and Population Standard Deviation?

**Sample Standard Deviation:** When you are calculating the standard deviation of a sample from the whole population it is called Sample Standard Deviation. It includes only a subset of the total data points.

The formula to calculate Sample Standard Deviation:

**Population Standard Deviation:** When you are calculating the standard deviation of the whole population it is called Population Standard Deviation. It includes the total data points of a dataset.

The formula to calculate Population Standard Deviation:

## Where Do We Use Standard Deviation?

Standard deviation is a helpful tool used in many areas. It is commonly used in statistics to analyze and describe the spread of data points around the mean, providing knowledge about the consistency or variability of a dataset. Standard deviation is also used in finance and investment analysis to measure risk or volatility, helping investors estimate the potential variability of returns.

## How to Calculate Standard Deviation in Excel?

There are different Excel functions such as **STDEV**, **STDEV.S**,** STDEVP** etc. functions using which you can calculate the standard deviation.

Here, we have a dataset containing the **names **and **scores **of some students. Now, we’ll show you how to calculate the sample and population standard deviation of these scores in Excel.

### 1. Calculate Sample Standard Deviation

You can use **STDEV**, **STDEV.S** or **STDVA **functions to calculate sample standard deviation in Excel.

#### 1.1 Using STDEV Function

Excel **STDEV **function is the oldest function that is available from **Excel 2003** to **2019**. It counts any numerical values or a text representation of numbers directly and counts only numbers in arrays and references. However, it ignores empty cells, logical values (TRUE/ FALSE), text, and error values.

You can use the following formula to calculate the sample standard deviation in your dataset.

`=STDEV(C5:C12)`

#### 1.2 Applying STDEV.S Function

Excel **STDEV.S** function is the newer version of the **STDEV **function which is available from **Excel 2010**. While using **Excel 2010** or newer versions use this function.

You can use the following formula in this regard and insert your own cell references.

`=STDEV.S(C5:C12)`

#### 1.3 Using STDEVA Function

This function differs slightly from the previous two functions. It counts all logical values within an array. It counts any kind of text value (empty string, number representation) within the array or argument as 0.

Use the following formula to calculate the sample standard deviation in Excel.

`=STDEVA(C5:C12)`

### 2. Get Population Standard Deviation

You can use **STDEVP**, **STDEV.P** or **STDPA **functions to calculate population standard deviation in Excel.

#### 2.1 Using STDEVP Function

This function is the oldest function used to calculate population standard deviation in Excel.

You can use the following formula to use this function.

`=STDEVP(C5:C12)`

#### 2.2 Inserting STDEV.P Function

Excel **STDEV.P** function is the modified version of the **STDEVP **function which is available from **Excel 2010**. While using **Excel 2010** or newer versions use this function. These two functions also count any numerical values or a text representation of numbers directly and count only numbers in arrays and references. They ignore empty cells, logical values (TRUE/FALSE), text, and error values.

You can use the following formula to use this function in newer versions of Excel.

`=STDEV.P(C5:C12)`

#### 2.3 Applying STDEVPA Function

You can use the **STDEVPA **function to calculate the standard deviation of a population if you want to include text or logical values in your calculation.

Use the following formula to do that.

`=STDEVPA(C5:C12)`

**Read More:** How to Calculate Deviation in Excel

## How to Calculate Mean in Excel?

To calculate the mean of a distribution you can use the **AVERAGE** function in Excel. You can see an overview of this function below.

Use the following formula to calculate the mean of a distribution.

`=AVERAGE(C5:C12)`

## How to Calculate Standard Error in Excel?

The standard error tells how close the average of a sample is likely to be to the actual average of the whole group. To calculate the standard error of a distribution you can use the **STDEV**, **SORT**, and **COUNT **functions in Excel.

You can see an overview of the **COUNT** function below.

Here is the formula which you can use to calculate standard error in Excel.

`=STDEV(C5:C12)/SORT(COUNT(C5:C12))`

**COUNT**function counts the number of numeric values in the range

**C5:C12**, the

**STDEV**function calculates the standard deviation of these values, and the

**SORT**function sorts the count value.

**Read More:** How to Calculate Absolute Deviation in Excel

## How to Add Standard Error Bars in Excel?

You can add standard error bars to your Excel chart from **Chart Elements** option.

- To create a chart, select your dataset.
- Go to the
**Insert**tab >> click on**Insert Line Chart**>> select**2-D Line**chart.

- Then, to add standard error bars, click on
**“+”**sign >> turn on**Error Bars**option >> select**Standard Error**.

## Which Things Should You Remember?

You must insert two numerical values as the function argument to calculate the sample standard deviation. Otherwise, it will return **#DIV/0!** error.

## Frequently Asked Questions

### 1. What is the difference between STDEV.S and STDEV.P functions in Excel?

You can use the** STDEV.S **function when you want to calculate the standard deviation of a sample from the whole population. On the other hand, use the **STDEV.P **function if you want to calculate the standard deviation of the whole population.

### 2. How can I interpret the Standard Deviation value in Excel?

Standard deviation means how much the data points are dispersed from the mean or average. When the standard deviation value is high, the values are more dispersed. If the value of the standard deviation is low, it means the data points are closely clustered around the mean.

### 3. What is the difference between Standard Deviation and Standard Error?

Standard Deviation tells you about the spread of individual data points within a dataset. On the other hand, the Standard Error tells you how reliable your sample mean is as an estimate of the true population mean.

## Conclusion

In conclusion, the article gives you a comprehensive guide to calculating various types of deviation in Excel. Here we’ve specifically discussed how you can calculate both Sample and Population Standard Deviation using different functions suitable for different Excel versions. Also, we’ve shown ways to calculate the mean and standard error of a distribution. Hope you find this article helpful and informative.

## Deviation in Excel: Knowledge Hub

**<< Go Back to Excel for Statistics**** | Learn Excel**