Don’t forget to share this post

Standard deviation is one of the main measures of dispersion in a dataset. It shows the spread of the data by calculating the average distance of values to the dataset’s mean, regardless of whether they are above or below it. In other words, a low standard deviation indicates that most of the values are close to the mean or average of the dataset, while a high value indicates that the values are spread out over a wider range. The formula used to calculate it is straightforward, but if you use a spreadsheet program like Microsoft Excel or Google Sheets, there are many built-in functions that will calculate it for you.

In this guide, you will learn how to calculate standard deviation and review all the built-in functions available to calculate it for you in Google Sheets. You will learn the differences between the multiple versions of the STDEV function, like STDEVP, STDEVA, STDEVPA, DSTDEV, and DSTDEVP. You have step-by-step instructions on how to use the functions with different types of data and in combination with other functions.

How to Find Standard Deviation in Google Sheets?

To calculate the standard deviation of a dataset, you need to sum the square of the differences between each value and the mean, then divide by the size of the dataset (population) or the size of the dataset minus one (sample). Finally, take the square of the result.

Standard Deviation of Population

The formula used to calculate the standard deviation for a population is as follows:

σ = √Σ( xi - μ)^2/N
  • σ = standard deviation of population
  • Σ = sum
  • xi = each value in dataset
  • μ = population mean
  • N = size of population

Standard Deviation of Sample

To calculate the standard deviation for a sample of the population, use the following formula:

s = √Σ( x- x̄)^2/(n-1)
  • s = standard deviation of population
  • Σ = sum
  • xi = each value in sample
  • = sample mean
  • n = size of sample

The choice between the two depends on the type of dataset you’re working with. If you have access to the data for the entire population, which isn’t often the case, you should use the population version of the formula. If you have a sample of the population, you should use the sample version. In addition to providing both of the formulas described above as built-in functions - STDEV for samples and STDEVP for populations - Google Sheets features some additional versions, all with the same syntax and parameters.

For example, both formulas are also available as database functions: DSTDEV and DSTDEVP. Like DSUM, these functions work with data structured as database-like tables.

Finally, there is another version of these formulas to be used on datasets that may contain text values in some cells, such as a dash or other characters to represent zero. The STDEVA and STDEVPA functions assume that cells with text are equal to a value of 0.

How to Calculate Standard Deviation in Google Sheets with the STDEV Function?

To calculate the standard deviation of a sample, you can use the STDEV function: STDEV(value/array1, value/array2*). Values can be typed or in the form of cell references. The function can be used on single values or arrays. When using single values, there must be a minimum of two.

1. Add Function

Type the STDEV function in an empty cell.

How to Find Standard Deviation in Google Sheets STDEV Function
How to Find Standard Deviation in Google Sheets - STDEV Function

2. Select Dataset

Type the values in your dataset, separated by commas. Alternatively, select the cells containing the values.

How to Find Standard Deviation in Google Sheets Add Values
How to Find Standard Deviation in Google Sheets - Add Values

3. Get Standard Deviation

Close the parenthesis and press ‘Enter’.

How to Find Standard Deviation in Google Sheets Standard Deviation
How to Find Standard Deviation in Google Sheets - Standard Deviation

Examples: Other Standard Deviation Functions in Google Sheets

Below, you have examples using STDEV vs. STDEVP, STDEVA vs. STDEVPA, and DSTDEV vs. DSTDEVP to find the standard deviation for different datasets.

How To Share Only One Tab in Google Sheets
How To Share Only One Tab in Google Sheets

When sharing a Google Sheets spreadsheet Google usually tries to share the entire document. Here’s how to share only one tab instead.

READ MORE

STDEV vs. STDEVP

In the example below, I will calculate the population and sample standard deviation for the same dataset to illustrate the difference. The dataset contains changes in price over a year for two vendors selling four different products. I will calculate the standard deviation for each product and vendor.

  1. 1. In an empty cell, type the STDEV function and select the first set of values.
How to Find Standard Deviation in Google Sheets STDEV Function 2
How to Find Standard Deviation in Google Sheets - STDEV Function
  1. 2. Press ‘Enter’ to see the result. Grab the fill handle and drag it right to the last column.
