How To Calculate Standard Deviation In Excel

Are you planning to learn how to calculate standard deviation in Excel? Read the article to know how to do it.

We all know that 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 features of Excel 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.

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 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 are closer to the average (mean) value.
• 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 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 cases, you would have to use the STDEV.S function.

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, a 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 69.2-2.81 and 69.2+2.81.

How Lio can Help You

Lio is a great platform that can help entrepreneurs, homemakers, students, businessmen, managers, shop owners, and many others. This mobile application helps to organize business data and present them in an eye-catching manner. 

Lio is a great platform for small business owners and can track a wholesome record of employee information for better employee management, customer data, etc. You can handle those data with ease.

If you want to be a professional, then you must save time, you need to learn to arrange all the business strategies in one place. In that case, Lio can be your partner.

Entrepreneurs can also allow multiple authorized users of their office to access the information from various locations within minutes.

Lio is definitely for the win and using it for your business is only going to make your journey smooth and easy to track.

Step 1: Select the Language you want to work on. Lio on Android

Step 2: Create your account using your Phone Number or Email Id.

Verify the OTP and you are good to go.

Step 3: Select a template in which you want to add your data.

Add your Data with our Free Cloud Storage.

Step 4: All Done? Share and Collaborate with your contacts.

Conclusion

Hope by now you have understood what standard deviation in Excel is and how you can calculate it. Once you have understood it and gotten to the depth of it, you can now use it in your work and grow your business.

Frequently Asked Questions (FAQs)