How to Work Out Standard Deviation on Excel: A Step-by-Step Tutorial (2026)

Understanding Standard Deviation: The Quick Version

Standard deviation is a statistical calculation used to quantify the amount of variation or dispersion in a set of values, indicating how far individual data points are from the mean value within a specific dataset. It is a foundational concept in statistics and one of the essential skills for anyone analyzing data in Excel, particularly when assessing the significance of results.

The key distinction in Excel is whether your data represents a sample taken from a larger population, or whether it covers the entire population itself.

Sample standard deviation (STDEV.S): Use this when your data represents a subset of a larger group. For example, if you surveyed 50 employees from a company of 500 to assess sales performance, your 50 responses are a sample. STDEV.S divides by n-1 (one less than the total number of data points) to account for sample variability, which produces a slightly larger result that corrects for the fact that a sample tends to underestimate the true spread of the entire population.

Population standard deviation (STDEV.P): Use this when your data represents the entire population with no subset involved. If you have the test scores of every student in a class, that is the entire population. STDEV.P divides by n (the total number of data points), producing a smaller result.

STDEV.S is commonly used for data analysis since it is rare to have complete data for an entire population. When in doubt, use STDEV.S.

Method 1: STDEV.S for Sample Standard Deviation

The STDEV.S function calculates the standard deviation based on a sample of a larger population. This is the function most people need most of the time.

Syntax: =STDEV.S(number1, [number2], ...)

The first number argument is required. It corresponds to a sample of a population and can be a single value, a cell reference, or a range. Additional arguments are optional and are arguments separated by commas if you need to include multiple non-adjacent ranges.

To calculate sample standard deviation in the sales data file:

1. Click an empty cell where you want the result to appear, for example cell E4.
2. Type =STDEV.S(B2:B21) where B2:B21 is the range containing your sales data points.
3. Press Enter.

The result appears in E4. In the screenshot below, the formula bar shows =STDEV.S(B2:B21) and the cell displays the calculated standard deviation value for the 20 sales figures in column B.

Add image alt text

Using a single array: You can also pass data as a single array directly in the formula. For example, =STDEV.S({12,45,67,34,89}) calculates the standard deviation of those five values without needing them in cells. This is useful for quick one-off calculations.

Multiple ranges: To calculate standard deviation across two separate data ranges, use comma-separated arguments: =STDEV.S(B2:B11, D2:D11). Excel treats all values across both ranges as a single dataset for the calculation.

Method 2: STDEV.P for Population Standard Deviation

The STDEV.P function calculates the population standard deviation, assuming that the arguments include the entire population of data. Use this when your dataset is complete and not a sample from a larger group.

Syntax: =STDEV.P(number1, [number2], ...)

To calculate population standard deviation:

1. Click an empty cell, for example E5.
2. Type =STDEV.P(B2:B21).
3. Press Enter.

STDEV.P will always return a slightly smaller result than STDEV.S on the same data, because it divides by n rather than n-1. In the screenshot below, D2 shows the STDEV.S result and E5 shows the STDEV.P result side by side, with the population standard deviation visibly smaller.

Add image alt text

When data represents the entire population: Excel's STDEV.P function calculates the standard deviation for an entire population, assuming that the provided arguments include the entire population. If you later add more records and the data no longer represents all the data, switch to STDEV.S.

Method 3: STDEVA and STDEVPA for Logical Values and Text

The standard STDEV.S and STDEV.P functions ignore text values and logical values in the selected range. If a dataset includes text representations of numbers or logical values like TRUE and FALSE, the STDEVA function is used for samples and the STDEVPA function is used for populations.

STDEVA syntax: =STDEVA(value1, [value2], ...)

STDEVA treats logical values as numbers: TRUE counts as 1 and FALSE counts as 0. Text representations of numbers such as "5" are also included in the calculation. Empty cells are still ignored.

To use STDEVA:

1. Click an empty cell, for example D4.
2. Type =STDEVA(B2:B21).
3. Press Enter.

The stdeva function produces a different result from STDEV.S only when your range contains logical values or text that can be converted to a number. In most clean numerical datasets, the results will be identical.

Add image alt text

STDEVPA for populations with logical values: Use =STDEVPA(value1, [value2], ...) when your data represents the entire population and includes logical values or text representations. It is the population equivalent of STDEVA, accounting for logical values the same way.

Method 4: Using a Named Range for Cleaner Formulas

For frequently referenced data ranges, defining a named range makes your standard deviation formulas easier to read and maintain, especially across a large excel spreadsheet with many analyses.

1. Select your data range, for example B2:B21.

2. Click the Name Box at the top-left of the screen (the box showing the cell address).

3. Type a name such as SalesData and press Enter.

4. Now in any empty cell, type =STDEV.S(SalesData) and press Enter. IronXL also supports named ranges in Excel files when building programmatic solutions.

The formula produces the same result as =STDEV.S(B2:B21) but is immediately readable. Named ranges also update automatically if you defined them to cover a dynamic range, which is particularly valuable when new data points are added regularly.

