how to find standard deviation in excel

How to Calculate Standard Deviation in Excel

How to CalculateStandard Deviation in Excel

Are your data points concentrated? Or spread out?

That's whatstandard deviation will tell you.

And when you present your data to someone else, it's a great way to provide more information.

First we'll define standard deviation.

Then we'll talk about populations and samples, and why they're important.

Kasper Langmann, Co-founder of Spreadsheeto

*This tutorial is for Excel 2019/Microsoft 365 (for Windows). Got a different version? No problem, you can still follow the exact same steps.

What is standard deviation?

The best way to understand standard deviation is by seeing it.

In the following scatterplot, we have two series.

The important thing to understand isboth series have the same average value.

The data points of thelow-variance series are clustered around the mean.

Those in thehigh-variance series are more spread out.

Kasper Langmann, Co-founder of Spreadsheeto

A low standard deviation implies that your data is uniform.

On the other hand, a high standard deviation shows more variance.

Grab your free exercise file here!

Throughout this guide, we'll use a sample workbook for calculating standard deviation on a practice dataset.

Click below to download the workbook for free and follow along!

Download the FREE Exercise File

Calculating the standard deviation of a population

Let's look at formulas for finding the standard deviation of a population.

Here's the standard-deviation-for-population syntax:

Excel formulas for standard deviation of population

=STDEV.P(number1, [number2],…)

This formula ignores non-numeric data.

If your data has blank cells, text data, or logical values TRUE or FALSE, this formula will exclude them from the calculation.

=STDEVPA(number1, [number2],…)

This formula accounts for non-numeric data by replacing FALSE and text items with 0 and TRUE items with 1.

It's important to note here that apopulation is the set of all possible values.

In our example, we'll calculate the standard deviation of test scores among a class.

We'll use the same dataset to calculate the standard deviation of a sample in a moment.

Kasper Langmann, Co-founder of Spreadsheeto

Since the data includes midterm and final exam scores from the entire class, this data represents an entire population.

You'll select either the STDEV.P or STDEVPA function.

Let's take a closer look to see which formula we should use.

Kasper Langmann, Co-founder of Spreadsheeto

Here are some factors to take into account:

One student missed the midterm and has a score of "n/a."
Another missed the final and was given an "incomplete" mark.
The test scores range from 52 to 100.

Knowing these facts, we determine that replacing "n/a" and "Inc" with zeroes wouldskew the mean and standard deviation.

So thebest formula in this case is STDEV.P.

We'll use both forms of the formula, though, just to show you the difference in results.

Kasper Langmann, Co-founder of Spreadsheeto

As you can see, the result of STDEV.P shows a smaller standard deviation (and therefore, a smaller variance).

In this case, treating non-numeric entries as zeroes throws off the calculation quite a bit.

Calculating the standard deviation of a sample

A sample is different from a population: it's only a selection of the data points from the entire population.

As you can see, the formulas are similar to the standard deviation for population formulas:

Excel formulas for standard deviation of samples

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

This formula ignores non-numeric data.

=STDEVA(number1, [number2],…)

Like STDEVPA, this formula counts non-numeric data as zeroes, except for "TRUE," which is counted as 1.

For this example, imagine you have 10 years of student test data.

You want to use the scores from a current class to calculate the standard deviation for scores over the past 10 years.

You're using the dataset as a sample of a larger population. So you'll use STDEV.S or STDEVA.

Since this data includes text entries, STDEV.S is best.

Notice that STDEV.S results in smaller standard deviations than STDEVA.

Because of the missing data, that's a better result.

Wrapping things up

Now you know thefour standard deviation functions.

Whether you have a set of data from a population or sample, you can get the standard deviation.

So the next time you're describing data, you can show off your Excel skills by including the variance! 💪

Now it's up to you to implement STDEV.P, STDEVPA, STDEV.S, and STDEVA in your own spreadsheets!

Kasper Langmann, Co-founder of Spreadsheeto

Kasper Langmann 2020-05-11T14:15:34+00:00