Excel's standard deviation functions measure 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 Excel, you calculate it with a single function: STDEV for a sample of data, or STDEVP for an entire population. The difference matters only if you are working with a complete dataset (population) versus a subset (sample). Most real-world work uses STDEV because you are almost always working with a sample — a month of sales, a batch of test scores, a week of temperature readings.

The function returns a single number. A small standard deviation means your values are close to the average. A large one means they are scattered. If you track daily website visitors and get an average of 500 with a standard deviation of 50, most days fall between 450 and 550. If the standard deviation is 200, you could see anywhere from 300 to 700 on any given day.

Key Takeaways

  • Use =STDEV(A1:A10) to calculate standard deviation for a sample, which is what most spreadsheets contain.
  • Use =STDEVP(A1:A10) only if your data represents an entire population with no missing values.
  • Standard deviation works on any range of numbers — you can select cells in any order, and Excel ignores text and blank cells automatically.
  • The result appears in a single cell and updates automatically if you change any of the numbers in your range.

The two functions: STDEV versus STDEVP

STDEV calculates standard deviation for a sample. Use this in almost every real situation: monthly sales figures, test scores from a class, daily temperatures, survey responses. A sample is any dataset that represents a larger group but does not include every single member of that group. Excel's STDEV function uses the formula that accounts for the fact that you are working with incomplete data.

STDEVP calculates standard deviation for a population — the entire group with no gaps. Use this only when you have every single data point: all 12 months of a year's sales (if you are analyzing that specific year, not predicting next year), every student in a classroom (not a sample of students), all items in a finished batch. STDEVP returns a slightly smaller number than STDEV because it does not adjust for missing data.

If you are unsure which to use, choose STDEV. Most spreadsheets contain samples, not complete populations. The difference between the two functions shrinks as your dataset grows larger, so for datasets with more than 30 rows, the results are nearly identical.

How to enter the formula in your spreadsheet

Click the cell where you want the standard deviation to appear. Type =STDEV( and then select the range of cells containing your numbers. You can click and drag to select them, or type the range directly — for example, =STDEV(A1:A50) to include cells A1 through A50. Close the parenthesis and press Enter.

Excel accepts ranges in any order. =STDEV(A1:A10) and =STDEV(A10:A1) produce the same result. You can also select non-adjacent cells by holding Ctrl (or Command on Mac) and clicking each cell individually, then type the cell references separated by semicolons: =STDEV(A1;A3;A5).

The formula ignores text, blank cells, and logical values automatically. If your range contains a mix of numbers and text, Excel calculates the standard deviation using only the numbers. This means you do not have to clean your data first — the function handles it.

Understanding what the number means

Standard deviation is measured in the same units as your original data. If you calculate standard deviation for a column of prices in dollars, the result is in dollars. If your column contains test scores from 0 to 100, the result is on that same 0-to-100 scale. This makes it straightforward to interpret: a standard deviation of 5 on a 0-to-100 test means scores cluster tightly; a standard deviation of 25 means they are spread out.

A rough rule: about 68 percent of your data falls within one standard deviation of the average, and about 95 percent falls within two standard deviations. If your average is 100 and your standard deviation is 10, you expect roughly 68 percent of your values to fall between 90 and 110. This rule holds for data that follows a normal (bell-curve) distribution, which most real-world data approximates.

Standard deviation becomes useful when you compare two datasets. If one group of test scores has an average of 75 with a standard deviation of 5, and another group has an average of 75 with a standard deviation of 15, both groups averaged the same but the first group performed much more consistently. The second group had some very high and very low scores.

Copying the formula to other cells

Once you enter the formula in one cell, you can copy it to calculate standard deviation for other ranges. Click the cell containing your formula, then copy it (Ctrl+C or Command+C). Select the cells where you want the formula to appear and paste (Ctrl+V or Command+V). Excel automatically adjusts the cell references for each row or column.

For example, if you enter =STDEV(A1:A10) in cell C1 and copy it down to C2, Excel changes it to =STDEV(A2:A11) in C2. This automatic adjustment works as long as your data is organized in rows or columns. If you want the formula to always reference the same range, use absolute references: =STDEV($A$1:$A$10). The dollar signs tell Excel not to change those cell references when you copy.

Common mistakes and how to avoid them

The most frequent error is using STDEVP when you should use STDEV. Unless you have genuinely captured every single data point in your population, use STDEV. Mixing the two functions across different columns or time periods creates inconsistent results.

Another mistake is including headers or labels in your range. If row 1 contains the text "Sales" and rows 2 through 11 contain numbers, use =STDEV(A2:A11), not =STDEV(A1:A11). Excel will ignore the text, but it is cleaner to exclude it from the start. Similarly, do not include totals or averages in your range — they skew the standard deviation.

A third error is forgetting that standard deviation requires at least two data points. If you try to calculate =STDEV(A1:A1) with only one cell, Excel returns an error. You need a minimum of two numbers for the function to work.

When standard deviation is the right tool

Use standard deviation when you need to understand consistency or variability in your data. It answers questions like: Are my sales figures stable month to month, or do they swing wildly? Do my employees' productivity scores cluster around an average, or is there huge variation? Are my manufacturing tolerances tight or loose?

Standard deviation is less useful for comparing datasets with different units or scales. If you want to compare the variability of height (measured in inches) to the variability of weight (measured in pounds), standard deviation alone will mislead you because the units are different. In that case, use coefficient of variation, which divides standard deviation by the average — but that is a separate calculation beyond this guide.

Frequently Asked Questions

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

Excel offers updated function names: STDEV.S (sample) and STDEV.P (population). These are identical to STDEV and STDEVP but use clearer naming. Either set works; STDEV.S and STDEV.P are straightforward the newer versions. Use whichever your version of Excel supports or whichever you find clearer.

Can I calculate standard deviation for data in multiple columns?

Yes. You can select non-adjacent ranges by holding Ctrl (or Command) and clicking each range, then enter the formula. For example, =STDEV(A1:A10;C1:C10) calculates standard deviation across both columns as if they were one dataset. This treats all 20 numbers as a single group.

Why does my standard deviation seem very large?

A large standard deviation usually means your data is genuinely spread out. Check whether you included outliers — extreme values that do not represent typical data. If a dataset contains one unusually high or low number, it inflates standard deviation. You can calculate standard deviation twice: once with the outlier and once without, to see how much it affects your result.

Does standard deviation work with negative numbers?

Yes. Standard deviation treats negative numbers the same as positive ones. If your data includes both gains and losses, or temperatures above and below zero, the formula handles them correctly. The result is always positive because standard deviation measures distance from the average, not direction.