Excel has two functions for standard deviation, and which one you use depends on your data

Excel offers STDEV.S for a sample of data and STDEV.P for an entire population. STDEV.S is the one most people need — it calculates how spread out your numbers are from the average, assuming your data is a subset of a larger group. STDEV.P is for when you have the complete dataset with no data left out. The older functions STDEV and STDEVP still work but are no longer the standard choice in newer Excel versions.

The practical difference matters. STDEV.S gives a slightly larger result than STDEV.P because it accounts for the fact that a sample tends to be less varied than the full population. If you are working with sales figures from three months out of a year, or test scores from one class, use STDEV.S. If you have measurements from every single item you are measuring, use STDEV.P.

Key Takeaways

  • Use STDEV.S when your data is a sample, such as monthly sales from one quarter or test scores from one group of students.
  • Use STDEV.P only when you have the complete population with no data excluded.
  • The formula syntax is =STDEV.S(range) or =STDEV.P(range), where range is the cells containing your numbers.
  • Standard deviation tells you how far numbers typically fall from the average — a smaller number means data points cluster close together, a larger number means they spread out.
  • Excel ignores empty cells and text in the range, but will return an error if the range contains no numbers or only one number.

How to enter the formula in a cell

Click the cell where you want the result to appear. Type =STDEV.S( and then select the range of cells containing your numbers. You can do this by clicking and dragging across the cells, or by typing the cell references directly — for example, =STDEV.S(A2:A50) for cells A2 through A50. Close the parenthesis and press Enter.

If your data is scattered across non-adjacent cells, separate each range with a comma: =STDEV.S(A2:A10,C2:C10). Excel will calculate the standard deviation across all the numbers you included, treating them as one dataset.

What the result actually means

Standard deviation is measured in the same units as your data. If you are measuring heights in inches, the standard deviation is in inches. If you are measuring sales in dollars, it is in dollars. A standard deviation of 5 means that, on average, your data points fall about 5 units away from the mean.

In a normal distribution, about 68 percent of your data falls within one standard deviation of the average, and about 95 percent falls within two standard deviations. So if your average is 100 and your standard deviation is 10, you would expect most of your data to land between 90 and 110. This makes standard deviation useful for spotting outliers or understanding how consistent your data is.

Common reasons the formula returns an error

If you see #DIV/0!, the range contains fewer than two numbers. Standard deviation requires at least two data points to calculate — a single number has no spread. Add more data or check that your range is correct.

If you see #VALUE!, the range contains text that Excel cannot convert to a number. Excel ignores empty cells and text labels automatically, but if a cell contains text mixed with numbers (like "5 inches"), the formula will fail. Clean the data by removing text from numeric cells, or select only the cells that contain pure numbers.

Using STDEV.S versus STDEV.P in practice

Most real-world situations call for STDEV.S. You are almost always working with a sample — last month's sales, this semester's grades, this week's website traffic. Even if you have a large dataset, it usually represents a sample of all possible data you could collect. STDEV.P is rare outside of specific fields like quality control where you measure every single unit produced.

If you are unsure which to use, STDEV.S is the safer choice. It produces a more conservative estimate of spread and is what most statistical analysis assumes. The difference between the two shrinks as your dataset gets larger, so with hundreds or thousands of data points, the choice matters less.

Comparing standard deviation to other spread measures

Excel also offers variance (VAR.S and VAR.P), which is the standard deviation squared. Variance is harder to interpret because it is in squared units, but it is useful in some statistical calculations. Range (MAX minus MIN) tells you the distance between your highest and lowest values, but ignores everything in between. Interquartile range (using QUARTILE) shows the spread of the middle 50 percent of your data.

Standard deviation is the most common choice because it accounts for all your data and is easy to understand. If you need to compare spread across datasets with different units or scales, you might use coefficient of variation instead, which is standard deviation divided by the mean — but that requires a separate calculation.

Frequently Asked Questions

What is the difference between STDEV and STDEV.S?

STDEV is the older function name and STDEV.S is the current standard. They calculate the same thing — standard deviation of a sample. Excel still accepts STDEV, but Microsoft recommends using STDEV.S in new spreadsheets for clarity and consistency with other statistical functions.

Can I calculate standard deviation for data in different sheets?

Yes. Reference the other sheet by typing the sheet name followed by an exclamation point and the cell range: =STDEV.S(Sheet2!A2:A50). If the sheet name contains spaces, wrap it in single quotes: =STDEV.S('Sheet 2'!A2:A50).

Why is my standard deviation zero?

All your numbers are identical. If every cell in your range contains the same value, there is no spread, so standard deviation is zero. Check that your data actually varies, or verify you selected the correct range.

Does standard deviation work with negative numbers?

Yes. Standard deviation treats negative numbers the same as positive ones. A dataset with values -10, 0, and 10 has the same standard deviation as one with 10, 20, and 30 — the spread from the average is what matters, not the direction.