Beginner’s Guide to Standard Deviation in Excel

Have you ever been looking at your monthly sales spreadsheet and just felt like something was off?

Sure, the average looks fine, but you know some weeks were way busier than others and the numbers just aren’t telling the full story. And that, it turns out, is the problem with averages. They tend to flatten everything, so you lose visibility into the spikes, the dips, and the real variation in your data.

The good news is that there’s a solution we can use called standard deviation to see how spread out our data really is, so you can stop guessing and start trusting what you're seeing.

The trick of course, is knowing how to use this properly. Which is why in this guide, you’ll learn how to calculate standard deviation in Excel the right way.

I’ll cover when to use each formula, how to avoid the common mistakes that quietly mess up your results, and some easy tricks to keep your spreadsheets clean, readable, and reliable.

Sidenote: If you want to take your Excel skills to the next level, check out my Complete Excel course.

You’ll learn Excel from scratch including advanced topics like data analysis & modeling, pivot tables, and VBA. You'll build your own professional spreadsheets and charts, and have the skills to get hired as a Data Analyst!

All online, and updated every year.

With that out of the way, let’s get into this 5-minute tutorial

What is standard deviation and why should you care?

Standard deviation is a mathematical method that tells you how much your numbers vary from the average. So it’s not just looking at the highest and lowest values — it’s measuring how spread out all the values are from the middle.

If most of your numbers are close to the average, the standard deviation is small. If they’re all over the place, the number is larger.

For example

Let’s say you’re looking at ten test scores, and most of them are clustered around 80.

78, 79, 79, 80, 80, 80, 80, 81, 81, 82

If we were to calculate the average score (the total combined scores, divided by the number of people taking the test), then this would average a score of around 80.

Just as importantly though, we can use standard deviation to assess the consistency of overall student performance.

How?

Well, a small standard deviation like in this example where almost all the scores were 80, would tell you the scores are quite similar. Everyone performed about the same, and you might feel confident that the material was clearly understood across the board.

Congrats, you’re a great teacher!

However… imagine that we still got the same average of 80, but the scores range from 60 to 95.

60, 65, 70, 75, 80, 80, 85, 85, 90, 95

Suddenly, your standard deviation is much higher because the numbers are much further from the average.

So why should we care?

Well, if we never bothered to check this, we would assume everything is fine - but instead, we now get a much better idea of what's going on. We can tell at a glance that this kind of spread suggests a possible gap in understanding. Some students really got it, while others might be struggling.

By going into depth like this, we can then assess what needs to happen to improve, and that same logic applies in business too.

For example

Let’s say that you’re tracking monthly revenue across different retail store locations.

If one location is crushing it while another is lagging behind, a high standard deviation will alert you to those inconsistencies, even if your total revenue looks solid on paper. This could then be a sign to investigate what's working in the top-performing branches so you can integrate that and raise performance across the board, while also investigating and uncovering challenges in the lower-performing ones to see how to help raise them up.

And that’s why standard deviation is so useful because it doesn’t just give you a summary, it gives you insight!

So whether you’re analyzing test results, project timelines, customer ratings, or anything else with inherent variation, it helps you spot inconsistency that averages can hide.

And the good news?

Excel makes standard deviation incredibly easy to calculate, as long as you use the right function.…which is what we’ll go over next!

The most common standard deviation functions in Excel (and which to use)

Before we jump into formulas, there’s something important you should know. Excel doesn’t just give you one standard deviation function. It gives you a few, and picking the wrong one can quietly throw off your results.

Here are the ones you’ll most likely run into.

`STDEV.S`

This stands for standard deviation of a sample, and is the option you’ll probably use most often. It’s designed for when you only have part of the full data, which is called a ‘sample’. (Hence the name).

More often than not, you’ll only have part of the data which is why this is helpful.

For example

Let’s say your company emails a customer satisfaction survey to 1,000 people.

However, if we’re being realistic here, not everyone will respond to that survey. Maybe 100 out of that 1,000, so even though you reached out to the full group, you're working with a sample, because only a portion replied.

Also?

The people who do reply probably don’t represent the entirety of your audience. Some people like filling out forms, some people like to complain, etc. So the data you’re getting is not only incomplete, but it's slightly biased.