Add image alt text

Naming multiple data ranges: If you have data for different teams or time periods in separate columns, define separate named ranges such as TeamA and TeamB and calculate each standard deviation independently: =STDEV.S(TeamA) and =STDEV.S(TeamB). This makes it straightforward to compare variability across groups.

Method 5: Calculate Mean and Standard Deviation Together

In most analyses you need both the average value and the standard deviation together to describe a dataset fully. Excel makes it easy to calculate both in adjacent cells.

1. In cell C2, type the label Mean.
2. In cell D2, type =AVERAGE(B2:B21) to calculate the average value (the mean) of your sales data.
3. In cell C3, type the label Std Dev.
4. In cell D3, type =STDEV.S(B2:B21).

With both the mean and standard deviation calculated, you can interpret the spread of your data. If the mean value is 5000 and the standard deviation is 200, most of your values fall within one standard deviation of the mean, between 4800 and 5200. This is essential context for understanding variability in sales performance, quality control measurements, or any dataset where understanding variation matters.

Add image alt text

Coefficient of variation: To quantify how large the standard deviation is relative to the mean, divide one by the other: =D3/D2. This gives you the coefficient of variation, another measure of variability that is especially useful when comparing data ranges with different average values. Standard deviation also plays a central role in hypothesis testing, where it helps determine whether observed differences between groups are statistically significant or simply the result of random variation in the sample data.

Method 6: Add Standard Deviation Error Bars to a Chart

Once you have calculated standard deviation, you can display it visually in a chart using error bars. Error bars draw vertical lines above and below each data point to show the range of one standard deviation, making variability immediately visible.

1. Create a chart from your data. Select the data range B2:B21, go to Insert > Charts, and choose a column or line chart.
2. Click on the chart to select it.

3. Click the + button at the top-right of the chart to open the Chart Elements menu.

4. Tick the Error Bars checkbox.

5. Click the arrow next to Error Bars and select More Options.

6. In the Format Error Bars panel, select Custom under Error Amount.

7. Click Specify Value, enter your standard deviation cell reference (for example D3) in both the Positive Error Value and Negative Error Value fields.

The chart now displays error bars extending one standard deviation above and below each point. In the screenshot below, the sales performance line chart shows error bars that visually communicate the variability of each month's data.

Add image alt text

Standard Deviation Formulas: Complete Reference

| Function | Purpose | Divides by | Includes text/logical values | | --- | --- | --- | --- | | STDEV.S | Sample standard deviation | n-1 | No | | STDEV.P | Population standard deviation | n | No | | STDEVA | Sample with text and logical values | n-1 | Yes | | STDEVPA | Population with text and logical values | n | No (ignored) |

The syntax for applying standard deviation functions in Excel follows the same pattern: =FUNCTION(range) or =FUNCTION(number1, [number2], ...). Every function accepts up to 255 arguments separated by commas, and each argument can be a single value, a cell reference, or a data range.

How Standard Deviation Is Calculated: The Short Explanation

Understanding the calculation behind the formula helps you interpret results correctly. Standard deviation is the square root of the variance. Variance is the average of the squared deviations of each data point from the mean.

The steps Excel performs internally:

1. Calculate the average value (mean) of all data points.
2. Subtract the mean from each individual data point to get each deviation.
3. Square each deviation (squared deviations are always positive, removing the sign).
4. Sum all the squared deviations.
5. Divide by n-1 (for a sample) or n (for the entire population) to get the variance.
6. Take the square root of the variance.

The result is in the same original units as your original data, which makes it easy to interpret alongside the mean. If your sales figures are in dollars, the standard deviation is also in dollars.

Common Errors and Troubleshooting

#DIV/0! error

If a #DIV/0! error occurs when calculating standard deviation, it indicates that there must be more than one data point in the selected range. A single value has no deviation from itself and produces this error. Ensure your range contains at least two numerical values.

#VALUE! error

This appears when the range contains error values that Excel cannot process in the calculation. Check the selected range for cells displaying #N/A, #REF!, or other error values and correct them before running the standard deviation formula.

Result seems too high or too low

Data structure should be verified to ensure that no text or empty cells are included in the selected range, as these can affect calculations. STDEV.S and STDEV.P skip text and empty cells silently, which can produce misleading results if those cells were supposed to contain numerical values. When using Excel to calculate standard deviation, ensure that your data is clean and free from errors, as incorrect data can lead to inaccurate results.

STDEV.S and STDEV.P return the same value

This happens when the dataset has only two data points. With n=2, both formulas produce the same result. Add more data points to see the divergence between them.

Choosing the wrong function

Using STDEV.P on a sample produces an underestimate of the true population standard deviation. Using STDEV.S on a complete population produces a slight overestimate. Confirm whether your data represents a sample or an entire population before choosing the function.

Quick Reference: Which Function to Use

