# How to Calculate Standard Deviation in Excel Step-by-Step (2023)

Talking about statistics, the concept of standard deviation and mean go hand in hand 🤝

Mean only gives you the average figure for a dataset. But how truly does that figure represent the individual numbers of that dataset? Standard deviation will tell you that.

Standard deviation tells more about the accuracy of a mean. And interestingly, Excel offers inbuilt functions to calculate mean and standard deviation, both.

Let’s see how these Excel functions work in the guide below. Stay tuned and download our sample workbook for this guide here to practice side by side 📩

**Table of Contents**

## What is the standard deviation?

Standard deviation is a measure of how much the values of the dataset deviate from the mean of that dataset 🎲

This way you know if your mean is a fair representation of the underlying dataset.

Let’s quickly see this through an example. Below are the marks of a student in different subjects 🎓

We also have the mean (average marks) and standard deviation calculated for the same.

The mean for all the marks is 52. Which tells the average marks scored in all the subjects are 52 👍

And the standard deviation of 3.41 tells that the actual marks scored by the student in most of the subjects will be 3.41 marks more or less than the mean of 52.

**Pro Tip!**

Standard deviation can be both, positive and negative.

The closer the standard deviation is to 0, the lesser the variability in your data 💪

For example, if the mean of a data set is 70 and the standard deviation is 5. That’s fine. It means that most of the values in the dataset are around 5 points less or more than the mean of 70.

But if the standard deviation of the same mean is 20 – this means there is too much variation in the dataset. And the mean might not be a very reliable representative of this dataset as each value of this dataset might be up to 20 points more or less than 50 🧐

## Calculate standard deviation using STDEV

We know how impatient you’re getting to see the Excel standard deviation function in action. So here we go with the oldest function for calculating standard deviation in Excel.

Below is the data for some people from a small town along with their ages 🔞

Note that this is a sample drawn from the entire population of that town.

We already have the mean for this data calculated (just the simple AVERAGE function running in the background) 🗯

Using these ages, let’s now find the standard deviation for this dataset through the STDEV function.

- Write the STDEV function as follows:

**= STDEV (**

- Create a reference to the cells containing the numbers whose standard deviation is sought.

In our example, **cell range B2:B7** contains the ages so, we are referring to the same.

**= STDEV (B2:B7)**

**Pro Tip!**

Note that there is an empty cell in the list. And the STDEV function ignores blank cells in calculating the standard deviation.

Similarly, some other things you must know about the STDEV function are 💡

- It ignores any Boolean values (TRUE or FALSE), text values, and error values in the referred cell range or array.
- However, Boolean values or text values directly written as the arguments of the function are taken into account.
- The STDEV function of Excel 2003 can process up to 30 arguments only. However, Excel 2007 and later versions can process up to 255 arguments.

- Yes, that’s it, hit ‘Enter’ now.

There you go! The standard deviation for our dataset turns out as 24.3. That’s a big number considering the mean of 48.8 😮

Why is that btw? The dataset speaks out the reason very clearly and loudly.

Our sample has people from very variable age groups.

Starting from the age of 20 to the age of 80 – the variability in the numbers is huge. The standard deviation of 24.35 tells the same that the mean of 48.8 is not a very accurate representation of the dataset.

The STDEV function is now obsolete. It is primarily available in Excel 2003 up to Excel 2019. In later versions, it is only available for compatibility purposes 👵

But don’t worry – Excel has replaced the STDEV function with two more advanced functions. The STDEV.S and the STDEV.P functions. We are going to see them both now.

## Calculate standard deviation using STDEV.P

Until the above example, we were dealing with a sample from a population. But if you are dealing with the entire population, you must use the STDEV.P function.

**P** suffixing this function represents **‘population’** 🎪

The STDEV.P function is the successor of the STDEVP function. It offers more accuracy and is available in all Excel versions starting from Excel 2010.

We will not change the dataset for now. However, this time we will use the STDEV.P function to find the standard deviation for it.

- Write the STDEV.P function as follows:

**= STDEV.P (**

- Again create a reference to cells B2:B7 (that contain the ages for which the standard deviation is sought).

**= STDEV.P (B2:B7)**

**Pro Tip!**

The STDEV.P function will ignore any empty cells, logical values, or text values in the referred cell range 🔪

- Hit ‘Enter’.

This time the standard deviation is slightly different from the one we calculated above. It is 21.78.

That’s because the STDEV.P function runs the formula for population standard deviation 🧠

Yes, the standard deviation formula for sample standard deviation and population standard deviation is different. We will see them both shortly.

## Calculate standard deviation using STDEV.S

And now it’s time we see the STDEV.S function. This function targets the calculation of the standard deviation for a sample.

**S** suffixing this function represents** ‘sample’.**

Let’s calculate the standard deviation of the same dataset, as above using the STDEV.S function 👇

- Write the STDEV.S function as follows:

**= STDEV.S (**

- Again create a reference to cells B2:B7 (that contain the ages for which the standard deviation is sought).

**= STDEV.S (B2:B7)**

- Hit ‘Enter’.

Hope you noticed that! The standard deviation calculated by the STDEV.S function is the same as the STDEV function – 24.35 😲

This means that both the STDEV.S and STDEV functions run on the same classic sample standard deviation formula 📝

## STDEV.P vs. STDEV.S

The main difference between the STDEV.P and the STDEV.S function is the formula that runs behind them. And this is why Excel replaced the STDEV function with two different functions 😎

The STDEV.S function uses the classic sample standard deviation formula as below:

**Pro Tip!**

For this formula:

**x**represents each value of the sample**x̅**is the mean of all the values in the sample**∑**is the sum of the expression**n**is the total number of values or data p

Whereas the function STDEV.P uses the following formula:

**Pro Tip!**

For this formula:

**x**represents each value of the population**u**is the mean of all the values in the population**∑**is the sum of the expression**N**is the total number of values in the sample

Did you note what’s different between both the formulas? Only the denominator 🔎

In the population standard deviation formula, the whole expression is divided by N (the number of values in the entire population).

Whereas, in the sample deviation formula, the whole expression is divided by N-1 (the number of values in the sample less 1) 👀

This is called Bessel’s correction phenomenon 🏆

For sample-based formulas, we deduct 1 from the number of values (n) to adjust the mistakes resulting from assessing a sample, and not the whole population.

## That’s it – Now what?

That’s all about the statistical functions of Excel dedicated to calculating standard deviation.

We have discussed all these functions meant for calculating the standard deviation of a sample and a population. Feeling pro at calculating standard deviations?

Believe me, all these functions don’t even make a speck in Excel’s functions library. Yes, it’s that huge and full of amazing functions ⚖

If you want to master the functions of Excel, better begin with the key Excel functions.

Like the VLOOKUP, SUMIF, and IF functions (some of my top favorite Excel functions).

## Other resources

Standard deviation alone might not offer sufficient information about your dataset. To take it a step ahead, learn how to turn standard deviation into confidence interval.

What is a confidence interval? Hop on here to read our blog on it.