Excel has two standard deviation functions built in, and which one you use depends on whether your data is a sample or a complete population

If you have a list of numbers in Excel and you want to know how spread out they are from the average, you use the standard deviation function. Excel offers two: STDEV.S for a sample of data, and STDEV.P for an entire population. Most of the time you will use STDEV.S, because you are usually working with a subset of a larger group. The formula is straightforward — you type =STDEV.S(range) where range is the cells containing your numbers, and Excel does the math.

The difference between sample and population matters because it changes how Excel calculates the spread. A sample standard deviation assumes your numbers represent only part of a larger whole, so it uses a slightly different divisor in the calculation to account for that uncertainty. A population standard deviation assumes your numbers are the entire group you care about. If you are measuring the heights of five specific people you care about, use STDEV.P. If you are measuring five people to estimate the height range of all people in your city, use STDEV.S.

Key Takeaways

  • Use =STDEV.S(A1:A10) for sample data, which is the most common choice when your numbers represent a subset of a larger group.
  • Use =STDEV.P(A1:A10) only when your numbers represent the entire population you are measuring.
  • The formula works on any range of cells — you can type the range directly, select it with your mouse, or reference named ranges.
  • Excel ignores empty cells and text in the range, so you do not need to clean your data first.
  • Older Excel files may use STDEV and STDEVP instead of STDEV.S and STDEV.P, but the newer names are preferred.

How to enter the formula in a cell

Click the cell where you want the result to appear. Type the equals sign to start a formula, then type STDEV.S followed by an opening parenthesis. Now select the range of cells containing your numbers — you can click and drag to highlight them, or type the range directly (for example, A1:A50). Close the parenthesis and press Enter. Excel calculates the standard deviation and displays the result in that cell.

If your data is in column A from row 1 to row 20, the formula looks like this: =STDEV.S(A1:A20). If your data is scattered across different cells, you can include multiple ranges separated by commas: =STDEV.S(A1:A10,C1:C10). Excel treats this as one calculation across all the numbers you listed.

Understanding what the number means

Standard deviation tells you how far, on average, your numbers stray from the middle value. A small standard deviation means most of your numbers cluster close to the average. A large standard deviation means your numbers are spread out over a wider range. If you have test scores of 85, 86, 87, 88, and 89, the standard deviation is small because they are all close together. If you have scores of 50, 60, 80, 95, and 100, the standard deviation is much larger because they are scattered.

The standard deviation is measured in the same units as your original data. If you are measuring weight in pounds, the standard deviation is also in pounds. This makes it easier to interpret — a standard deviation of 5 pounds tells you something concrete about how much variation exists in your measurements.

Copying the formula to other cells

Once you have entered the formula in one cell, you can copy it to calculate standard deviation for other ranges without retyping. Click the cell containing your formula, then copy it (Ctrl+C on Windows, Cmd+C on Mac). Select the cells where you want the result to appear and paste (Ctrl+V or Cmd+V). Excel automatically adjusts the cell references in each copy — if the original formula was =STDEV.S(A1:A20), the next row down will become =STDEV.S(A2:A21), and so on.

If you want the formula to always refer to the same range no matter where you copy it, use absolute references by adding dollar signs: =STDEV.S($A$1:$A$20). Now when you copy this formula, the range stays locked to A1:A20 in every copy.

When to use STDEV.P instead of STDEV.S

Use STDEV.P only when your data represents every single item in the group you are measuring. For example, if you have the test scores of all 30 students in one class and you want to know the standard deviation of that exact class, use STDEV.P. If you have the test scores of 30 students and you are using them to estimate what the standard deviation might be for all students in your school, use STDEV.S.

In practice, STDEV.S is the safer choice when you are unsure. Most real-world data is a sample — you are working with a subset of a larger population — so STDEV.S is the right function in most cases. STDEV.P is less common and is usually reserved for specific situations where you genuinely have complete data.

Older Excel versions and alternative function names

If you are using an older version of Excel, you may see STDEV and STDEVP instead of STDEV.S and STDEV.P. These older names still work and do the same thing — STDEV is equivalent to STDEV.S, and STDEVP is equivalent to STDEV.P. Microsoft introduced the newer names to make it clearer which function to use, but both versions are supported.

If you open a file created in an older version of Excel, the formulas will still calculate correctly. However, if you are writing new formulas, use STDEV.S and STDEV.P because they are clearer and are the current standard.

Troubleshooting common errors

If you see #DIV/0! error, it usually means your range contains fewer than two numbers, or all the numbers are identical. Standard deviation requires at least two different values to calculate. If you see #VALUE! error, the range probably contains text or other non-numeric data. Excel ignores empty cells, but it stops if it encounters text in the middle of your range.

If your result looks unusually large or small, double-check that you selected the correct range. A single outlier — one number much larger or smaller than the rest — can significantly increase the standard deviation. This is not an error; it is the formula working correctly. Standard deviation is sensitive to extreme values by design.

Frequently Asked Questions

What is the difference between STDEV.S and STDEV.P?

STDEV.S calculates standard deviation for a sample, assuming your numbers represent part of a larger group. STDEV.P calculates it for a complete population, assuming your numbers are the entire group. Use STDEV.S in most cases unless you are certain your data includes every item you care about.

Can I calculate standard deviation for non-adjacent cells?

Yes. Use commas to separate ranges: =STDEV.S(A1:A10,C1:C10,E5:E15). Excel treats all the numbers across all ranges as one dataset and calculates the standard deviation across them all.

Why is my standard deviation so large?

Standard deviation measures how spread out your numbers are. A large value means your numbers vary widely from the average. Check whether you have any outliers — numbers much larger or smaller than the rest — because even one extreme value can increase standard deviation significantly.

Does Excel ignore empty cells in the range?

Yes. Excel skips empty cells and only calculates based on cells containing numbers. However, if your range contains text, Excel will return a #VALUE! error and stop the calculation.

Can I use STDEV instead of STDEV.S?

Yes, STDEV and STDEV.S do the same thing. STDEV is the older name, and STDEV.S is the newer, clearer name. Both work in current versions of Excel, but STDEV.S is preferred for new formulas.