How To Calculate Standard Deviation In Excel

This guide shows you how to calculate standard deviation in Excel using the correct formula for your data. You will learn the difference between sample and population formulas, how to avoid common errors, and how to read your results with confidence.

Fast Answer

Use STDEV.S for sample data: =STDEV.S(range)
Use STDEV.P for full population: =STDEV.P(range)

5–10 minutes Time needed

Beginner Difficulty

Wrong formula choice Watch out for

Before You Start

Microsoft Excel 2013 or newer (or Excel for Microsoft 365)
A spreadsheet with your numbers typed into a single column or row
No blank cells, text, or errors inside the number range you plan to use

Check first: Decide whether your data is a sample or a full population. Choosing the wrong formula gives a misleading result, even if Excel shows no error.

Step-by-Step Instructions

Open Excel and enter your data

Launch Excel and open the workbook that contains your numbers. If you are starting fresh, click into cell A1 and type your first value. Press Enter, then continue down the column for each remaining number.

You can also place data in a row, such as cells A1 through J1. Excel handles either layout, but a vertical column is easiest to read and reference in formulas.

Tip: Keep the range tidy. Remove blank rows or cells marked with a dash, because Excel treats those as missing values and can produce the #DIV/0! error.

Identify whether you have a sample or a population

Ask yourself one question: do these numbers represent every possible observation, or just a smaller group taken from a larger set? If you measured all students in one class, that is a population. If you surveyed 50 shoppers out of thousands, that is a sample.

Sample data uses STDEV.S, which divides by n-1. Population data uses STDEV.P, which divides by n. The difference matters most with smaller data sets, where the two results can differ noticeably.

Most business, research, and test-score scenarios involve samples, so STDEV.S is the safer default unless you are certain you have complete data.

Caution: STDEV (the old function without a suffix) still works in Excel, but it matches STDEV.S. Microsoft recommends using the newer named functions to avoid confusion.

Click an empty cell for the result

Choose a cell that will not interfere with your data, such as two columns to the right of your last number. Click that cell once so it becomes the active cell.

This is where your formula will live. You do not need to select the data range first by highlighting it, though you can if it helps you visualise the formula.

Tip: Use cell B1 or B2 for a vertical column of data, so the result stays visible without scrolling.

Type the STDEV.S formula for sample data

