STDEV Function in Google Sheets: Complete Guide

Key Insights

• STDEV calculates sample standard deviation (n-1 denominator) while STDEVP calculates population standard deviation (n denominator)—use STDEV when your data represents a subset of a larger group, STDEVP when you have complete data
• The function ignores text and empty cells but treats boolean values differently depending on whether they’re in a range or passed directly as arguments
• Combine STDEV with FILTER for conditional standard deviation calculations since Google Sheets lacks a native STDEV.IF function

Introduction to STDEV and Standard Deviation

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 values are scattered across a wider range.

You’ll reach for STDEV when you need to answer questions like: How consistent are our monthly sales figures? How much variation exists in product quality measurements? Which supplier delivers more predictable lead times?

Google Sheets provides several standard deviation functions:

• STDEV / STDEV.S — Sample standard deviation
• STDEVP / STDEV.P — Population standard deviation
• STDEVA — Sample standard deviation including text and booleans
• STDEVPA — Population standard deviation including text and booleans

The STDEV and STDEV.S functions are identical, as are STDEVP and STDEV.P. The .S and .P suffixes were added for clarity in newer spreadsheet versions.

STDEV vs STDEVP: Sample vs Population

The distinction between sample and population standard deviation trips up many spreadsheet users. The math differs in one key way: STDEV divides by (n-1) while STDEVP divides by n, where n is the count of values.

Use STDEV (sample) when:

• Your data represents a subset of a larger group
• You’re analyzing survey responses from selected participants
• You’re measuring a sample of products from a production run

Use STDEVP (population) when:

• You have data for the entire group you care about
• You’re analyzing all employees in your company
• You’re measuring every transaction in a specific time period

Here’s a comparison showing how the results differ:

| A              | B                    |
|----------------|----------------------|
| Test Scores    | 85                   |
|                | 90                   |
|                | 78                   |
|                | 92                   |
|                | 88                   |
|----------------|----------------------|
| Sample STDEV   | =STDEV(B2:B6)        |  → 5.36
| Population     | =STDEVP(B2:B6)       |  → 4.79

The sample standard deviation is always larger because dividing by (n-1) instead of n produces a slightly higher result. This adjustment corrects for the bias that occurs when estimating population variability from a sample.

With small datasets, the difference is noticeable. With thousands of data points, the results converge. When in doubt, use STDEV—most business analysis involves samples rather than complete populations.

Basic STDEV Syntax and Usage

The STDEV function accepts multiple input formats:

=STDEV(value1, [value2, ...])

You can pass:

• A single range: =STDEV(A1:A100)
• Multiple ranges: =STDEV(A1:A50, C1:C50)
• Individual values: =STDEV(10, 20, 30, 40)
• Mixed inputs: =STDEV(A1:A10, 25, B5)

The function handles different data types in specific ways:

| A        | B                        |
|----------|--------------------------|
| 10       |                          |
| 20       |                          |
| thirty   | (text - ignored)         |
|          | (blank - ignored)        |
| 40       |                          |
| 50       |                          |
|----------|--------------------------|
| Result   | =STDEV(A1:A6)  → 16.33   |

Text strings and empty cells are silently ignored. The calculation above uses only the four numeric values: 10, 20, 40, and 50.

Boolean values behave inconsistently. When passed in a range, TRUE and FALSE are ignored. When passed directly as arguments, TRUE equals 1 and FALSE equals 0:

=STDEV(A1:A5)        // TRUE/FALSE in range are ignored
=STDEV(1, 2, TRUE)   // TRUE counts as 1

This quirk catches people off guard. If you need booleans included from ranges, use STDEVA instead.

Practical Examples and Use Cases

Analyzing Sales Performance Variability

You want to identify which sales representatives have the most consistent performance:

|   | A          | B      | C      | D      | E      | F              |
|---|------------|--------|--------|--------|--------|----------------|
| 1 | Rep        | Jan    | Feb    | Mar    | Apr    | Consistency    |
| 2 | Alice      | 45000  | 52000  | 48000  | 51000  | =STDEV(B2:E2)  |
| 3 | Bob        | 30000  | 65000  | 25000  | 70000  | =STDEV(B3:E3)  |
| 4 | Carol      | 47000  | 49000  | 46000  | 48000  | =STDEV(B4:E4)  |

Results:

• Alice: 2,944 (moderate consistency)
• Bob: 22,174 (highly variable)
• Carol: 1,291 (very consistent)

Carol’s low standard deviation indicates predictable performance, while Bob’s high value suggests feast-or-famine months.

Measuring Quality Control Metrics

Manufacturing environments track process consistency using standard deviation:

