Standard Deviation is one of the best measures in statistics to find the spread of a distribution.
As it is very common to do statistical calculations in Microsoft Excel, it is important to learn how to calculate standard deviation in Excel.
In this article, I will show you how to calculate standard deviation in Excel.
What is Standard Deviation?
The standard deviation tells us the level of deviation of the data set compared to its mean.
A low standard deviation indicates that the values tend to be close to the mean/average value of the set, while a high standard deviation indicates that the values are spread out over a wider range.
Let’s say we have the test scores of 20 students. The mean value is 70, and the standard deviation is 10. This tells us that most of the students have scored between 60 and 80 (which is 70-10 and 70+10).
In statistics, you can calculate the standard deviation for a population or for a sample set. Excel has formulas to calculate the standard deviation for both the population, as well as, the sample.
While calculating the standard deviation for an entire population would be more accurate, in most practical cases, it is not possible to collect data from the entire population, and hence sample is used.
Also read: How To Find Standard Error In Excel?
How to Calculate Standard Deviation of a Population in Excel?
There are three functions that we can use to calculate the standard deviation of a population in Excel. They are:
- STDEV.P
- STDEVPA
- STDEVP
All the above functions ignore empty cells. Further, all the above functions evaluate TRUE values of logical values as 1 and FALSE values of logical values as 0 in the list of arguments (other than references or array).
If we enter numbers in text format in the list of arguments of the above functions, Excel will evaluate them as numbers.
Using STDEV.P to Calculate the Standard Deviation of Population
We can use the Excel STDEV.P function to find the standard deviation of a population.
This function ignores all text values and logical values in arrays or references.
The below table shows the distance between the employee’s home and the office of all employees.
Now, I need to find the standard deviation of this population.
I can use the STDEV.P function to calculate the standard deviation of the population.
=STDEV.P(B2:B11)
The syntax of the STDEV.P function is STDEV.P(number1,[number2],…). For this function, we have to enter at least one argument.
In this case, I have selected distances as an array to the function. So, I selected cells B2 to B11 as the argument of the STDEV.P function.
If any of these numbers contain text values or logical values, the function will ignore those.
Let’s say that the distance from Ann’s home to the office is entered as text.
Now, if I calculate the standard deviation of the population using the STDEV.P function, the function will ignore Ann’s home-to-office distance.
In other words, the STDEV.P function considers the cells that contain text values and logical values as empty cells.
So, if we delete Ann’s home-to-office distance and calculate the standard deviation of the population, we get the same answer as keeping Ann’s home-to-office distance as a text value.
Using STDEVPA to Calculate the Standard Deviation of a Population
As I explained earlier, the STDEV.P function ignores empty cells and all text values and logical values in arrays or references.
But, sometimes, we want to calculate the standard deviation from all data values, including logical values and text values. In this case, we have to use the Excel STDEVPA function.
The below table shows the distance between the employee’s home and the office of all employees.
However, Ann’s home-to-office distance is stored as text.
Now, I need to find the standard deviation of this population, including all text values and logical values.
I can use the Excel STDEVPA function to calculate the standard deviation of the population, including all text values and logical values of the data set.
=STDEVPA(B2:B11)
The syntax of the STDEVPA function is STDEVPA(value1, [value2], …).
Similar to the STDEV.P function, we have to enter at least one argument for this function.
In this case, I have selected distances as an array to the function. So, I selected cells B2 to B11 as the argument of the STDEVPA function.
The STDEVPA function considers text values or logical values for the population’s standard deviation calculation.
It will convert text values to zeros (0). For logical values, the function will convert True values to 1 and False values to 0.
So, in this case, the function will consider Ann’s home-to-office distance as zero because the distance is stored as a text value.
If we recalculate the standard deviation by replacing Ann’s home-to-office distance with zero, we will get the same answer as above.
Also read: Standard Deviation in Pivot Tables in Excel
Using STDEVP to Calculate the Standard Deviation of a Population
When you are typing the first few letters of the above two formulas, you may have noticed that there is another formula to calculate the population’s standard deviation.
This STDEVP function is the older version of the STDEV.P function.
The STDEVP function is still available in the new Excel versions for backward compatibility purposes.
Microsoft has announced that this function may not be available in future Excel versions. So, it is advisable to always use the Excel STDEV.P function instead of the STDEVP function.
The below table shows the distance between the employee’s home and the office of all employees.
I want to find the standard deviation of this population, ignoring all text values and logical values. Assume that my Excel version is 2007 or earlier.
I can use the Excel STDEVP function to calculate the standard deviation of the population, excluding all text values and logical values of the data set.
=STDEVP(B2:B11)
The syntax of the STDEVP function is STDEVP(number1,[number2],…). For this function, we have to enter at least one argument.
In this case, I have selected distances as an array to the function. So, I selected cells B2 to B11 as the argument of the STDEVP function.
If any of these numbers contain text values or logical values, the function will ignore those.
The standard deviation that we are calculating using the STDEV.P is exactly equal to the standard deviation that we are calculating using the STDEVP.
Also read: How to Find NPV in Excel
How to Calculate Standard Deviation of a Sample in Excel?
There are three functions that we can use to calculate the standard deviation of a sample in Excel. They are;
- STDEV.S
- STDEVA
- STDEV
All the above functions ignore empty cells.
Further, all the above functions evaluate TRUE values of logical values as 1 and FALSE values of logical values as 0 in the list of arguments (other than references or array).
If we enter numbers in text format in the list of arguments of the above functions, Excel will evaluate them as numbers.
Using STDEV.S to Calculate the Standard Deviation of a Sample
We can use the Excel STDEV.S function to find the standard deviation of a sample.
This function ignores all text values and logical values in arrays or references.
The below table shows the distance between the employee’s home and the office of a sample of all employees.
Now, I need to find the standard deviation of this sample.
I can use the STDEV.S function to calculate the standard deviation of the sample.
=STDEV.S(B2:B11)
The syntax of the STDEV.S function is STDEV.S(number1,[number2],…). For this function, we have to enter at least one argument.
In this case, I have selected distances as an array to the function. So, I selected cells B2 to B11 as the argument of the STDEV.S function.
If any of these numbers contain text values or logical values, the function will ignore those (Because I referred to all cells as an array argument to the function).
Let’s say that the distance from Ann’s home to the office is entered as text.
Now, if I calculate the standard deviation of the sample using the STDEV.S function, the function will ignore Ann’s home-to-office distance.
In other words, the STDEV.S function considers the cells that contain text values and logical values as empty cells.
So, if we delete Ann’s home-to-office distance and calculate the standard deviation of the sample, we get the same answer as keeping Ann’s home-to-office distance as a text value.
Using STDEVA to Calculate the Standard Deviation of a Sample
As I explained earlier, the STDEV.S function ignores empty cells and all text values and logical values in arrays or references.
But, sometimes, we want to calculate the standard deviation from all data values of a sample, including logical values and text values. In this case, we have to use the Excel STDEVA function.
The below table shows the distance between the employee’s home and the office of a sample of employees. However, Ann’s home-to-office distance is stored as text.
Now, I need to find the standard deviation of this sample, including all text values and logical values.
I can use the Excel STDEVA function to calculate the standard deviation of the sample, including all text values and logical values of the data set.
=STDEVA(B2:B11)
The syntax of the STDEVA function is STDEVA(value1, [value2], …).
Similar to the STDEV.S function, we have to enter at least one argument for this function. In this case, I have selected distances as an array to the function.
So, I selected cells B2 to B11 as the argument of the STDEVA function.
The STDEVA function considers text values or logical values for the sample’s standard deviation calculation.
It will convert text values to zeros (0). For logical values, the function will convert True values to 1 and False values to 0.
So, in this case, the function will consider Ann’s home-to-office distance as zero because the distance is stored as a text value.
If we recalculate the sample standard deviation by replacing Ann’s home-to-office distance with zero, we will get the same answer as above.
Using STDEV to Calculate the Standard Deviation of a Sample
When you are typing the first few letters of the above two formulas, you may have noticed that there is another formula to calculate the sample’s standard deviation.
This STDEV function is the older version of the STDEV.S function.
The STDEV function is still available in the new Excel versions for backward compatibility purposes.
Microsoft has stated that this function may not be available in future Excel versions. So, it is advisable to always use the Excel STDEV.S function instead of the STDEV function.
The below table shows the distance between the employee’s home and the office of a sample of employees.
I want to find the standard deviation of this sample, ignoring all text values and logical values. Assume that my Excel version is 2007 or earlier.
I can use the Excel STDEV function to calculate the standard deviation of the sample, excluding all text values and logical values of the data set.
=STDEV(B2:B11)
The syntax of the STDEV function is STDEV(number1,[number2],…). For this function, we have to enter at least one argument.
In this case, I have selected home-to-office distance distances as an array to the function. So, I selected cells B2 to B11 as the argument of the STDEV function.
If any of these numbers contain text values or logical values, the function will ignore those.
The standard deviation that we are calculating using the STDEV.S is exactly equal to the standard deviation that we are calculating using the STDEV.
If we try to do these standard deviation calculations manually, we have to follow many calculation steps to get the final answer.
With these amazing Excel functions, we can calculate complicated standard deviation calculations within a second.
Also read: How to Find Z-score in Excel?
Interpreting Standard Deviation Results
Standard deviation is a measure of how widely data values are dispersed from the average (mean) value.
After calculating standard deviation in Excel with the STDEV.S or STDEV.P functions, it is essential to understand the meaning of the results.
This section covers the interpretation of low and high standard deviations, as well as the accuracy of calculations.
Low Standard Deviation
A low standard deviation indicates that data values are close to the mean. This means that the data is relatively consistent and has minimal variation.
For example, if a teacher records the test scores of a class and finds a low standard deviation, this suggests that most students performed similarly to the class average.
- Consistency: A low standard deviation reflects consistency in the data set.
- Minimal variation: Data points are clustered around the mean, with few outliers.
High Standard Deviation
On the other hand, a high standard deviation reflects a more significant spread in the data set.
Data values are comparatively dispersed from the mean, indicating more considerable variability or inconsistency.
For example, if the same teacher records a high standard deviation for another test, it implies that the student’s performance varied significantly, with some far above or below the class average.
- Variability: A high standard deviation indicates more variability in the data set.
- Greater distribution: Data points are spread out, with a broader range of values and potential outliers.
Other Excel articles you may also like:
