**How to Calculate Standard Deviation in Excel**

Are your data points concentrated? Or spread out?

That’s what **standard 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.

The data points of the

low-varianceseries are clustered around the mean.Those in the

high-varianceseries are more spread out.

A **low standard deviation implies that your data is uniform**.

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

**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.

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

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.

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 would **skew the mean and standard deviation**.

So the **best formula in this case is STDEV.P**.

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

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.

**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.

Now you know the **four 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!