How to Calculate Standard Deviation in Excel

Standard deviation is one of the most useful statistical measures you'll encounter — it tells you how spread out your data is around the average. A small standard deviation means your values cluster tightly together; a large one means they're scattered. Excel makes this calculation straightforward, but there are a few function choices that trip people up if you don't understand what they're actually measuring.

What Standard Deviation Actually Measures

Before touching a formula, it helps to understand what you're asking Excel to calculate.

Standard deviation measures the average distance each data point sits from the mean. If you're tracking daily sales figures and the mean is $500, a standard deviation of $20 means most days land close to that number. A standard deviation of $200 means your results vary wildly.

This matters because Excel offers multiple standard deviation functions — and picking the wrong one produces a number that's technically calculated but statistically misleading for your situation.

The Two Core Functions: STDEV.S vs STDEV.P

Excel's most important distinction is between sample standard deviation and population standard deviation.

Function	Full Name	When to Use
`STDEV.S`	Sample Standard Deviation	You're working with a subset of a larger dataset
`STDEV.P`	Population Standard Deviation	You have every data point — the complete dataset

STDEV.S uses what statisticians call Bessel's correction — it divides by (n−1) instead of n, which compensates for the fact that a sample tends to underestimate the true spread of the full population. This is the right choice for most practical analysis: survey responses, test scores from one class, a month of sales data used to represent a year.

STDEV.P assumes your data is the entire population, with nothing left out. If you're analyzing every product your store has ever sold — not a sample — STDEV.P is appropriate.

📊 A common mistake: using STDEV.P on a sample produces a slightly lower number that appears more precise but actually understates variability.

How to Enter the Formula

The syntax is identical for both functions:

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

In practice, you'll almost always reference a cell range rather than individual values.

Step-by-step:

Click on an empty cell where you want the result to appear
Type =STDEV.S( (or =STDEV.P( depending on your data type)
Select your data range — for example, B2:B50
Close the parenthesis and press Enter

Your formula will look like this: =STDEV.S(B2:B50)

Excel instantly returns the standard deviation for that range.

Older Function Names You Might Encounter

If you open spreadsheets built in older versions of Excel, you'll likely see STDEV or STDEVP without the dot notation. These still work in modern Excel for backward compatibility, but Microsoft recommends the updated versions:

STDEV → replaced by STDEV.S
STDEVP → replaced by STDEV.P

There are also two additional functions worth knowing:

STDEVA — Same as STDEV.S but includes text and logical values (TRUE/FALSE) in the calculation. Text is counted as 0.
STDEVPA — Same as STDEV.P but also includes text and logical values.

Most users won't need STDEVA or STDEVPA, but they matter if your dataset contains checkboxes, TRUE/FALSE flags, or text entries that represent a value of zero.

Handling Common Issues

Empty cells: Excel automatically ignores blank cells in your range. This is usually the behavior you want — blank isn't the same as zero.

Zero values: Unlike blank cells, zeros are included in the calculation. If a zero represents "no data" rather than an actual measurement, you'll need to clean your data before running the formula.

Non-numeric data mixed in: If text is scattered through your range and you're using STDEV.S, Excel skips those cells. If you want them counted as zero, switch to STDEVA.

Small datasets: Standard deviation becomes less reliable with very few data points. A sample of three or four values will technically produce a result, but the number carries significant uncertainty. The formula works — the interpretation requires caution.

Visualizing the Result 📈

Standard deviation becomes more meaningful when paired with the mean (average). Calculate both side-by-side:

=AVERAGE(B2:B50) ← mean =STDEV.S(B2:B50) ← standard deviation

From there, you can determine a typical range: mean ± one standard deviation covers roughly 68% of values in a normally distributed dataset. Two standard deviations covers about 95%.

Excel also lets you add standard deviation bars to charts. Select your chart, go to Chart Elements, enable Error Bars, and choose Standard Deviation — this gives you an immediate visual sense of spread without building a separate table.

What Affects Which Function Is Right for You

The formula mechanics are simple. The judgment call is trickier, and it depends on factors specific to your data:

How the data was collected — a random sample behaves differently from a complete census
What you're trying to communicate — internal analysis versus published reporting have different accuracy expectations
Your dataset size — the difference between STDEV.S and STDEV.P shrinks as n grows larger, but matters significantly in small datasets
Whether zeros mean something — a zero temperature reading and a zero representing "no response" are statistically different situations

Understanding the mechanics of each function gets you most of the way there. Knowing which one applies depends on what your data actually represents and what question you're trying to answer with it.