The two functions that do the work

Excel has two built-in functions for standard deviation: STDEV.S and STDEV.P. The difference matters, and most of the time you want STDEV.S.

STDEV.S calculates standard deviation for a sample — a subset of a larger group. If you have sales data from 12 stores but your company has 50 stores total, those 12 are a sample. STDEV.S is the function you use almost always, because you are usually working with partial data.

STDEV.P calculates standard deviation for an entire population — every single item you are measuring. You use this only when your spreadsheet contains the complete set of data with no missing pieces. In practice, this is rare.

Both functions work the same way: you type the function name, then put your data range in parentheses. If your numbers are in cells A2 through A13, you type =STDEV.S(A2:A13) and press Enter. Excel returns a single number that tells you how spread out your data is.

Key Takeaways

  • Use STDEV.S for any data that represents a sample or subset, which covers almost all real spreadsheets.
  • Use STDEV.P only when your spreadsheet contains every single data point with no gaps or missing values.
  • Type the function name, then list your data range in parentheses: =STDEV.S(A2:A13) and press Enter.
  • Standard deviation tells you how far numbers typically vary from the average, measured in the same units as your data.
  • Excel ignores empty cells and text automatically, but you must remove or handle error values before calculating.

Setting up your data before you calculate

Standard deviation works only on numbers. Before you type the formula, make sure your data column contains only numbers — no text, no blank cells mixed in the middle, no error symbols like #DIV/0!.

If you have a header row (like "Monthly Sales" in cell A1), start your range below it. So if your numbers begin in A2 and end in A13, your formula is =STDEV.S(A2:A13). Excel ignores the header automatically if you include it, but it is cleaner to exclude it from the start.

If your data has gaps — for example, A2 through A10 has numbers, then A11 is blank, then A12 and A13 have numbers — Excel still works correctly. It skips the blank cell and calculates based on the nine numbers it finds. However, if a cell contains an error value (like #N/A or #DIV/0!), the entire formula returns an error. Delete or fix those cells first.

Where to put the formula and what the result means

Click on an empty cell below or to the right of your data. This is where the standard deviation number will appear. Type your formula — for example, =STDEV.S(A2:A13) — and press Enter. Excel calculates when ready and shows you a decimal number.

That number is the standard deviation, measured in the same units as your original data. If your data is in dollars, the standard deviation is in dollars. If it is in pounds, the standard deviation is in pounds. A small standard deviation means your numbers cluster close to the average. A large standard deviation means they are spread far apart.

For example, if you have monthly sales of $5,000, $5,100, $4,900, $5,050, and $4,950, the average is $5,000 and the standard deviation might be around $75. But if your sales are $2,000, $5,000, $8,000, $3,000, and $7,000, the average is still $5,000 but the standard deviation is much larger — around $2,500 — because the numbers vary wildly.

Copying the formula to multiple cells

If you need to calculate standard deviation for several different groups of data, you can copy the formula instead of typing it each time. Type the formula in one cell, then click that cell and drag the small square in the bottom-right corner down or across to the cells where you want the formula to appear.

Excel automatically adjusts the cell references as it copies. If your first formula is =STDEV.S(A2:A13) in cell C2, and you drag down to C3, the new formula becomes =STDEV.S(A3:A14). Make sure this adjustment matches what you actually want — sometimes you need to type each formula separately instead.

You can also copy a cell, select the range where you want it to go, and press Ctrl+V (or Cmd+V on Mac). This is faster when you have many cells to fill.

Common mistakes and how to fix them

The most common error is including text or blank cells in the middle of your data range. If you see #VALUE! as your result, check that every cell in your range contains a number. Delete any text labels or error values, or adjust your range to exclude them.

Another mistake is using STDEV.P when you should use STDEV.S. STDEV.P gives a smaller number because it assumes your data is complete. Unless you are certain your spreadsheet contains every single data point in existence, use STDEV.S instead.

If your formula returns a very small number like 0.0000001, check whether your data actually varies. If all your numbers are identical or nearly identical, standard deviation will be close to zero. This is correct — it means there is almost no spread in your data.

When standard deviation is actually useful

Standard deviation helps you understand whether your data is consistent or erratic. In quality control, a small standard deviation means your manufacturing process is reliable. In sales forecasting, a large standard deviation means demand is unpredictable. In test scores, standard deviation shows whether students performed similarly or with wide variation.

Standard deviation also connects to the normal distribution curve. In a typical dataset, about 68 percent of values fall within one standard deviation of the average, and about 95 percent fall within two standard deviations. This rule lets you spot unusual values — if a data point is three standard deviations away from the average, it is genuinely unusual and worth investigating.

You do not need to understand the math behind standard deviation to use it. You just need to know that it measures spread, and that Excel calculates it for you in one line of code.

STDEV.S versus STDEV.P in plain terms

Think of STDEV.S and STDEV.P as two different questions. STDEV.S asks: "Based on this sample, how much do I expect values to vary in the larger group?" STDEV.P asks: "In this complete set, how much do the values actually vary?"

STDEV.S divides by one fewer number than STDEV.P does. This makes STDEV.S slightly larger, which accounts for the fact that a sample usually underestimates variation in the full population. If you use STDEV.P on a sample, you will get a number that is too small and misleading.

The older Excel functions STDEV and STDEVP do the same thing as STDEV.S and STDEV.P. Excel still supports them for backward compatibility, but the newer names are clearer about what they do, so use those instead.

Frequently Asked Questions

What is the difference between standard deviation and variance?

Variance is the square of standard deviation. Excel has VAR.S and VAR.P functions that calculate variance. Standard deviation is easier to interpret because it is in the same units as your data, so use that unless you have a specific reason to work with variance.

Can I calculate standard deviation for text data or categories?

No. Standard deviation only works on numbers. If you have categories like "red," "blue," and "green," you cannot calculate standard deviation. You can count how many of each category you have, but you cannot measure how spread out they are.

Why does my standard deviation formula show an error?

The most common cause is a cell containing text, an error value, or a formula that returns an error. Check each cell in your range and delete or fix any non-numeric values. Also make sure you are using STDEV.S or STDEV.P, not a misspelled function name.

Should I use STDEV.S or STDEV.P for my data?

Use STDEV.S unless you are absolutely certain your spreadsheet contains every single data point with no missing values. In business, science, and most real-world work, you are almost always working with a sample, so STDEV.S is the right choice.

Can I calculate standard deviation for data in different columns?

Yes. Instead of a single range like A2:A13, you can list multiple ranges separated by commas: =STDEV.S(A2:A13,C2:C13). Excel treats all the numbers as one group and calculates standard deviation across all of them.