How to Calculate Standard Deviation in Google Sheets

Key Insights

• Google Sheets offers five standard deviation functions—use STDEV.S for sample data (most common) and STDEV.P when you have the complete population
• The difference between sample and population standard deviation matters: using the wrong one can skew your statistical analysis by 5-15% on smaller datasets
• Functions like STDEVA and STDEVPA handle text values by treating them as zero, which is rarely what you want—clean your data first instead

Introduction to Standard Deviation

Standard deviation measures how spread out your data is from the average. A low standard deviation means your values cluster tightly around the mean; a high one means they’re scattered widely. If you’re analyzing sales figures, website traffic, or survey responses in Google Sheets, standard deviation tells you whether your data is consistent or volatile.

This matters more than most people realize. An average monthly revenue of $50,000 sounds great until you learn the standard deviation is $40,000—meaning some months you’re barely breaking even while others are exceptional. That volatility changes how you plan, budget, and make decisions.

Google Sheets gives you multiple functions for calculating standard deviation. Picking the right one depends on whether your data represents a sample or an entire population, and whether your dataset contains text or errors. Let’s break down each scenario.

Understanding Google Sheets’ STDEV Functions

Google Sheets provides five standard deviation functions:

The critical distinction is sample versus population. Use sample standard deviation (STDEV.S) when your data is a subset of a larger group—like surveying 500 customers out of 10,000, or analyzing 30 days of data to understand yearly patterns. Use population standard deviation (STDEV.P) when you have every single data point that exists—like calculating the deviation of all 12 monthly sales figures for the year, with no extrapolation intended.

Here’s how the results differ on the same dataset:

| A          | B                |
|------------|------------------|
| Test Scores|                  |
| 78         |                  |
| 82         |                  |
| 91         |                  |
| 67         |                  |
| 85         |                  |
| 73         |                  |
| 88         |                  |
| 79         |                  |

Sample SD:     =STDEV.S(A2:A9)   → 7.65
Population SD: =STDEV.P(A2:A9)   → 7.16

The sample standard deviation is always larger because it uses n-1 in the denominator (Bessel’s correction) to account for the fact that a sample tends to underestimate population variability. With 8 data points, the difference is about 7%. With 100 data points, it shrinks to around 0.5%. With 1000 points, it’s negligible.

Rule of thumb: When in doubt, use STDEV.S. Most real-world spreadsheet analysis involves samples, not complete populations.

Basic Standard Deviation Calculation

Let’s walk through a practical example. You’re tracking daily sales for your e-commerce store and want to understand the variability in revenue.

First, set up your data:

|   A    |    B     |
|--------|----------|
| Date   | Revenue  |
| Jan 1  | 1,250    |
| Jan 2  | 980      |
| Jan 3  | 1,450    |
| Jan 4  | 1,120    |
| Jan 5  | 2,100    |
| Jan 6  | 1,890    |
| Jan 7  | 1,340    |
| ...    | ...      |
| Jan 31 | 1,560    |

To calculate the standard deviation of your January revenue:

=STDEV.S(B2:B32)

If this returns 385.42, it means your daily revenue typically varies by about $385 from the average. Combined with the mean, this gives you a complete picture:

Average:  =AVERAGE(B2:B32)  → $1,456
Std Dev:  =STDEV.S(B2:B32)  → $385.42

You can now say that on a typical day, revenue falls between roughly $1,071 and $1,841 (one standard deviation from the mean). About 68% of your days will fall in this range, assuming roughly normal distribution.

Calculating Population Standard Deviation

Population standard deviation applies when you’re analyzing a complete, finite dataset with no intention of generalizing beyond it.

Example: You run a company with exactly 25 employees and want to analyze salary distribution. You have everyone’s salary—this is the entire population, not a sample.

|    A     |    B    |
|----------|---------|
| Employee | Salary  |
| Alice    | 65,000  |
| Bob      | 72,000  |
| Carol    | 58,000  |
| David    | 91,000  |
| ...      | ...     |
| Yolanda  | 67,500  |

