How to Calculate Standard Deviation in Excel (STDEV.S or STDEV.P)

An average on its own can fool you. Two weeks of social posts might both average the same number of likes, but one week could be steady while the other had one post that flew and four that flopped. Standard deviation is the number that tells those two weeks apart.

Microsoft describes it as a measure of how widely values are spread out from the mean. The good news is that Excel does all the maths for you. The only real decision is which of two functions to use, and that's where most people get stuck.

STDEV.S or STDEV.P: choosing the right Excel standard deviation

Excel has two main standard deviation functions, and they give slightly different answers from the same numbers.

Function	Use it when	Method Microsoft lists
STDEV.S	Your numbers are a sample of something bigger	n-1
STDEV.P	Your numbers are the whole group	n

Here's how I'd think about it. If you've surveyed some of your customers, or you're looking at a few weeks out of a whole year of posts, that's a sample, so use STDEV.S. If you're looking at every single order from one product launch and nothing else, that's the whole population, so STDEV.P fits.

If you're not sure, STDEV.S is the safer everyday choice. Microsoft also notes that for large data sets the two return approximately equal values, so the choice matters most when you've only got a handful of numbers.

Calculating standard deviation in Excel with STDEV.S

1. Put your numbers in one column, for example A2 down to A10.
2. Click an empty cell where you want the answer.
3. Type =STDEV.S(A2:A10) and press Enter.

You can also type the function name, then drag over your cells with the mouse before closing the bracket. Microsoft says STDEV.S can take up to 254 number arguments, or a single range like the one above, which is what you'll use nearly every time.

Calculating standard deviation in Excel with STDEV.P

The steps are identical, just with the other function name: =STDEV.P(A2:A10). Use it when your list is the complete set, not a slice of something bigger. A good example is the full list of attendees at a single workshop you ran, where nobody is missing from the data and you only care about that one group.

A worked standard deviation example to check in Excel

Microsoft's help pages use the same ten breaking-strength readings for both functions: 1345, 1301, 1368, 1322, 1310, 1370, 1318, 1350, 1303 and 1299. Here's what each function returns:

Formula	Result Microsoft shows
=STDEV.S on the ten readings	27.46391572
=STDEV.P on the ten readings	26.05455814

Same numbers, two answers. The sample version is a little higher because it uses the n-1 method, which allows for the fact that a sample might not capture the full spread. If you type these ten numbers into a sheet yourself, you can check you get the same results before you trust a formula on your own data.

What Excel standard deviation ignores in your cells

This catches people out when a result looks odd. Microsoft says that when you point STDEV.S or STDEV.P at a range, only numbers count. Empty cells, TRUE and FALSE values, text and error values in that range are ignored.

So if some of your numbers were pasted in as text, perhaps from an export, Excel will skip them without warning. If you want TRUE, FALSE and numbers stored as text in a range to be counted, Microsoft points to STDEVA for samples and STDEVPA for populations. For most business data, though, you'll be better off fixing the cells so they hold real numbers.

Reading your standard deviation result in Excel

The number on its own means little until you compare it with the average. Put =AVERAGE(A2:A10) next to it. A small standard deviation compared with the average means your numbers sit close together and are fairly consistent. A large one means they're all over the place.

For creators and small business owners, that's useful for questions like these:

• Are my weekly sales steady, or do a few big weeks hide lots of quiet ones?
• Is engagement consistent across my posts, or is one viral post skewing the average?
• Do delivery times vary a lot between suppliers?

If you mostly work in Google Sheets, the basics carry across, and this guide to totalling a column in Google Sheets is a good starting point before you move on to spread and averages.

The older STDEV function in Excel

You may see plain STDEV in older templates or online guides. Microsoft says it's still there for compatibility, but it may not be available in future versions of Excel. If you're building something new, use STDEV.S instead, which does the sample calculation.

If your Excel standard deviation looks wrong

1. Getting a divide error? You probably have only one number in the range, or the cells hold text rather than numbers. A sample needs at least two values to measure any spread.
2. Result seems too small? Check whether some values are stored as text and being skipped.
3. Getting an error? Microsoft says text that can't be turned into a number, typed straight into the function, causes errors.
4. Two people got different answers? One may have used STDEV.S and the other STDEV.P.

If you're tracking results with a simple tick list as you go, adding checkboxes in Google Sheets is a quick way to mark which weeks you've already reviewed.

Frequently asked questions

What is the formula for standard deviation in Excel?

Use =STDEV.S(A2:A10) for a sample or =STDEV.P(A2:A10) for a whole population, changing the range to match your cells.

Should I use STDEV.S or STDEV.P?

Use STDEV.S when your data is a sample of a bigger group, and STDEV.P when it's the complete group. STDEV.S is the safer default if you're unsure.

Why are STDEV.S and STDEV.P different?

Microsoft says STDEV.S uses the n-1 method and STDEV.P uses the n method, so the sample version comes out slightly higher on small data sets.

Does Excel standard deviation ignore blank cells?

Yes. Microsoft says empty cells, logical values, text and error values in a range are ignored, and only numbers are counted.

Is the STDEV function still OK to use?

It works for compatibility, but Microsoft says it may not be available in future versions, so use STDEV.S for new work.