How to Calculate Standard Deviation in Excel

5.4 min read|Last Updated: January 26th, 2024|Categories: excel|
table of content

We all know that Excel is broadly used in statistical calculations. Standard deviation is one of the most useful parameters in statistics. So, we will discuss what it is and how to calculate it.

The Concept of Standard Deviation

The standard deviation is a statistic that indicates the dispersion of the data. In other words, it shows how much the values in a dataset are spread out around the average. Mathematically, it’s the square root of the variance.

A low standard deviation shows that the values in the dataset tend to be close to the mean, while a high standard deviation indicates that the data points are further from the mean.

When the standard deviation value of a dataset with the mean of x̅ is a small number (σ), we can say that most of the data points are within the range of x̅ ∓ σ, and when we say most of the data points, we refer to the normal distribution of the sample.

Note: 68% of the values are within one standard deviation away from the mean.

The standard normal distribution

Sample vs. Population Standard Deviation

You might have already heard the phrases “sample” and population” standard deviation. First, let’s see what these phrases are:

Population: Is the entire dataset you want to draw a conclusion about.

Sample: Is a smaller group of the data that you choose to represent the characteristics of the whole population.

In statistics, analysts are more interested in population standard deviation because it contains all the values we are dealing with. Therefore you would calculate the population standard deviation in two cases:

  • When you have the entire population
  • When you have a sample of a population, and you are only interested in the characteristics of that part of the data.

Now, it’s obvious that you would use the sample standard deviation when you have only a sample of a population, but you want to make a statement about the whole population.

Functions to Calculate the Population Standard Deviation

The formula to calculate the population standard deviation is as below:

square root of [ (1/N) times Sigma i=1 to N of (xi - mu)^2 ]

Where xi are individual values in the population, x̅ is the population average, and n is the number of values in the population.

The functions in this category are STDEVP, STDEV.P, and STDEVPA.

  • STDEV.P: It is used when the data is numeric. It ignores text values, empty cells, and logical values.

Note: The function counts logical values and text representations of numbers only if you type them directly in the list of arguments.

  • STDEVPA: You can use it when you want to include text, logical values, or text representations of the values in the calculations.

FALSE logical value and text values within arrays or reference arguments, including empty strings (“”), text representations of numbers, and any other text, are counted as “0.” TRUE is counted as “1.”

Note: If you type text representation of the numbers directly in the function, it will count them as the numbers themselves.

  • STDEVP: It is the old version of the STDEV.P function.

Functions to Calculate the Sample Standard Deviation

The formula to calculate the population standard deviation is as below:

square root of [ (1/N-1) times Sigma i=1 to N of (xi - mu)^2 ]

Where xi are individual values in the sample, x̅ is the sample average, and n is the number of values in the population.

The functions in this category are STDEV, STDEV.S, and STDEVA. The way to use these functions is exactly like the STDEVP, STDEV.P, and STDEVPA functions, respectively, but for the sample standa