How to Find Standard Deviation in Google Sheets Sample Standard Deviation
How to Find Standard Deviation in Google Sheets - Sample Standard Deviation
  1. 3. In the row beneath, I have repeated steps 1 and 2 with the STDEVP function to see the difference.
How to Find Standard Deviation in Google Sheets Population Standard Deviation
How to Find Standard Deviation in Google Sheets - Population Standard Deviation

STDEVA vs. STDEVPA

Below, I will calculate the standard deviations for three tests. Since some of the cells contain dashes to indicate that the student missed the test, I will use STDEVA and STDEVPA so they are converted to zeroes.

  1. 1. In an empty cell, type the STDEVA function and select the first set of values.
How to Find Standard Deviation in Google Sheets STDEV Function 3
How to Find Standard Deviation in Google Sheets - STDEV Function
  1. 2. Press ‘Enter’ to see the result. Grab the fill handle and drag it right to the last column.
How to Find Standard Deviation in Google Sheets Sample Standard Deviation 2
How to Find Standard Deviation in Google Sheets - Sample Standard Deviation
  1. 3. In the row beneath, I have repeated steps 1 and 2 with the STDEVPA function to show the difference.
How to Find Standard Deviation in Google Sheets Population Standard Deviation 2
How to Find Standard Deviation in Google Sheets - Population Standard Deviation
How to Password Protect a Google Sheet
How to Password-Protect a Google Sheet?

If you work with important data in Google Sheets, you probably want an extra layer of protection. Here's how you can password protect a Google Sheet

READ MORE

DSTDEV vs. DSTDEVP

For this last example, I will use the database functions DSTDEV and DSTDEVP. To use these functions, you need to structure your data in tabular form. The first row should contain the headers of field labels, and subsequent rows should contain individual records. With these functions, cells containing text characters will be ignored, not replaced by zeroes.

  1. 1. Set up the criteria array for the function as shown below. In this case, I want to include all IDs, so I will leave the second row empty in the criteria array.
How to Find Standard Deviation in Google Sheets Set Up Criteria Array
How to Find Standard Deviation in Google Sheets - Set Up Criteria Array
  1. 2. In an empty cell, type the DSTDEV function and select the table, including the headers.
How to Find Standard Deviation in Google Sheets DSTDEV Function
How to Find Standard Deviation in Google Sheets - DSTDEV Function
  1. 3. Add a comma followed by the header or field label for the column you want. In this case, the ‘Test1’ column.
How to Find Standard Deviation in Google Sheets Add Field
How to Find Standard Deviation in Google Sheets - Add Field
  1. 4. Finally, select the cells containing the criteria array and close the parenthesis.
How to Find Standard Deviation in Google Sheets Add Criteria Array
How to Find Standard Deviation in Google Sheets - Add Criteria Array
  1. 5. Press ‘Enter’ to see the result. Below, I have repeated steps 2 to 5 with the DSTDEVP function to see the difference.
How to Find Standard Deviation in Google Sheets DSTDEV DSTDEVP
How to Find Standard Deviation in Google Sheets - DSTDEV & DSTDEVP

What is the Difference Between STDEV and STDEVP in Google Sheets?

The STDEV function calculates the standard deviation for datasets that constitute a sample of the population. STDEVP, however, calculates it for the population. The difference is in what is used in the formula’s denominator: n is used for a population, and n-1 for a sample.

Conclusion

Calculating standard deviation in Google Sheets is very easy. Higher standard deviation values indicate that the data is spread out, while a lower standard deviation indicates that the values are centered around the mean or average of the dataset. Remember that the value obtained for the standard deviation will vary depending on whether you use the sample or population formula. The standard deviation for a sample will be higher, as it is calculated over n-1, not N.

You now know the formulas used to find the standard deviation for a sample and a population, but you also know that Google Sheets provides multiple versions of the formulas as built-in functions. You have step-by-step instructions on how to find the sample and population standard deviations using STDEV and STDEVP. If some of the cells in your dataset contain text characters that represent zeroes, you can use STDEVA and STDEVPA to convert text values to zeroes. Finally, if your data is structured in a database-like table, you know how to use DSTDEV and DSTDEVP to find the standard deviation for a sample and a population.

Maria Del Olmo
Originally published Mar 17 2023, Updated Jun 26 2023

Layer is now Sheetgo

Automate your procesess on top of spreadsheets