Standard deviation measures how spread out your numbers are from the average

Standard deviation tells you whether your data points cluster tightly around the average or scatter widely. In Google Sheets, you calculate it with a single function: STDEV. If your numbers are close together, standard deviation is small. If they're far apart, it's large. This matters when you're comparing consistency — say, whether one supplier's delivery times are more reliable than another's, or whether test scores in one class vary more than in another.

Google Sheets offers two versions of the standard deviation function. STDEV.S calculates standard deviation for a sample (a subset of data), and STDEV.P calculates it for a population (all the data you have). Most of the time, you'll use STDEV.S. Use STDEV.P only when you're measuring every single item in the group you care about — not a sample of it.

Key Takeaways

  • Type =STDEV.S(range) to calculate standard deviation for a sample, where range is the cells containing your numbers.
  • Use STDEV.P instead if you're measuring an entire population, not a sample of it.
  • Standard deviation appears in the cell where you typed the formula and updates automatically if you change any numbers in the range.
  • You can calculate standard deviation for multiple groups in the same sheet by using separate formulas in different cells.

The basic formula: STDEV.S for most situations

Open your Google Sheet and click the cell where you want the standard deviation to appear. Type the formula exactly as shown: =STDEV.S(A2:A10), replacing A2:A10 with the actual range of cells holding your numbers. Press Enter. Google Sheets calculates the result and displays it in that cell.

The range can be as small as two numbers or as large as your sheet allows. You can also type the numbers directly into the formula if you have only a few: =STDEV.S(5,8,12,9,6) works just as well as pointing to a range. If your data is in a column, use the column letter and the row numbers (A2:A100). If it's in a row, use the same format with row numbers (B5:H5).

When to use STDEV.P instead of STDEV.S

Use STDEV.P only when you have measured or counted every single item in the group you're studying. This is rare in practice. If you're looking at sales data for all 12 months of a year, that's a population — use STDEV.P. If you're looking at 50 customer transactions out of thousands, that's a sample — use STDEV.S.

The difference between the two formulas is small when you have many data points, but it matters with small datasets. STDEV.S produces a slightly larger number than STDEV.P because it accounts for the fact that a sample may not perfectly represent the whole population. When in doubt, use STDEV.S.

Calculating standard deviation for multiple groups

If you have several columns of data and want to compare how spread out each one is, put a separate formula in a different cell for each group. For example, if column A holds January sales and column B holds February sales, type =STDEV.S(A2:A31) in cell D2 and =STDEV.S(B2:B28) in cell D3. You can now compare the two standard deviations side by side.

Label each result clearly so you remember which standard deviation belongs to which group. Add a label in column C like "January SD" and "February SD" next to your formulas. This makes it straightforward to see at a glance which month had more variation in sales.

What to do if your formula returns an error

If you see #DIV/0! or #VALUE!, check three things. First, make sure every cell in your range contains a number, not text. A cell with the word "pending" or a blank space will break the formula. Second, verify that you have at least two numbers in your range — standard deviation needs at least two data points. Third, check that your range reference is correct: A2:A10 means cells A2 through A10, not A2 and A10 alone.

If one cell in your range legitimately contains text (like a header), exclude it from the formula. Instead of =STDEV.S(A1:A10), use =STDEV.S(A2:A10) to skip the header in A1. Google Sheets will ignore any cells that contain text and calculate standard deviation using only the numbers.

Standard deviation changes when your data changes

The beauty of a formula is that it updates automatically. If you change any number in the range, the standard deviation recalculates when ready. This is useful when you're testing different scenarios — you can adjust a few data points and watch how the standard deviation responds, which helps you understand how sensitive your results are to changes.

You can also copy a formula to other cells. Click the cell with your formula, then drag the small square in the bottom-right corner down or across to copy it. Google Sheets adjusts the range automatically — if you copy =STDEV.S(A2:A10) down to the next row, it becomes =STDEV.S(A3:A11). This saves time when you're calculating standard deviation for many similar groups.

Why standard deviation matters for your data

Standard deviation is most useful when you're comparing two groups or tracking consistency over time. A restaurant might calculate the standard deviation of delivery times to see whether they're reliable. A teacher might compare standard deviation of test scores across two classes to see which group had more varied performance. A manufacturer might track standard deviation of product weights to spot when quality control is drifting.

Standard deviation works best with numerical data that follows a roughly normal distribution — data that clusters around a middle value rather than bunching at the extremes. If your data has extreme outliers or is heavily skewed, standard deviation alone may not tell the full story, and you might want to look at other measures like the range or interquartile range alongside it.

Frequently Asked Questions

What's the difference between STDEV.S and STDEV?

In Google Sheets, STDEV and STDEV.S do the same thing — both calculate sample standard deviation. Google Sheets accepts both names for backward compatibility with older spreadsheets. Use whichever you prefer; the result is identical. STDEV.P is the only one that's truly different, and it's for populations, not samples.

Can I calculate standard deviation for cells that aren't next to each other?

Yes. Use a comma to separate multiple ranges: =STDEV.S(A2:A10,C2:C10) calculates standard deviation for both column A and column C together, treating all the numbers as one group. If you want separate standard deviations for each column, use separate formulas in different cells instead.

Why is my standard deviation so large?

A large standard deviation means your numbers are spread far apart from the average. This isn't wrong — it's information. Check whether you have any extreme outliers (unusually high or low numbers) that are pulling the standard deviation up. You can also compare your standard deviation to the average to get a sense of scale: if the average is 100 and standard deviation is 50, that's high variation.

Do I need to sort my data before calculating standard deviation?

No. Standard deviation doesn't care about the order of your numbers. You can calculate it on unsorted data, and the result will be the same as if you sorted it first. The formula looks at how far each number is from the average, regardless of where it sits in the list.