To calculate standard deviation in Google Sheets, type =STDEV(A2:A100) if your data is a sample, or =STDEVP(A2:A100) if it covers an entire population, using your own range. If you want any text in the data counted as 0, use STDEVA or STDEVPA instead.
Here's the catch, and it's buried in Google's notes. STDEV returns an error if a value you type straight into the formula is text, but if a cell you reference contains text, that cell's simply ignored. So a column with a stray word in it won't break STDEV; it'll quietly leave that row out. And if you give it fewer than two values, you get #DIV/0!.
Which of the four functions to pick
| Function | Use it for | What happens to text |
|---|---|---|
| STDEV | A sample | Text typed into the formula errors; text in a referenced cell is ignored |
| STDEVP | An entire population | Returns an error if a value argument includes text |
| STDEVA | A sample | Each text value is set to 0 |
| STDEVPA | An entire population | Each text value is set to 0 |
The sample or population question is the one that matters. If your numbers are every value you care about, such as every order in a month, that's a population. If they're a slice standing in for something bigger, such as a survey of some customers, that's a sample.
Step by step with a column of numbers
- Put your values in one column, for example A2:A100.
- Click an empty cell where you want the result.
- Type =STDEV(A2:A100) for a sample, or =STDEVP(A2:A100) for a population.
- Press Enter.
- To add more data, list extra values or ranges after a comma, as value2 and onwards.
- To count text as 0 instead, use the same range with the other pair, such as =STDEVA(A2:A100) or =STDEVPA(A2:A100).
You can also type the numbers in directly, as Google does in its sample: =STDEV(1,2,3,4,5,6,7,8,9,10). And although STDEV is specified as taking a maximum of 30 arguments, Google Sheets supports an arbitrary number of them, so you won't hit that limit. Google also documents BigQuery versions of STDEV, STDEVP and VAR, so the same functions are there when you work with BigQuery data in Sheets.
Why sample and population give different answers
The difference comes from variance. Google explains that VAR takes the sum of the squares of each value's deviation from the mean and divides by the number of values minus one. The population version divides by the size of the dataset without subtracting one. Standard deviation is the square root of that variance, so the sample version, dividing by one fewer, comes out a little higher on the same data.
Checking your answer with VAR
Each standard deviation function has a variance twin, and Google says each one equals the square root of that twin on the same dataset. That gives you a quick way to check a result you're unsure of.
| Standard deviation | Equals | Variance function |
|---|---|---|
| STDEV(...) | SQRT(VAR(...)) | VAR: variance of a sample |
| STDEVP(...) | SQRT(VARP(...)) | VARP: variance of an entire population |
| STDEVA(...) | SQRT(VARA(...)) | VARA: sample, text set to 0 |
| STDEVPA(...) | SQRT(VARPA(...)) | VARPA: population, text set to 0 |
One difference trips people up. VAR only returns an error if all of the value arguments are text, while STDEV errors if any of them include text. So the two can disagree on a messy formula even though they agree on clean numbers.
Fixing #DIV/0! and other surprises
- #DIV/0! means the total number of values supplied isn't at least two. Check your range isn't empty or pointing at the wrong column.
- An unexpected error from STDEV or STDEVP usually means text has been typed into the formula itself. Switch to STDEVA or STDEVPA if text should count as 0.
- A result that looks too high or low can come from text cells being skipped by STDEV but counted as 0 by STDEVA. Pick the one that matches what the text means in your data.
- If you want the spread across every value and you used STDEV, switch to STDEVP, and the other way round.
Other spread and shape functions in Sheets
| Function | What Google says it does |
|---|---|
| DSTDEV | Standard deviation of a population sample selected from a database table-like range using a SQL-like query |
| DSTDEVP | Standard deviation of an entire population selected the same way |
| DVAR and DVARP | The variance versions of the same database functions |
| DEVSQ | The sum of squares of deviations based on a sample |
| AVEDEV | The average of the magnitudes of deviations of data from a dataset's mean |
| SKEW | The skewness of a dataset, which describes its symmetry about the mean |
| KURT | The kurtosis of a dataset, which describes its shape, in particular how peaked it looks |
Numbers alone don't always tell the story, so it helps to see the spread too. A histogram in Google Sheets shows how values cluster, and a box and whisker plot in Google Sheets shows the range and middle at a glance.
My advice on standard deviation in Sheets
I'd use STDEV by default, because most data you'll analyse is a sample of something bigger, and switch to STDEVP only when you've got every value. I'd reach for STDEVA or STDEVPA only when a word in a cell really should count as zero. And whenever a figure looks odd, I'd check it against SQRT(VAR(...)) on the same range. If you also work in Microsoft's spreadsheet, here's how standard deviation in Excel works.
Want AI doing the heavy lifting in your marketing?
I build the systems that handle the boring 80 percent, so you get your week back. Done properly, with the human kept in.
Frequently asked questions
What is the formula for standard deviation in Google Sheets?
Use =STDEV(range) for a sample, such as =STDEV(A2:A100), or =STDEVP(range) for an entire population.
What's the difference between STDEV and STDEVP in Google Sheets?
STDEV calculates standard deviation based on a sample, while STDEVP calculates it based on an entire population.
Why does STDEV return #DIV/0! in Google Sheets?
STDEV returns #DIV/0! if the total number of values supplied isn't at least two.
Does STDEV ignore text in Google Sheets?
Text in a referenced cell is ignored, but STDEV returns an error if text is typed into the formula as a value. STDEVA sets text to 0 instead.
Should I use STDEV or STDEVA?
Use STDEV if text in your data should be left out of the calculation, and STDEVA if each text value should count as 0.
How is STDEV related to VAR?
STDEV is equivalent to the square root of the variance, or SQRT(VAR(...)) using the same dataset.
