How to Calculate Standard Deviation in Excel Using STDEV and STDEV.S

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

The STDEV.S function calculates standard deviation for a sample of data — the most common choice when you're working with a subset of a larger group. The STDEV.P function calculates standard deviation for an entire population. In Excel 2007 and later, STDEV.S is the modern version of the older STDEV function, which still works but is considered outdated.

To calculate standard deviation in Excel, enter a formula like =STDEV.S(A1:A10) in any empty cell, replacing A1:A10 with the range of your actual numbers. Press Enter, and Excel returns the result. The formula works the same way whether your data spans a single row, a single column, or a rectangular block of cells.

Key Takeaways

Use STDEV.S when your numbers represent a sample from a larger group; use STDEV.P only when you have the entire population.
Enter the formula =STDEV.S(A1:A10) in an empty cell, replacing the range with your actual data location, then press Enter.
Excel ignores empty cells and text within your range, but will return an error if the range contains fewer than two numbers.
You can calculate standard deviation for non-adjacent cells by separating ranges with a semicolon, like =STDEV.S(A1:A5;C1:C5).

When to use STDEV.S versus STDEV.P

Most of the time, you will use STDEV.S. This function assumes your numbers are a sample — a subset drawn from a larger group. If you surveyed 50 customers out of 10,000, or measured 20 products from a production run of 500, your data is a sample. STDEV.S divides by one less than the count of numbers, which gives a more realistic estimate of variation in the larger population.

STDEV.P is for when you have every single data point in the group you care about. If you are calculating the standard deviation of sales across all 12 months of a year, or the heights of all students in a specific classroom, you have the complete population. STDEV.P divides by the exact count of numbers. In practice, STDEV.P is rarely used outside of academic settings or when working with closed, finite groups.

If you are unsure which to use, STDEV.S is the safer choice. It produces a slightly larger result than STDEV.P, but that difference shrinks as your dataset grows larger.

How to enter the formula step by step

Click on an empty cell where you want the result to appear. Type the equals sign to start a formula: =STDEV.S(. Then select the range of cells containing your numbers — you can click and drag to highlight them, or type the range directly. Close the parenthesis and press Enter.

For example, if your numbers are in cells B2 through B15, you would type =STDEV.S(B2:B15) and press Enter. Excel calculates the result and displays it in that cell. The formula bar at the top shows the formula you entered, so you can verify it is correct.

If you need to calculate standard deviation for multiple columns or non-adjacent ranges, separate each range with a semicolon. For instance, =STDEV.S(A1:A10;C1:C10) calculates the standard deviation of all 20 numbers from both ranges combined, treating them as one dataset.

What happens when your data has empty cells or text

Excel automatically ignores empty cells within your range, so they do not affect the calculation. If a cell contains text, Excel skips it as well. This means you can include a range with labels or gaps without having to clean it up first.

However, if your range contains fewer than two numbers, Excel returns a #DIV/0! error. Standard deviation requires at least two data points to calculate, so a single number or an empty range will fail. If you see this error, check that your range actually contains at least two numeric values.

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 datasets. Click the cell containing your formula, then copy it (Ctrl+C on Windows, Command+C on Mac). Select the cells where you want the formula to appear and paste (Ctrl+V or Command+V).

Excel automatically adjusts the cell references in each copy. If your original formula was =STDEV.S(A1:A10) and you paste it one row down, it becomes =STDEV.S(A2:A11). To keep the range fixed while copying, add dollar signs: =STDEV.S($A$1:$A$10) will not change when copied.

Understanding what the result means

Standard deviation measures how spread out your numbers are from the average. A small standard deviation means most of your numbers cluster close to the average. A large standard deviation means your numbers are scattered across a wider range.

The result is in the same units as your original data. If you are measuring heights in inches, the standard deviation is also in inches. If you are working with dollar amounts, the standard deviation is in dollars. This makes it easier to interpret than some other statistical measures.

Common mistakes and how to fix them

The most frequent error is using STDEV instead of STDEV.S or STDEV.P. While STDEV still works in modern Excel, it is the old function and Microsoft recommends using STDEV.S instead. If you type STDEV, Excel may show a warning or autocomplete to suggest STDEV.S.

Another common issue is including the wrong range. Double-check that your formula covers all the numbers you want to include. If you have data in columns A and B but only included column A, your result will be incomplete. Click on the cell with the formula and look at the formula bar to verify the range is correct.

If your result looks unusually large or small, check whether your data contains any extreme outliers — very high or very low numbers that pull the standard deviation up. Standard deviation is sensitive to outliers, so a single very different value can significantly change the result.

Frequently Asked Questions

What is the difference between STDEV and STDEV.S?

STDEV is the older function that Excel still supports for backward compatibility. STDEV.S is the modern replacement and works identically. Microsoft recommends using STDEV.S in new spreadsheets. Both calculate sample standard deviation by dividing by one less than the count of numbers.

Can I calculate standard deviation for data in different sheets?

Yes. Include the sheet name in your formula, like =STDEV.S(Sheet1.A1:A10;Sheet2.B1:B10) on Windows or =STDEV.S(Sheet1.A1:A10;Sheet2.B1:B10) on Mac. Excel treats data from multiple sheets as a single combined dataset.

Why does my formula show #DIV/0! error?

This error appears when your range has fewer than two numbers. Standard deviation cannot be calculated from a single value or empty cells. Check that your range includes at least two numeric values and that you did not accidentally select text-only cells.

Should I use STDEV.P if I have a very large dataset?

No. The choice between STDEV.S and STDEV.P depends on whether your data is a sample or a complete population, not on how many numbers you have. Use STDEV.S for samples and STDEV.P only when you have the entire population you are studying.

This guide is for general information only and is not medical, financial, legal, or other professional advice. For decisions specific to your situation, consult a qualified professional. See our Editorial Policy.