Excel: How to Find the Standard Deviation

Key Insights

• Excel offers two primary standard deviation functions: STDEV.S for sample data (most common) and STDEV.P for complete population data—using the wrong one will skew your results
• Standard deviation quantifies data spread around the mean, making it essential for identifying outliers, comparing dataset variability, and making statistical inferences
• Modern Excel functions (STDEV.S, STDEV.P) should replace legacy functions (STDEV, STDEVP) for better accuracy and consistency with statistical standards

Introduction to Standard Deviation in Excel

Standard deviation measures how spread out your data is from the average. A low standard deviation means values cluster tightly around the mean; a high standard deviation indicates data points are scattered across a wider range.

This metric appears everywhere in practical analysis: quality control teams use it to monitor manufacturing tolerances, financial analysts rely on it for volatility calculations, and researchers apply it to validate experimental results. Understanding variability often matters more than knowing the average alone.

Excel eliminates the tedious manual calculation—what would require squaring differences, summing them, dividing, and taking square roots becomes a single function call. But choosing the right function matters. Excel provides multiple standard deviation functions, and selecting incorrectly will produce subtly wrong results that can undermine your analysis.

Understanding the Two Types: Population vs. Sample

The distinction between population and sample standard deviation trips up many Excel users. The difference comes down to what your data represents.

Population standard deviation applies when your dataset includes every possible value you care about. If you’re analyzing test scores for an entire class of 30 students and you have all 30 scores, that’s your complete population. You’re not trying to infer anything about students outside this group.

Sample standard deviation applies when your data represents a subset of a larger group you want to understand. If you survey 500 customers to understand satisfaction across your 50,000-customer base, those 500 responses are a sample. You’re using this subset to estimate characteristics of the broader population.

The mathematical difference lies in the denominator. Population standard deviation divides by N (the total count). Sample standard deviation divides by N-1, applying what statisticians call Bessel’s correction. This adjustment compensates for the fact that samples tend to underestimate population variability.

In practice, sample standard deviation is far more common. Unless you’re certain you have complete data for the entire group you’re analyzing, default to the sample version.

Using STDEV.S for Sample Data

STDEV.S handles sample standard deviation and covers most real-world scenarios. The syntax is straightforward:

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

For a contiguous range of sales figures in column A:

=STDEV.S(A1:A50)

This calculates the standard deviation across 50 sales values, treating them as a sample from a larger potential dataset.

You can also reference multiple non-contiguous ranges:

=STDEV.S(A1:A50, C1:C50, E1:E50)

Or mix ranges with individual values:

=STDEV.S(A1:A50, 100, 150, 200)

Here’s a practical example. Suppose you’re tracking daily website response times over a month:

| A              | B                    |
|----------------|----------------------|
| Day            | Response Time (ms)   |
| 1              | 245                  |
| 2              | 312                  |
| 3              | 198                  |
| ...            | ...                  |
| 30             | 267                  |

=STDEV.S(B2:B31)

If this returns 45.3, you know response times typically vary about 45 milliseconds from the average. Combined with the mean (say, 250ms), you can establish that “normal” performance falls roughly between 205ms and 295ms (one standard deviation in either direction).

For context, pair standard deviation with the mean:

=AVERAGE(B2:B31)    ' Returns 250
=STDEV.S(B2:B31)    ' Returns 45.3

Using STDEV.P for Population Data

When your data represents a complete population, STDEV.P provides the correct calculation:

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

Consider a teacher grading a final exam for their entire class of 25 students:

| A              | B           |
|----------------|-------------|
| Student        | Score       |
| Alice          | 87          |
| Bob            | 92          |
| Carol          | 78          |
| ...            | ...         |
| Yolanda        | 85          |

=STDEV.P(B2:B26)

Since these 25 students constitute the entire population of interest (this specific class), STDEV.P is appropriate. The teacher isn’t trying to infer anything about students in other classes or future years.

Other population scenarios include:

• Complete inventory counts for a warehouse
• All transactions for a specific account in a closed period
• Every employee’s salary at a small company
• All products in a specific category

' Complete inventory of product weights
=STDEV.P(C2:C100)

' All employee salaries (company of 45 people)
=STDEV.P(Salaries!B2:B46)

The numerical difference between STDEV.S and STDEV.P shrinks as sample size grows. With 1,000 data points, the results are nearly identical. With 10 data points, the difference becomes meaningful. When in doubt with small datasets, consider which function aligns with your analytical intent.

Handling Edge Cases and Errors

Excel’s standard deviation functions handle imperfect data in specific ways you should understand.

Blank cells are ignored entirely. They don’t count as zeros or affect the calculation:

| A    |
|------|
| 10   |
|      |  <- Ignored
| 20   |
| 30   |

=STDEV.S(A1:A4)  ' Calculates based on 10, 20, 30 only

Text values are also ignored in range references:

| A       |
|---------|
| 10      |
| N/A     |  <- Ignored
| 20      |
| Error   |  <- Ignored
| 30      |

=STDEV.S(A1:A5)  ' Calculates based on 10, 20, 30

Zeros are included as valid data points. This matters when zeros represent missing data rather than actual zero values:

| A    |
|------|
| 10   |
| 0    |  <- Included in calculation
| 20   |
| 30   |

=STDEV.S(A1:A4)  ' Includes the zero, affecting results

To exclude zeros or apply other conditions, combine STDEV with IF as an array formula. In modern Excel (365/2021), use:

=STDEV.S(IF(A1:A100>0, A1:A100))

In older Excel versions, enter this as an array formula with Ctrl+Shift+Enter:

{=STDEV.S(IF(A1:A100>0, A1:A100))}

For more complex filtering, consider FILTER:

=STDEV.S(FILTER(A1:A100, A1:A100>0))

Or filter based on another column:

' Standard deviation of sales where region is "East"
=STDEV.S(FILTER(B2:B100, A2:A100="East"))

Error values (#N/A, #VALUE!, #DIV/0!) cause the entire STDEV function to return an error. Clean your data first or use IFERROR:

=IFERROR(STDEV.S(A1:A100), "Data contains errors")

Visualizing Standard Deviation

Numbers alone don’t always communicate variability effectively. Adding error bars to charts shows standard deviation visually, making it immediately apparent how much data spreads around the mean.

To add standard deviation error bars to a chart:

1. Create a bar or column chart from your data
2. Select the data series in the chart
3. Go to Chart Design → Add Chart Element → Error Bars → More Error Bars Options
4. Select “Custom” and specify your standard deviation values

For a chart comparing average sales across regions with variability:

| A        | B              | C                |
|----------|----------------|------------------|
| Region   | Avg Sales      | Std Dev          |
| North    | 45000          | =STDEV.S(...)    |
| South    | 52000          | =STDEV.S(...)    |
| East     | 48000          | =STDEV.S(...)    |
| West     | 51000          | =STDEV.S(...)    |

Create a column chart from columns A and B, then add custom error bars using the values in column C. The resulting visualization shows not just which region performs best on average, but which has the most consistent (or variable) performance.

Error bars extending ±1 standard deviation capture roughly 68% of data points in a normal distribution. For ±2 standard deviations (about 95% of data), multiply your STDEV result by 2 when setting up error bars.

Quick Reference Summary

Best practices:

• Default to STDEV.S unless you’re certain you have complete population data
• Always pair standard deviation with the mean for context
• Use STDEV.S and STDEV.P instead of legacy STDEV and STDEVP functions
• Remember that blank cells and text are ignored, but zeros are included
• Validate your data for errors before calculating—one #N/A breaks the entire formula
• Consider coefficient of variation (standard deviation divided by mean) when comparing variability across datasets with different scales