Excel Standard Deviation: Highlighting Excel’s Statistical Prowess

If you are learning to perform statistical calculations and you have yet to understand Excel, you are missing out on a powerful tool. Perfect for taking in a range of inputs and automating calculations on the data, Excel operates as an excellent statistical calculator. And with a user friendly interface, intuitive controls, and lots of resources for learning, you should have no trouble making this program your new best friend.

If you want to start learning how to use Excel as a statistics workhorse, you can take a comprehensive course covering Excel’s statistical functions.

Here, we’ll take a look specifically at how you can use Excel to calculate standard deviation. This tutorial aims to explain Excel’s features comprehensively. So it will take an approach that introduces you to a number of functions as we go. In doing so, you will come to appreciate the benefit of knowing your way around Excel’s advanced functions, as they can save you a significant amount of time.

So, first we will do a step-by-step calculation, then we will use the STDEV function to do it all at once. If you are just looking for a reference on the STDEV function, skip ahead to the last section. Note that you will want to be familiar with some basic statistics before launching in to this exercise. If you need a brush-up, you can get it in an online introductory statistics course.

Completing a standard deviation calculation in steps

Excel makes it easy to calculate standard deviation in one step, but before we get to that, let’s take an approach that goes through the steps and also highlights additional statistics for a data set. For a simple data set to use, we can use a hypothetical list of salaries.

Here is a spreadsheet that includes a (fictional) sampling of current salaries for marketing managers in the U.S.  There are placeholders included for the mean, variance and standard deviation.

Mean

So, working through the standard deviation calculation, let’s start with the mean. You will want to put a formula in cell G2 to show the mean. There are two options for doing this.

1. You can use the SUM function to add all numbers and divide by the total of samples taken (35):

=SUM(B2:B36)/35

2. You can use the AVERAGE function, which automates these two steps for you:

=AVERAGE(B2:B36)

Variance

Now that you have the mean, you can work out the variance in steps, the long way. Or there is a function that will simplify this. And this is where you will see the power of excel for helping you build economical solutions.

Option 1. The long way:

If you are calculating variance in steps, you need to add two columns to your data. One will be for the difference from the mean for each salary, and the other will be the squared difference. Here is the header row in the example sheet with those columns added.

And here are are the formulas you will use:

Difference from mean: In the first row, you will plug in the difference calculation for the first salary and then copy it to the other rows. Keeping in mind that the mean is displayed in cell I3, your formula is simply this:

=(B2 – $G$2)

Make sure you use a cell reference for the mean when you use this method. This will make your spreadsheet dynamic and allow you to continue automating calculations in case the mean changes with more data. Also note that your reference needs to be a static one. If you are unfamiliar with what that means, it might be an indication that you could benefit from learning some Excel basics. You might try an online course in the essentials of Excel.

Squared difference: Your second column needs to show the previous result squared. For the first row, you will enter:

=D2^2

Then, simply copy this down to all rows. When you are finished, the three rows will look like the example on the right:

Variance: Now, with all this data, you are ready to calculate the variance in cell I2. Remember that, since we are using a sample, rather than a population, we want N-1 in the denominator. So our formula is [sum of squared differences]/(N-1). In this Excel spreadsheet, that means it will be:

=SUM(E2:E36)/34

Option 2. Now, the easy way

If you use the Excel function designed to calculate variance, you can eliminate all of the previous steps. Instead of adding extra rows and writing these formulas, you can simply plug in the VAR.S function (note that a variance function for population variance, VAR.P, is also available).

In the cell where you will display variance, simply type this:

=VAR.S(B2:B36)

This should give you the same result you came up with taking the long way.

Standard deviation

With the variance result, calculating standard variation is simple using a mathematical formula. All you need to do, of course, is take the square root. You have two options for that, recalling that your variance calculation is in cell K3:

=SQRT(I3)

or

=I3^(1/2)

You have now walked through a multi-step approach to calculating standard deviation, using the different methods in Excel… except for one.

Using functions to make your life easier

All of the steps you just took save you time over calculating standard deviation by hand or with a calculator.  However, we still haven’t unleashed the full potential of Excel. That’s because Excel includes a function that combines all of these steps in one. And it’s so simple, you’ll be kicking yourself for not learning about it sooner. For that matter, you will likely want to learn all about the options you have for simplifying your work with Excel.  You can do that with an advanced course in Excel.

Now, to calculate the standard deviation, we once again have two available functions. STDEV.S produces a result for a sample, and STDEV.P produces a result for an entire population. Again, the difference is using N vs N-1 in the denominator.

Since this example uses a sample set, you will want to use the STDEV.S function. So, using the set in B2:B36, the function is this:

=STDEV.S(B2:B36)

And the result for this set is 15650.55.

Now, to go a step further, you should also realize that functions in Excel make it possible to show the mean, variance, and standard deviation without having to perform mathematical equations. To recap, you would do that as follows

Mean:

=AVERAGE(B2:B36)

Variance:

=VAR.S(B2:B36)

Standard Deviation:

=STDEV.S(B2:B36)