How to Calculate Deviation in Excel (3 Easy Examples)

In this article, I will show you how to calculate standard deviation in Excel. In many situations, we need to calculate standard deviation whether performing any business calculations or dealing with large statistical data. Fortunately, Excel offers multiple functions to deal with those situations. Here, I will try to explain each function with proper illustrations and examples. So, let’s get started.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

What Is Standard Deviation?

Before jumping into the ways of calculating the Standard Deviation in Excel, let’s understand what the Standard Deviation is. In statistics, standard deviation is a measurement of the data’s dispersion from the mean. If a certain data set has a higher standard deviation, then the data points are more dispersed from the average. The general formula for standard deviation is as follows.

Here,

X_i= each value from the dataset

X̄= mean value of the entire data set

N= number of data points

For example, below, I have calculated the standard deviation for two sets of data.

Here, we can see that the marks on Chemistry are more dispersed from the average mark (65.3) compared to Physics. Hence, the standard deviation is larger for Chemistry (14.3) compared to Physics (8.1).

Read More: How to Calculate Variance and Standard Deviation in Excel

Types of Standard Deviation

The standard deviation is of two kinds. They are

1. Sample Standard Deviation
2. Population Standard Deviation

The Sample Standard Deviation measures the deviation for a selected portion of data points of a data set whereas the Population Standard Deviation measures the deviation for the entire data points of the data set. The formulas also differ a bit from each other. Below, the two formulas are given.

Here,

X_i=each value from the dataset

X̄=mean value of the entire data set

N=Number of data points

6 Functions for Calculating Standard Deviation

There is a total of 6 functions to calculate standard deviation in Excel.

• STDEV.S: This function considers just numerical data to determine the sample standard deviation. It overlooks text and logical values (TRUE or FALSE) in the spreadsheet. The syntax of STDEV.S is

This function can take a maximum of 255 arguments in the form of numbers, arrays, names of ranges, or references that contain numbers. This function is only available in Excel 2010 and later versions. In the earlier versions, we need to use the STDEV function.

• STDEV: This is an earlier version of the STDEV.S function in Excel used to calculate sample standard deviation before 2007. For the sake of compatibility, it is still available. But Microsoft recommends using the latest counterpart. The syntax of this function is

• STDEV.P: This function determines the population standard deviation. It calculates by considering only numerical values. It does not consider texts and logical values in the spreadsheet. The syntax of the STDEV.P function is

Like the STDEV.S function, this function is also available in Excel 2010 and later versions. In earlier versions, we need to use the following function.

• STDEVP: This is an earlier version of the STDEV.P formula that is still available for compatibility issues. It also measures the population standard deviation by considering only numerical values. The syntax of the function is

• STDEVA: This function calculates the sample standard deviation of a dataset considering the text and logical values in the calculation. All FALSE values are considered 0, and TRUE values are considered 1. The syntax of the function is

• STDEVPA: This function calculates the population standard deviation considering text and logical values in the calculation. Like STDEVA, all FALSE values are considered 0, and TRUE values are considered 1. The syntax of the function is

Read More: How to Use AVEDEV Function in Excel (2 Suitable Examples)

3 Useful Examples to Calculate Deviation in Excel

In this section, we will demonstrate 3 effective methods to calculate standard deviation in Excel with appropriate illustrations. Let’s explore those examples one by one.

1. Using STDEV.S and STDEV Functions

In this example, I will show you how we can calculate the sample standard deviation of a data set using STDEV.S and STDEV functions. For illustration, I have taken a data set where the heights of 6 students in cm are given.

Now, we will calculate the sample standard deviation of the heights. To do that, follow the steps below.

Steps:

• First, select a cell where you want to calculate the standard deviation. Then, write down the following formula.

• Now, press OK.  As a result, you will see the following outcome.

• Now, we can also use the legacy STDEV function to acquire the same result.

Though we have shown obtaining the same result using the STDEV function, we recommend using the STDEV.S function always as it is the latest function.

Read More: How to Calculate Quartile Deviation in Excel (4 Easy Methods)

2. Calculation of Population Standard Deviation

In the 1st example, I have shown the sample standard deviation. In this example, I will work with the same data set but determine the population standard deviation using the STDEV.P and STDEVP functions. So, to know more, follow the steps below.

Steps:

• First, pick a cell and write down the following formula.

• Now, press Enter As a result, you will get the population standard deviation of the student’s heights.

• Here, we can also use the STDEVP function to get the same result.

In this way, we can easily get the population standard deviation.

3. Calculating Standard Deviation of Text Representation of Numbers

Many times, our required numbers remain as a part of a large text string and we need to extract the numbers. After extracting the numbers, they are still formatted as text. In order to calculate the standard deviation, we can not directly apply the above 4 functions. Rather, we need to apply STDEVA and STDEVPA functions in this scenario. To illustrate, I have taken a data set where the student’s name and height are merged.

Now, we need to extract the numbers( heights) in another column and then use the STDEVA and STDEVPA functions to Calculate Standard Deviations. To know more, follow the steps below.

Steps:

• First, we will extract the numbers from the merged text. To do that, take a cell on the right column(C5), and write down the following formula.

• Now, press OK.  As a result, you will get the height of Abel in cell C5.

• Now, use the Fill Handle to extract the height value for the rest of the cells.

• As the functions used in the formula returns text values, the numbers are in text format.
• Now if we use the STDEV.S function, we will get an error like this.

• Hence, to calculate the sample standard deviation, we have to directly insert the height value inside the STDVA function like this.

• Here, we have directly inserted the number-extracting formulas as arguments of the STDEVA function.
• In a similar manner, we can use the STDEVPA function to calculate the Population standard deviation.

In this way, we can calculate the standard deviation for numbers in text format. But if you have a large number of data, it is best to convert the numerical values into Number format.

Read More: How to Use DEVSQ Function in Excel (3 Suitable Examples)

Things to Remember

• Before applying any of the mentioned functions, check whether it will fit your requirement.
• In the 3rd example, we can also use the STDEV.S and STDEV.P functions to get the same result.

Conclusion

That is the end of this article regarding how to calculate deviation in Excel. If you find this article helpful, please share this with your friends. Moreover, do let us know if you have any further queries. Finally, please visit Exceldemy for more exciting articles on Excel.

Related Articles

• How to Calculate Absolute Deviation in Excel (2 Useful Examples)
• Find Sum of Squared Deviations in Excel (2 Easy Ways)
• How to Create Deviation Report Format in Excel (with Easy Steps)