=STDEV.P(B2:B26)  → $12,340

Another common scenario: analyzing all responses from a closed survey. If you surveyed exactly 150 conference attendees and got 150 responses, that’s your population:

=STDEV.P(C2:C151)

The key question to ask yourself: “Am I trying to infer something about a larger group, or am I only describing this exact dataset?” If it’s the latter, use STDEV.P.

Handling Text and Errors in Data

Real-world data is messy. You’ll encounter blank cells, text entries like “N/A” or “pending,” and error values. Google Sheets’ standard functions handle these differently.

Default behavior of STDEV.S and STDEV.P:

• Blank cells: Ignored
• Text values: Ignored
• Boolean TRUE/FALSE: Ignored
• Error values (#N/A, #DIV/0!): Returns an error

The STDEVA and STDEVPA functions take a different approach:

|   A    |    B     |
|--------|----------|
| Day    | Reading  |
| Mon    | 45       |
| Tue    | N/A      |
| Wed    | 52       |
| Thu    | 48       |
| Fri    | absent   |
| Sat    | 51       |
| Sun    | 47       |

=STDEV.S(B2:B8)   → 2.94 (ignores "N/A" and "absent")
=STDEVA(B2:B8)    → 21.07 (treats "N/A" and "absent" as 0)

That’s a massive difference. STDEVA converted your text entries to zeros, which dramatically increased the spread. This is almost never what you want.

My recommendation: Don’t rely on STDEVA or STDEVPA. Instead, clean your data first:

=STDEV.S(FILTER(B2:B8, ISNUMBER(B2:B8)))

This filters to only numeric values before calculating, giving you explicit control over what’s included.

For handling errors, wrap your calculation:

=IFERROR(STDEV.S(B2:B50), "Check data for errors")

Or use FILTER to exclude error cells:

=STDEV.S(FILTER(B2:B50, NOT(ISERROR(B2:B50))))

Practical Applications and Visualization

Standard deviation becomes powerful when combined with other calculations.

Coefficient of Variation (CV): This expresses standard deviation as a percentage of the mean, letting you compare variability across datasets with different scales.

=STDEV.S(D2:D50)/AVERAGE(D2:D50)

If Product A has a mean price of $10 with SD of $2 (CV = 20%), and Product B has a mean of $100 with SD of $15 (CV = 15%), Product B’s pricing is actually more consistent despite the higher absolute deviation.

Z-scores: Determine how many standard deviations a value is from the mean:

=(A2-AVERAGE($A$2:$A$50))/STDEV.S($A$2:$A$50)

A Z-score of 2.5 means that value is 2.5 standard deviations above average—likely an outlier worth investigating.

Error bars in charts: When creating a chart in Google Sheets:

1. Select your chart and click the three dots → Edit chart
2. Go to Customize → Series
3. Check “Error bars”
4. Set Type to “Constant” and enter your calculated standard deviation

This visually communicates data variability, which is essential for any serious data presentation.

Troubleshooting Common Issues

"#DIV/0!" error: You have fewer than 2 numeric data points. STDEV.S requires at least 2 values (it divides by n-1); STDEV.P requires at least 1.

Unexpectedly high values: Check for outliers or data entry errors. One value of 10,000 in a dataset averaging 100 will explode your standard deviation. Use conditional formatting to highlight outliers:

=A2 > AVERAGE($A$2:$A$50) + 2*STDEV.S($A$2:$A$50)

Results don’t match other tools: Confirm you’re using the same function type. Excel’s STDEV function matches Google Sheets’ STDEV.S. Some statistical software defaults to population standard deviation.

Formatted numbers causing issues: Currency symbols, percentages, and thousand separators shouldn’t cause problems in Google Sheets, but imported data sometimes brings in text that looks like numbers. Use =ISNUMBER(A2) to verify cells contain actual numbers.

Standard deviation is fundamental to understanding your data’s behavior. Master these functions, and you’ll make better decisions backed by statistical confidence rather than gut feeling.