How to Get Standard Deviation in Google Sheets

Standard deviation is the most widely-used measure of how spread out a set of numbers is, and it's also the most widely-misused. In Google Sheets, there are four different functions that compute it: STDEV, STDEVP, STDEVA, and STDEVPA. Picking the wrong one gives you an answer that's quietly wrong by 5% to 20%, which is usually enough to flip a conclusion. Worth getting straight before you build a dashboard on top of it.

The Two Real Choices

STDEV is sample standard deviation. Use it when your data is a subset of a larger population that you can't fully measure. Examples: 100 customer transactions out of millions, a survey of 500 respondents out of a country, daily revenue numbers for one month out of a multi-year history. STDEV uses the n-1 divisor (Bessel's correction), which gives you a slightly larger number that's an unbiased estimate of the true population spread.

STDEVP is population standard deviation. Use it when your data is the entire population, not a sample. Examples: every single transaction in a closed quarter, every employee's salary at a specific company, the temperature on every day of a specific year you're describing. STDEVP uses the n divisor, no correction needed because there's no inference to make.

The two answers diverge by a factor of √(n / (n-1)). For n = 10, that's about 5%. For n = 100, it's about 0.5%. For very small samples (n < 20), the choice matters meaningfully; for large datasets it usually doesn't.

The STDEVA and STDEVPA Variants

These two are like STDEV and STDEVP but they treat text and boolean values differently. STDEV ignores text values entirely. STDEVA counts text as 0 and boolean TRUE as 1. The variant matters if your data column has a mix of numbers and text, and you want to fold the text rows in as zeros rather than skipping them.

For most use cases, you don't want this behavior. STDEV's default skip-text behavior is what you want when your column has occasional gaps or string error markers. Use the A variants only when you specifically need text to count as zero, which is uncommon.

How to Actually Type the Formula

Click in any empty cell. Type =STDEV(, then drag-select the range of cells containing your data, then close with ). Hit enter. The cell shows the standard deviation.

For a column of data in cells A2 through A101: =STDEV(A2:A101). For multiple disconnected ranges: =STDEV(A2:A101, C2:C101), separating the ranges with commas. Sheets handles the union automatically.

If you also want the mean, add another cell with =AVERAGE(A2:A101). The mean and standard deviation together describe the distribution roughly: under a normal distribution, about 68% of values fall within one standard deviation of the mean, 95% within two, and 99.7% within three. That's the rule of thumb that makes standard deviation useful as a summary number.

Where Standard Deviation Misleads

Standard deviation assumes the underlying distribution is roughly symmetric and not too heavy-tailed. For income data, viral content metrics, network packet sizes, and most things that follow a power law or log-normal distribution, the standard deviation is a poor summary because the rare large values dominate it.

For those cases, log-transform the data first (use the LN or LOG functions in Sheets) and compute the standard deviation of the transformed values. The result is the "multiplicative standard deviation," which is much more informative for skewed data. Or use percentiles (PERCENTILE function) to describe the distribution by quantiles instead of by mean and spread. This is the most common gotcha when copying a formula from a tutorial into real-world data; the tutorial assumes a normal distribution, your data isn't, and the answer the formula produces is technically correct but practically useless.

The One-Line Rule

Sample data, normal-ish distribution: STDEV. Full population, normal-ish distribution: STDEVP. Heavy-tailed data: log-transform first, or use percentiles instead. That's the entire decision tree for 95% of spreadsheet uses of standard deviation.