STDEV Function in Excel, Google Sheets, Airtable & Notion
STDEV measures how spread out numbers are around their average. Use STDEV.S when your data is a sample, and STDEV.P when it is the entire population.
Syntax in each app
| App | Support | Syntax & notes |
|---|---|---|
| Excel | ✔ Built in | STDEV.S(number1, [number2], ...)STDEV() is the older name for STDEV.S. Use STDEV.P() for a population. |
| Google Sheets | ✔ Built in | STDEV.S(value1, [value2, ...])Also STDEV (sample) and STDEV.P / STDEVP (population). |
| Airtable | ≈ Workaround | SQRT(({Sum of squares} - POWER({Sum}, 2) / {Count}) / ({Count} - 1))No STDEV. Add a formula field POWER({Value}, 2) to the linked records, then rollups for its SUM(values), the SUM(values) of the value and COUNT(values), and combine them as shown. Use / {Count} instead of / ({Count} - 1) for a population. |
| Notion | ≈ Workaround | lets(x, list, m, mean(x), sqrt(sum(x.map(pow(current - m, 2))) / (x.length() - 1)))No standard deviation function; this formula is the textbook definition. Divide by x.length() instead of x.length() - 1 for a population. |
| Excel | =STDEV.S(A2:A9) | open excel.new ↗ |
|---|---|---|
| Google Sheets | =STDEV.S(A2:A9) | open sheets.new ↗ |
| Airtable workaround | SQRT((SUM(4, 16, 16, 16, 25, 25, 49, 81) - POWER(SUM(2, 4, 4, 4, 5, 5, 7, 9), 2) / 8) / 7) | |
| Notion workaround | lets(x, [2, 4, 4, 4, 5, 5, 7, 9], m, mean(x), sqrt(sum(x.map(pow(current - m, 2))) / (x.length() - 1))) |
Excel & Sheets use cell ranges. Paste this sample data into cell A1 of a blank sheet, then paste the formula into any empty cell.
Value 2 4 4 4 5 5 7 9
The Airtable formula is written out with the numbers squared, so you can check it; with real data the three parts come from rollup fields.
Arguments
| Argument | What it means |
|---|---|
number1 | The first number, cell or range. |
[number2], ... optional | More numbers or ranges. You need at least two numbers for a sample. |
Examples
| Formula | Result | Why | Copy |
|---|---|---|---|
=STDEV.P(2, 4, 4, 4, 5, 5, 7, 9) | 2 | Population: the mean is 5 and the average squared distance is 4. | |
=STDEV.S(2, 4, 4, 4, 5, 5, 7, 9) | 2.13809 | Sample: divides by n − 1 = 7, so the result is a little larger. | |
=STDEV.S(10, 10, 10) | 0 | No spread at all. | |
=STDEV.S(5) | #DIV/0! | A sample needs at least two numbers. |
Examples use Excel / Google Sheets syntax unless noted.
How STDEV works
Standard deviation is the typical distance of each value from the average, in the same units as the data. Small means the values cluster tightly around the mean; large means they are spread out. For roughly bell-shaped data, about two thirds of values fall within one standard deviation of the mean.
The choice between the two versions is about what your numbers represent. If they are all the values you care about (every employee, every day this month), use STDEV.P, which divides by n. If they are a sample used to estimate a bigger group (a survey, a batch of test parts), use STDEV.S, which divides by n − 1 and gives a slightly larger answer. With many values the difference is tiny; with five it is noticeable. The plain STDEV function is the sample version. Like AVERAGE, both ignore text and blanks in ranges; STDEV.S returns #DIV/0! with fewer than two numbers. The variance is the square, available as VAR.S and VAR.P.
Neither Airtable nor Notion has a standard deviation function, but both can compute it. In Notion, the lets formula maps each value to its squared distance from the mean. In Airtable, formulas cannot loop over a rollup, so use the identity “sum of squares minus square of sum over n”: one helper formula field for the square, three rollups, and one final formula.
Frequently Asked Questions
- Should I use STDEV.S or STDEV.P?
- STDEV.P when the data is the entire population you are describing; STDEV.S when it is a sample used to estimate a larger group. If unsure, STDEV.S is the more common and more cautious choice.
- What is the difference between STDEV and STDEV.S?
- None. STDEV is the older name kept for compatibility.