STDEV.S is built for this kind of situation. It applies a small adjustment (called Bessel’s correction) to avoid underestimating the variability in the full population. Think of it as Excel’s way of saying “Hey, you don’t have all the info so let me help balance that out.”

`STDEV.P`

This stands for standard deviation of the population, and is only used when you have all the data from the group you care about. The key thing about this option is that it doesn’t need to make adjustments because it assumes that it has all the data to pull from.

For example

Imagine we sent out that survey to 1,000 people, but this time every one of those 1,000 people replied.

In this situation you would use STDEV.P, because you're working with the full dataset and so Excel doesn’t need to adjust anything. It just gives you the exact spread.

What about the original `STDEV` function?

If you’ve been working with Excel for a while, you might have come across STDEV in older spreadsheets or tutorials. This still calculates standard deviation for a sample, just like STDEV.S does today.

So why did Excel change it?

Mainly for clarity. Back then, there were only two functions, STDEV for sample data, and STDEVP for population data. But the names weren’t very descriptive for beginners, and that caused a lot of confusion.

So Excel introduced clearer versions of STDEV.S and STDEV.P. These make it obvious which one to use just by looking at the name. S is for Sample, P is for Population.

That being said, the older options still work (mainly to support older files), but when you're writing formulas now, it’s best to use the newer versions. They’re easier to understand, easier to maintain, and they future-proof your spreadsheets in case the old functions ever get phased out.

Enough about those older methods though.

Now that you know there are two standard deviation functions to choose from in Excel, the obvious next question is, well, which one should you use and when?

So which should you use, `STDEV.S` or `STDEV.P`?

In simple terms:

If you have all the data, use STDEV.P. You’re measuring the actual variation of a complete dataset, and Excel can calculate it precisely with no guesswork or adjustments involved
If you only have part of the data, use STDEV.S. You’re estimating based on a sample, and Excel adjusts the formula to help you get as close as possible to the real picture. It’s not perfect, but it’s designed to give you a more accurate guess than you'd get otherwise

That’s why STDEV.S is the safer choice in most real-world scenarios. It accounts for the fact that your data might be incomplete, and helps you avoid underestimating how much things really vary.

Easy!

So now that you know which one to use, let’s look at how to actually plug these into Excel.

How to calculate standard deviation in Excel

We’ll go through two quick examples. One using STDEV.S for a sample, and one using STDEV.P for a full dataset. Both follow the same process so once you learn to use one, you can use the other confidently as well.

How to calculate standard deviation with `STDEV.S`

Let’s start with some simple data and an example dataset:

72, 75, 78, 79, 80, 82, 85, 87, 90, 93

You can type these numbers into Excel, one in each cell (say A1 to A10), or paste them all at once into a column. Once they’re in, here’s what to do:

If you’re working with a sample

Click on an empty cell where you want the result.
Type the formula =STDEV.S(A1:A10)
Press Enter.

And that’s it. Excel will return the standard deviation based on that sample, and then use Bessel’s correction behind the scenes to account for the fact that you're estimating from part of a population.

In this case, the result is 6.63 (rounded to two decimal places).

How to calculate standard deviation with `STDEV.P`

This time we’re going to assume you have an entire dataset and not just a sample.

However, to keep things simple we’re going to just use the exact same dataset so you don’t need to add in a bunch of data, and treat it as if it was a complete dataset of all data collected. (Also, this will help you to see the difference between the adjusted values).

Here’s how it works:

Click on another empty cell
Type the formula =STDEV.P(A1:A10)
Press Enter

This time, Excel skips the correction and calculates the exact spread, since it assumes you’re working with the full population.

The result is now changed to 6.29.

Notice the difference?

STDEV.S gave you a slightly higher value than STDEV.P due to Excel compensating for the fact that you're only working with part of the data.

It’s not quite correct, but if this was all we had to work with, it gives us a closer approximation to the actual deviation. We just notice it more here because the smaller your sample or the more uneven your data, the more noticeable this adjustment becomes.

Common mistakes and how to avoid them

Calculating standard deviation in Excel is pretty straightforward, but interpreting it correctly is where most people slip up. This is because the formulas won’t throw errors, but using them the wrong way can quietly lead to false confidence in skewed results.

So here are the most common mistakes to watch out for, along with ways to handle or avoid them.

Mistake #1. Using the wrong function