|   | A              | B        |
|---|----------------|----------|
| 1 | Widget Weight  | 10.02    |
| 2 |                | 9.98     |
| 3 |                | 10.05    |
| 4 |                | 9.97     |
| 5 |                | 10.01    |
| 6 |                | 10.03    |
| 7 |----------------|----------|
| 8 | Mean           | =AVERAGE(B1:B6)  → 10.01  |
| 9 | Std Dev        | =STDEV(B1:B6)    → 0.030  |
| 10| Upper Limit    | =B8+3*B9         → 10.10  |
| 11| Lower Limit    | =B8-3*B9         → 9.92   |

This creates control limits at three standard deviations from the mean—a common quality control threshold.

Comparing Investment Volatility

Standard deviation quantifies investment risk:

|   | A          | B          | C          |
|---|------------|------------|------------|
| 1 | Month      | Fund A     | Fund B     |
| 2 | Jan        | 2.1%       | 0.8%       |
| 3 | Feb        | -1.5%      | 0.5%       |
| 4 | Mar        | 3.2%       | 0.9%       |
| 5 | Apr        | -0.8%      | 0.6%       |
| 6 | May        | 2.8%       | 0.7%       |
| 7 |------------|------------|------------|
| 8 | Avg Return | =AVERAGE(B2:B6) | =AVERAGE(C2:C6) |
| 9 | Volatility | =STDEV(B2:B6)   | =STDEV(C2:C6)   |

Fund A shows higher average returns but also higher volatility. Fund B offers modest, stable returns.

Combining STDEV with Other Functions

Coefficient of Variation

The coefficient of variation (CV) expresses standard deviation as a percentage of the mean, enabling comparison across datasets with different scales:

=STDEV(A1:A100) / AVERAGE(A1:A100) * 100

A CV of 15% means the standard deviation is 15% of the average value. This normalizes variability for meaningful comparisons.

Conditional Standard Deviation

Google Sheets lacks a native STDEV.IF function. Use FILTER to calculate standard deviation for a subset:

|   | A          | B        | C        |
|---|------------|----------|----------|
| 1 | Region     | Sales    |          |
| 2 | North      | 45000    |          |
| 3 | South      | 52000    |          |
| 4 | North      | 48000    |          |
| 5 | South      | 38000    |          |
| 6 | North      | 51000    |          |
| 7 |------------|----------|----------|
| 8 | North StdDev | =STDEV(FILTER(B2:B6, A2:A6="North")) |
| 9 | South StdDev | =STDEV(FILTER(B2:B6, A2:A6="South")) |

The FILTER function returns only rows matching the condition, and STDEV calculates on that filtered subset.

For multiple conditions:

=STDEV(FILTER(B2:B100, A2:A100="North", C2:C100>=2024))

Dynamic Ranges

Calculate standard deviation for the last N entries using OFFSET:

=STDEV(OFFSET(A1, COUNT(A:A)-12, 0, 12, 1))

This formula finds the last 12 numeric values in column A and calculates their standard deviation—useful for rolling volatility calculations.

Common Errors and Troubleshooting

#DIV/0! Error

STDEV requires at least two numeric values. One value or an empty range triggers this error:

=STDEV(A1)           // Error: single value
=STDEV(A1:A10)       // Error if range contains 0-1 numbers

#VALUE! Error

This appears when the function receives unparseable input:

=STDEV("text")       // Error: text passed directly
=STDEV(A1:A10, "x")  // Error: text as explicit argument

Text within a range is ignored, but text passed directly as an argument causes errors.

Unexpected Results from Mixed Data

If your results seem wrong, check for hidden text or inconsistent formatting:

| A        | Notes                    |
|----------|--------------------------|
| 100      | Number                   |
| 200      | Number                   |
| 300      | Text formatted as number |
| 400      | Number                   |

Cell A3 might look like a number but contain text. STDEV ignores it, skewing your results. Use =ISNUMBER(A3) to verify cell contents.

Error-Handling Wrapper

Protect your formulas with IFERROR:

=IFERROR(STDEV(A1:A100), "Insufficient data")

For more granular handling:

=IF(COUNT(A1:A100)<2, "Need 2+ values", STDEV(A1:A100))

Summary and Quick Reference

Standard deviation quantifies data spread. Choose your function based on whether you’re analyzing a sample or complete population.

Quick syntax reference:

=STDEV(range)                           // Basic usage
=STDEV(FILTER(data, criteria))          // Conditional
=STDEV(range) / AVERAGE(range) * 100    // Coefficient of variation
=IFERROR(STDEV(range), "Error")         // With error handling

Related functions worth exploring: VAR and VARP for variance calculations, AVERAGE for mean, MEDIAN for central tendency, and PERCENTILE for distribution analysis.