How To Calculate Standard Deviation In Excel

Know all about calculating standard deviation in Excel and more.

We all know that Microsoft Excel makes our organizations and data storage manageable by not just the features it offers when it comes to rows and columns but much more. But some users might not really know about it and overlook the ability of Excel to make tedious tasks more efficient. 

Excel gives us the features of a spreadsheet that are great to use to save time and be more productive. You just need to know what formula can be used where. Excel can definitely speed up your process. In this article, we’ll look at how to calculate the standard deviation in Excel. But, before we get there, let’s take a quick look at what the standard deviation is.

What is the standard deviation?

A standard deviation value would tell you how much the data set deviates from the mean of the data set.

For example, you have a group of 50 people, and you are recording their weight (in kgs).

In this data set, the average weight is 60 kg, and the standard deviation is 4 kg. It means that most people’s weight is within 4 kg of the average weight (which would be 56-64 kg).

Now let’s understand the standard deviation value:

• A lower value indicates that the data points tend to be closer to the average value (mean).
• A higher value indicates that there is widespread variation in the data points. This can also be a case when there are many outliers in the data set.

Calculating Standard Deviation in Excel

It is easy to calculate standard deviation but one should know which formula one must use. 

There are six standard deviation formulas in Microsoft Excel 

These six formulas can be divided into two groups:

1. Calculating the sample standard deviation: The formulas in this category are STDEV.S, STDEVA, and STDEV.
2. Calculating the standard deviation for an entire population: The formulas in this category are STDEV.P, STDEVPA, and STDEVP.

You will use standard deviation for a sample in almost all cases.

You can use the sample data to calculate the standard deviation and infer for the entire population.

So, this narrows down the number of formulas to three. Now let’s understand these three formulas:

Standard Deviation Formula In Excel

• STDEV.S – Use this when your data is numeric. It ignores the text and logical values.
• STDEVA – Use this when you want to include text and logical values in the calculation (along with numbers). Text and FALSE are taken as 0 and TRUE is taken as 1.
• STDEV – STDEV.S was introduced in Excel 2010. Before it, the STDEV function was used. It is still included for compatibility with prior versions.

So, you can safely assume that in most of the cases, you would have to use the STDEV.S function.

Also Read
How to apply Round Off Formula in Excel
How to use If Formula in Excel
How to calculate age in Excel

Using STDEV.S Function in Excel

As mentioned, the STDEV.S function uses numerical values but ignores the text and logical values.

Here is the syntax of STDEV.S function:

STDEV.S(number1,[number2],…)

• Number1 – This is a mandatory argument in the formula. The first number argument corresponds to the first element of the sample of a population. You can also use a named range, single array, or a reference to an array instead of arguments separated by commas.
• Number2 – [Optional argument in the formula] You can use up to 254 additional arguments. These can refer to a data point, a named range, a single array, or a reference to an array.

Example – Calculating the Standard Deviation for Weight Data

Suppose you have a data set as shown below:

Use the following formula to calculate the standard deviation using this data set: 

=STDEV.S(A2:A10)

In case you’re using Excel 2007 or prior versions, you will not have the STDEV.S function. In that case, you can use the below formula:

=STDEV(D2:D10)

The above formula returns the value of 2.81, which shows that most of the people in the group are within the weight range of 69.2-2.81 and 69.2+2.81.

Also Read
How to split cells in Excel
How to insert tick mark in Excel
How to convert text to number in Excel

conclusion

Microsoft Excel is a software program that enables users to use a spreadsheet system to format, organize, and filter data using formulae. You may do a wide range of actions, from easy ones like subtracting numbers to complicated ones like concatenating in Excel.

EasySheet is a tech blog that features incredible Excel hacks, tips, and tricks. Our objective is to help beginners learn Excel and use EasySheet to find answers to all of their questions.

If you need assistance with Microsoft Office products like MS Excel, Word, PowerPoint, Publisher, or anything else, come to us!