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.

STDEV.S and STDEV.P: The Names You Should Actually Type

Google Sheets also accepts STDEV.S and STDEV.P. They compute exactly the same things as STDEV and STDEVP. The difference is that the reader of your spreadsheet can tell which one you meant.

This sounds like pedantry and is not. The single most common way this goes wrong in a real workbook is not a mathematical mistake, it is that someone six months later cannot tell whether =STDEV(A2:A101) was a deliberate choice or the first autocomplete suggestion, so they leave it alone. STDEVP and STDEV differ by one character and mean opposite things. STDEV.P and STDEV.S announce the assumption in the name. Use the dotted forms in anything another person will open.

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.

Standard Deviation of Only Some Rows

The formula everyone actually needs five minutes after the basic one: spread of a subset. Sheets has no STDEVIF, which is the thing people search for and give up on. Two ways to get it.

FILTER, which reads best.For the standard deviation of column B where column A equals "West":

=STDEV.S(FILTER(B2:B101, A2:A101 = "West"))

Stack conditions by multiplying them, since FILTER takes each condition as a separate argument: =STDEV.S(FILTER(B2:B101, A2:A101 = "West", C2:C101 > 100)).

An array IF, if you inherited one. =STDEV.S(IF(A2:A101 = "West", B2:B101)) does the same job. In modern Sheets this evaluates as an array without needing Ctrl+Shift+Enter, but it is harder to read and harder to extend than the FILTER version. Prefer FILTER for anything new.

On blanks and text: STDEV ignores cells containing text rather than erroring, and skips truly empty cells.^[1] A cell holding an empty string from a formula, though, such as the output of IFERROR(..., ""), counts as text and is skipped too, so a column full of those quietly shrinks your n. If the count matters, check it with =COUNT(A2:A101), which counts numbers only, against =COUNTA(A2:A101), which counts anything non-empty. When those two disagree, that gap is your answer.

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.S. Full population, normal-ish distribution: STDEV.P. Subset of rows: wrap the range in FILTER. Heavy-tailed data: log-transform first, or use percentiles instead. That's the entire decision tree for 95% of spreadsheet uses of standard deviation.

If you are computing spread on returns rather than on measurements, the distribution caveat above is the whole ballgame rather than a footnote. Financial returns are heavy-tailed, and standard deviation is the number underneath most published volatility figures, which is worth keeping in mind when you read one. That thread continues in why traders quote percentages instead of dollars.

Picking the right statistic matters less often than picking the right instrument, and the US multilingual numbers are a case study in a figure that measures something other than what everyone quotes it for.