A lot of people default to the first standard deviation function that AutoComplete suggests, or just reuse a formula from another sheet, without thinking about whether they’re working with a sample or the full population. And as a result, they end up using the sample or population formula at the wrong times.

Remember that if you use STDEV.P when you only have a sample, Excel will underestimate the spread in your data. This can make your results look more consistent than they really are, which can lead to bad decisions if you’re relying on that analysis.

And likewise, if you use STDEV.S on a full dataset, it’s going to produce slightly skewed results thanks to the adjustment.

So the bottom line is, always make sure you’re using the right option for the data you have.

Mistake #2. Hardcoding ranges instead of making your formulas dynamic

It’s easy to write something like =STDEV.S(A1:A10) when you first build a spreadsheet, but what happens when new data comes in and you paste it into A11?

Well, Excel won’t include it because your formula is stuck looking at just A1 to A10.

This isn’t a big deal if you remember to update the formula manually. But it’s all too easy to forget - especially when you’re juggling multiple sheets, copying data from exports, or automating reports.

That leads to your formula appearing to calculate standard deviation for your whole dataset when it’s actually skipping recent entries. Even if this new number is significantly higher or lower than earlier values, the final number won’t reflect the real spread.

And the more this happens, the worse your data will be - which can obviously cause you to overlook spikes, changes, or inconsistencies in your data.

The best practice then is to make your formulas dynamic so they grow with your data, and there are a few ways to do it.

Option #1. Convert your dataset into a table

Select your data and press Ctrl + T to turn it into an Excel table. Now, instead of using =STDEV.S(A1:A10), you can use structured references like =STDEV.S(Table1[Scores]).

When you add new rows to the table, the formula pointing to the table automatically updates.

Option #2. Use a dynamic named range

If you prefer not to use tables, you can create a named range using a formula like =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

Then call it like =STDEV.S(MyRange).

Just as with tables, the named range (and thus the range of data the formula is pointing to) will then expand as new values are added to column A.

Option #3. Use Excel’s newer dynamic array functions (for newer versions)

If your Excel version supports it, functions like LET or FILTER can help you build even more flexible formulas based on conditions or multiple columns.

TL;DR

It doesn’t matter which you choose, just that you set one of these up so you get accurate data moving forward, and don’t have to remember to tweak or adjust things manually.

Mistake #3. Including totals or averages in your range

Let’s say you’ve summarized your dataset neatly. Maybe with a row at the bottom showing the total or average. It’s a totally normal thing to do, and it's great for visibility.

In and of themselves, these summary totals and averages aren’t an issue. The issue is when you go to calculate the standard deviation and accidentally include that summary row in your range.

Whoops!

Excel doesn’t know you meant to add that extra row. It just sees another number, so now your formula is calculating the spread across an additional value, even though one of them is a calculated average that doesn’t belong.

As you can imagine, this will distort your results, often pulling the standard deviation down and making your data seem more consistent than it really is! So always make sure to keep your raw data and summary rows separate.

That could mean:

Leaving a blank row between your dataset and any totals or averages
Using tables or dynamic named ranges that only refer to the actual data (e.g. Scores =A1:A10)
Adding your formulas in another column instead of at the bottom of the dataset

Basically, anything that helps you avoid accidentally dragging in an average, subtotal, or formula row is worth doing.

Give this a try on your own data!

So as you can see, you don’t need to be a stats expert to use standard deviation in Excel. You just need to know which function to use, how to apply it, and what pitfalls to avoid, and now you’ve got that covered.

Huzzah!

However, the best way to learn this concept properly is to try it out yourself. So make sure to give standard deviation a go in an Excel spreadsheet of your own today, and see what you can learn about your data from the results.

That way, the next time someone asks you for insights, you’ll be ready to answer with more accurate and well-rounded analysis than you could have ever produced with sums and averages alone.

P.S.

Remember, if you want to take your Excel skills to the next level, and even open up new career options, then check out my Complete Excel course.

You’ll learn Excel from scratch including advanced topics like data analysis & modeling, pivot tables, and VBA. You'll build your own professional spreadsheets and charts, and have the skills to get hired as a Data Analyst!

Plus, once you join, you'll have the opportunity to ask questions in our private Discord community from me, other students, and working Data professionals.

Check it out today.

Beginner’s Guide to Standard Deviation in Excel

Travis Cuzick

In This Guide:

What is standard deviation and why should you care?