| Situation | Function | | --- | --- | | Data is a sample from a larger population | STDEV.S | | Data covers the entire population | STDEV.P | | Sample data includes TRUE/FALSE or text numbers | STDEVA | | Population data includes TRUE/FALSE or text numbers | STDEVPA | | Need both average and variability | AVERAGE + STDEV.S | | Need to visualize variability on a chart | Error bars with STDEV.S result |

For Developers: Calculate Standard Deviation in C# with IronXL

If your application generates reports or analyses from Excel files automatically, you'll often need to calculate standard deviation as part of a data pipeline. IronXL supports over 165 Excel formulas including STDEV.S and STDEV.P, and you can write them directly into cells using the Formula property. No coding required beyond setting the formula string. For the full math functions reference, see the IronXL math functions guide.

Here's how to write STDEV.S and STDEV.P formulas into an Excel file using C#:

using IronXL;
WorkBook workBook = WorkBook.Load("sales_data.xlsx");
WorkSheet sheet = workBook.DefaultWorkSheet;
// Write sample standard deviation formula into cell D2
sheet["D2"].Formula = "=STDEV.S(B2:B21)";
// Write population standard deviation formula into cell D3
sheet["D3"].Formula = "=STDEV.P(B2:B21)";
// Write STDEVA formula for data with logical values into cell D4
sheet["D4"].Formula = "=STDEVA(B2:B21)";
// Write the mean formula into D5 for mean and standard deviation together
sheet["D5"].Formula = "=AVERAGE(B2:B21)";
// Force recalculation of all formulas in the workbook
workBook.EvaluateAll();
// Read the calculated standard deviation value back into C#
decimal stdDev = (decimal)sheet["D2"].DoubleValue;
Console.WriteLine($"Sample Standard Deviation: {stdDev:F2}");
workBook.SaveAs("sales_data_with_stats.xlsx");

using IronXL;
WorkBook workBook = WorkBook.Load("sales_data.xlsx");
WorkSheet sheet = workBook.DefaultWorkSheet;
// Write sample standard deviation formula into cell D2
sheet["D2"].Formula = "=STDEV.S(B2:B21)";
// Write population standard deviation formula into cell D3
sheet["D3"].Formula = "=STDEV.P(B2:B21)";
// Write STDEVA formula for data with logical values into cell D4
sheet["D4"].Formula = "=STDEVA(B2:B21)";
// Write the mean formula into D5 for mean and standard deviation together
sheet["D5"].Formula = "=AVERAGE(B2:B21)";
// Force recalculation of all formulas in the workbook
workBook.EvaluateAll();
// Read the calculated standard deviation value back into C#
decimal stdDev = (decimal)sheet["D2"].DoubleValue;
Console.WriteLine($"Sample Standard Deviation: {stdDev:F2}");
workBook.SaveAs("sales_data_with_stats.xlsx");

Imports IronXL

Dim workBook As WorkBook = WorkBook.Load("sales_data.xlsx")
Dim sheet As WorkSheet = workBook.DefaultWorkSheet
' Write sample standard deviation formula into cell D2
sheet("D2").Formula = "=STDEV.S(B2:B21)"
' Write population standard deviation formula into cell D3
sheet("D3").Formula = "=STDEV.P(B2:B21)"
' Write STDEVA formula for data with logical values into cell D4
sheet("D4").Formula = "=STDEVA(B2:B21)"
' Write the mean formula into D5 for mean and standard deviation together
sheet("D5").Formula = "=AVERAGE(B2:B21)"
' Force recalculation of all formulas in the workbook
workBook.EvaluateAll()
' Read the calculated standard deviation value back into VB.NET
Dim stdDev As Decimal = CDec(sheet("D2").DoubleValue)
Console.WriteLine($"Sample Standard Deviation: {stdDev:F2}")
workBook.SaveAs("sales_data_with_stats.xlsx")

IronXL's formula engine recalculates results when you call EvaluateAll(), so the values in the saved file reflect the correct standard deviation without needing Excel installed. You can also read back the calculated result directly into a C# variable for further processing, as shown above.

For reading and writing data to and from Excel files in C#, see the IronXL Excel formulas code example and the reading Excel data guide.

Getting started: Install via NuGet with Install-Package IronXL.Excel and start your free trial to test in your environment. IronXL runs on .NET 6+ and is compatible with Windows, Linux, macOS, Docker, Azure, and AWS.

Wrapping Up

Calculating standard deviation in Excel is a straightforward process once you understand the two main functions. With this knowledge of when each function is relevant, STDEV.S handles sample data and is the right choice for most real-world analyses. STDEV.P is for complete population datasets where every member of the group is represented.

Standard deviation is one of the essential skills in statistical analysis because it gives you valuable insights into data variability that the average alone cannot provide. A dataset with a mean of 100 and a standard deviation of 5 tells a very different story from one with a mean of 100 and a standard deviation of 50. Pair standard deviation with the mean, add error bars to your charts for visual communication, and you have a complete picture of both the center and the spread of your data. For developers processing Excel files in code, IronXL makes it straightforward to write standard deviation formulas programmatically so every generated report includes the statistical analyses your users need.