Type an equals sign, then the letters STDEV.S, then an opening parenthesis. Your formula should look like this: =STDEV.S(

Now select the range. If your data is in cells A1 through A10, click A1, hold the mouse button, and drag down to A10. Excel will insert the range automatically. Alternatively, type A1:A10 manually.

Close the parenthesis and press Enter. Excel shows the standard deviation as a decimal number, usually with several digits after the decimal point.

Tip: You can also type =STDEV.S(A1:A10) directly, without using the mouse, if you prefer typing.

Use STDEV.P when you have the full population

If you confirmed that your data covers every member of the group, type =STDEV.P( instead of STDEV.S. Follow the same steps: select the range, close the parenthesis, and press Enter.

The population formula gives a slightly smaller result than the sample formula for the same data. That is expected because it does not apply the n-1 correction.

For example, with the numbers 10, 12, 14, and 16, STDEV.P returns about 2.236, while STDEV.S returns about 2.582. Both are correct, but they answer different questions.

Check first: If you are unsure whether data is a sample or population, treat it as a sample. Most real-world datasets are samples, and the conservative correction is safer.

Verify the formula range includes only numbers

After Excel calculates the result, double-check the range shown in the formula bar. If you accidentally included a header row or a total row, the standard deviation will be wrong.

Look for common issues like text labels, percentage signs, or formulas that return text. Excel ignores text values in STDEV functions, but it counts blank cells as zero in some versions, which skews the result.

If you see a value that seems too large or too small, click the formula cell and re-select the range. Drag only over the numeric cells, not the surrounding labels.

Tip: Click the small green flag that appears beside the cell if Excel warns about an inconsistent formula, then choose Update Formula to include all adjacent numbers.

Format the result for easier reading

Standard deviation results often show many decimal places, such as 3.141592654. Right-click the result cell, choose Format Cells, then select Number. Set the decimal places to 2 and click OK.

You can also use the Decrease Decimal button in the Home tab, under the Number group. Click it once or twice until you see a clean, readable value.

Keep in mind that formatting only changes how the number looks, not the underlying value. Excel still uses the full precision for any calculations that reference this cell.

Tip: For most school or business reports, two decimal places is enough. For scientific work, keep four or more.

Interpret the result in plain language

A low standard deviation means your numbers cluster close to the average. A high standard deviation means your numbers spread out widely. Both values come with the same unit as your original data, so if your data is in pounds, the standard deviation is also in pounds.

For example, a standard deviation of 4 on test scores means most students scored within 4 points of the mean, assuming a roughly normal distribution. That helps you judge whether the average is a reliable summary.

Compare your result to the mean. If the standard deviation is more than half the mean, you likely have a very spread-out dataset, and you should mention that when reporting your findings.

Caution: Standard deviation assumes your data is roughly bell-shaped. If you have extreme outliers, consider reporting the median and interquartile range instead.

Quick Reference

Situation	Use this	Why
Survey of 200 customers out of 10,000	=STDEV.S(range)	You have a sample, not the full population
Test scores for every student in a class	=STDEV.P(range)	You have complete data for the group
Quarterly sales for your company only	=STDEV.P(range)	Your company data is the entire population of interest
Quality control sample from a production batch	=STDEV.S(range)	You tested only a subset of items

Common Problems When You Calculate Standard Deviation in Excel

You get the #DIV/0! error

This error appears when Excel cannot find at least two numbers in the range. Check that your range includes numeric cells. If you typed numbers as text, such as '10 instead of 10, Excel treats them as text and ignores them.

Fix this by selecting the range, then in the Home tab, change the number format from Text to General. Re-type any cells that show a green triangle warning.

You get the #VALUE! error

This happens when your range contains text that Excel cannot ignore, such as the word N/A or a cell with a formula that returned an error. Remove the text cells or move them outside the range.

If you have a column with a header row, make sure the header is not inside the range you selected. Start the range at the first data row, not the label row.

The result looks too small or too large

Check whether you accidentally selected only one cell instead of a full range. A single value always returns 0 for standard deviation because there is no spread.

Also, confirm that all cells contain numbers. A cell with a zero value counts as a data point and lowers the standard deviation. Decide whether zero is a legitimate measurement or a placeholder.

Excel shows a different value than your calculator

This usually means you used the sample formula on your calculator and the population formula in Excel, or vice versa. Compare the appropriate formula names: STDEV.S matches most scientific calculator sample modes, and STDEV.P matches population modes.

Also check whether your calculator rounds intermediate steps. Excel keeps full precision internally, so final differences of a few hundredths are normal.

You need to exclude blank cells from the range

Excel ignores truly blank cells in STDEV functions, but it counts cells with a space character as text. These text cells cause the #VALUE! error.

To clean up, select the column, use Find & Select in the Home tab, choose Go To Special, then select Blanks. Delete any cells that contain only spaces.

Advanced Tips for Calculating Standard Deviation in Excel

Calculate standard deviation for a filtered list

When you filter a table, the standard STDEV functions still use all hidden rows. To calculate the standard deviation only for visible rows, use the SUBTOTAL function with function number 7 for STDEV.S or 110 for STDEV.P.

Type =SUBTOTAL(7, A1:A10) to get the sample standard deviation of only the visible cells. When you change the filter, the result updates automatically. This is useful when you have a large dataset and you want to compare segments.

Reference a named range for clarity

Instead of typing A1:A10 every time, give the range a name. Select the cells, click the Name Box above the spreadsheet, type something like SalesData, and press Enter.

Now you can type =STDEV.S(SalesData) instead of referring to cell addresses. This reduces the risk of selecting the wrong range and makes your formulas easier for others to understand.

Combine standard deviation with AVERAGE in one formula

Use =AVERAGE(A1:A10)&" +/- "&TEXT(STDEV.S(A1:A10),"0.00") to display both the mean and the standard deviation in a single cell, separated by a plus-minus sign.

This creates a clear summary like 45.50 +/- 3.21, which is a standard way to report results in scientific and business writing. Adjust the text format code to match your decimal preference.

Use conditional formatting to flag high variability

Select the result cell, go to the Home tab, then Conditional Formatting, and choose New Rule. Set a rule that highlights the cell red if it exceeds a threshold you define, such as 10.

This helps you spot datasets with unusually high spread at a glance, especially if you calculate standard deviation for many rows of grouped data.

How To Calculate Standard Deviation In Excel FAQ

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

STDEV.S calculates the standard deviation for a sample of data, while STDEV.P calculates it for a full population. STDEV.S divides by n-1, which makes the estimate slightly larger and less biased. Use STDEV.S unless you have data for every member of the group you are studying.

Can I calculate standard deviation if my data has text or blank cells?

Excel ignores blank cells and text values in STDEV functions, as long as they are not causing errors. However, you should avoid text labels inside the numeric range because they can trigger the #VALUE! error. Blank cells are safe to leave in the range.

How many decimal places should I use?

For general reports, two decimal places is standard. For scientific or technical work, use the same number of decimal places as your original measurements. Do not round to fewer than one decimal place because standard deviation is a precise measure of spread.

What does the standard deviation number mean?

Standard deviation tells you how spread out your data is around the average. A value of zero means all numbers are identical. A larger value means greater variation. The number uses the same units as your original data, so it is directly interpretable.

Why does my standard deviation change when I sort the data?

Sorting should not change the standard deviation because the formula only uses the values, not their order. If the result changes, you likely selected a different range by accident, or your formula includes cells that moved during sorting. Double-check that the range still covers all the original data.

Can Excel calculate standard deviation for rows instead of columns?

Yes, Excel handles horizontal ranges just as easily as vertical ones. If your data sits in row 1, from A1 to J1, type =STDEV.S(A1:J1). The formula works the same way regardless of orientation.

Final Checklist for Calculating Standard Deviation in Excel

All data entered as numbers, with no text labels inside the range
You identified whether the data is a sample or a full population
You used STDEV.S for sample data or STDEV.P for population data
The selected range includes every data point and nothing else
The result cell shows no Excel error messages
You formatted the result to a readable number of decimal places
You interpreted the result as the average distance from the mean

Next step: Write your result alongside the mean in your report, for example: Mean 45.50, SD 3.21. That gives readers a complete picture of your data.