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 Excel, you calculate it in one line using either STDEV.S (for a sample of data) or STDEV.P (for an entire population). Most of the time you will use STDEV.S, because you are usually working with a sample rather than every single data point that exists.

The practical difference: if you are measuring test scores from 30 students in one class, use STDEV.S. If you are measuring test scores from every student in the entire school district, use STDEV.P. The sample version (STDEV.S) gives a slightly larger result because it accounts for the fact that a sample might not perfectly represent the whole population.

Key Takeaways

  • Type =STDEV.S(A1:A10) into any cell to calculate standard deviation for a sample of data in cells A1 through A10.
  • Use STDEV.S for most real-world situations where you have a subset of data, and STDEV.P only when you have the complete population.
  • Standard deviation appears as a single number in your cell — a larger number means your data is more spread out, a smaller number means it clusters near the average.
  • You can calculate standard deviation for non-contiguous cells by separating ranges with semicolons, like =STDEV.S(A1:A10;C1:C10).

The basic formula: selecting your data range

Open your spreadsheet and click on an empty cell where you want the result to appear. Type the equals sign to start a formula, then type STDEV.S followed by parentheses. Inside the parentheses, type the range of cells containing your numbers — for example, A1:A50 if your data runs from row 1 to row 50 in column A.

The formula looks like this: =STDEV.S(A1:A50). Press Enter, and Excel calculates the standard deviation when ready. The result appears in that cell as a single decimal number.

You can also click and drag to select your data range instead of typing it. Click the cell where you want the result, type =STDEV.S(, then click the first cell of your data and drag to the last cell. Excel fills in the range automatically. Type the closing parenthesis and press Enter.

When to use STDEV.S versus STDEV.P

Use STDEV.S when your data is a sample — a subset chosen from a larger group. This applies to most business and research situations: survey responses from 200 customers (not all customers), monthly sales from one store (not all stores), or test scores from one class (not all classes). STDEV.S assumes your data might not perfectly represent the whole population, so it adjusts the calculation slightly upward.

Use STDEV.P only when you have data for the entire population you care about. This is rare in practice. You would use it if you measured every single employee's salary in your company (the complete population of your company), or every product sold in your store last month (the complete population of last month's sales). The formula is identical except for the P: =STDEV.P(A1:A50).

If you are unsure which to use, choose STDEV.S. It is the safer choice for most real-world data because you are almost always working with a sample rather than a complete population.

Calculating standard deviation for multiple separate ranges

Sometimes your data is not in one continuous block. You might have numbers in cells A1:A10 and also in cells C1:C10, with empty space between them. You can calculate standard deviation for both ranges at once by separating them with a semicolon.

Type: =STDEV.S(A1:A10;C1:C10). Excel treats both ranges as one dataset and calculates a single standard deviation that includes all the numbers from both areas. This is faster than calculating each range separately and then combining them manually.

You can add as many ranges as you need: =STDEV.S(A1:A10;C1:C10;E1:E10) would include three separate blocks of data. Just remember to use semicolons (or commas, depending on your regional Excel settings) to separate each range.

Understanding what your standard deviation number means

Standard deviation is measured in the same units as your data. If you are measuring heights in inches, your standard deviation will be in inches. If you are measuring test scores from 0 to 100, your standard deviation will be a number between 0 and 100.

A small standard deviation (close to zero) means your numbers cluster tightly around the average. A large standard deviation means your numbers are spread out — some much higher, some much lower than the average. For example, if test scores average 75 with a standard deviation of 5, most students scored between 70 and 80. If the standard deviation is 15, scores ranged much more widely, from perhaps 60 to 90.

Standard deviation does not tell you the highest or lowest value in your dataset. It only describes how tightly or loosely the numbers group around the middle. Two datasets can have the same average but very different standard deviations.

Common mistakes and how to avoid them

The most common error is including text or empty cells in your range. If you select A1:A50 but some cells contain words instead of numbers, Excel ignores those cells and calculates based only on the numbers it finds. This usually gives you a result, but it might not be what you intended. Check that your range contains only numbers before pressing Enter.

Another mistake is using STDEV.P when you should use STDEV.S. The results look similar but are slightly different, and using the wrong one can lead to incorrect conclusions, especially with small datasets. When in doubt, use STDEV.S.

Do not confuse standard deviation with standard error. Standard error measures how far your sample average might be from the true population average. It is a different calculation: =STDEV.S(A1:A50)/SQRT(COUNT(A1:A50)). Standard deviation describes spread within your data; standard error describes uncertainty about your average.

Using standard deviation to find outliers

Standard deviation helps you spot unusual values in your dataset. A common rule is that values more than two standard deviations away from the average are unusual, and values more than three standard deviations away are very unusual.

Calculate your average using =AVERAGE(A1:A50) and your standard deviation using =STDEV.S(A1:A50). Then look for any data points that are more than two or three times the standard deviation above or below the average. For example, if your average is 100 and your standard deviation is 10, a value of 130 (three standard deviations above) is worth investigating — it might be a data entry error, or it might be a genuinely unusual case that deserves attention.

Frequently Asked Questions

What is the difference between STDEV and STDEV.S?

STDEV is an older Excel function that works the same way as STDEV.S. Microsoft kept STDEV for backward compatibility with older spreadsheets, but STDEV.S is the current standard name. Both calculate sample standard deviation identically. Use STDEV.S in new spreadsheets because it is clearer what you are calculating.

Can I calculate standard deviation for a column that has some empty cells?

Yes. Excel automatically skips empty cells and calculates based only on cells containing numbers. If you select A1:A100 but only 80 cells have numbers, Excel uses those 80 values. Make sure you are not accidentally including cells with text, which Excel also ignores.

Why are my STDEV.S and STDEV.P results different?

STDEV.S divides by one less than the number of data points, while STDEV.P divides by the exact number of data points. This makes STDEV.S slightly larger. The difference shrinks as your dataset gets bigger. With 1,000 data points, the difference is tiny; with 10 data points, it is more noticeable.

How do I calculate standard deviation if my data is in rows instead of columns?

The formula works exactly the same way. If your data runs across row 5 from column A to column Z, type =STDEV.S(A5:Z5). Excel handles rows and columns identically — you just specify the range differently.

Can I use standard deviation to compare two datasets?

Standard deviation alone is not enough to compare datasets fairly. Two groups might have the same standard deviation but very different averages, or the same average but very different spreads. Calculate both the average and standard deviation for each group, then compare them together to understand the full picture.