How to Calculate Standard Deviation, Variance, and Z-Score in Google Sheets

Learn how to better navigate Google Sheets and the tools it has to offer. Standard deviation, variance, and z-score are all important aspects and tools at your disposal.

Table of Contents

In this tutorial, we will learn how to calculate the standard deviation, the variance and the Z-score using Google Sheets. We talked about these quantities in one of our articles titled Data Analysis 101: Data Analysis Pitfalls To Watch For. Click here to read the article again.

For our tutorial, we will use a generated normal distribution of scores from Social Science Statistics. You can access the generator here.

How to calculate standard deviation

There are several functions that you can use to calculate the standard deviation. The main ones are STDEV() and STDEVP(). To choose which of these functions to use, you need to know the answer to the following question:

Is it the dataset that you have just a slice or a sample of the entire population?

If your answer is yes, then use STDEV(). If your answer is no, as the dataset contains the entire population, then use STDEVP(). Both STDEV() and STDEVP() use the following syntax:

=STDEV(range)

where the range is the range of cells that contain the dataset. You can see the difference in their values below:

STDEV and STDEVP.
STDEV and STDEVP.


Finally, if the dataset contains text that should otherwise not belong there, both STDEV() and STDEVP() ignore them.

How to calculate variance

In a similar fashion, Google Sheets offers two main functions to calculate variance: VAR() and VARP(). Just like standard deviation, VAR() is used to find the variance of the sample while VARP() is used to find the variance of the population. Similarly, the syntax of VAR() and VARP() are the same as that of STDEV() and STDEVP():

=VAR(range)

where the range is the range of cells that contain the dataset. You can see the difference in their values below:

VAR and VARP.
VAR and VARP.


Similarly, VAR() and VARP() ignore cells containing text.

How to calculate z-score

Finally, Google Sheets has a convenient formula for calculating the Z-score: Z.TEST(). This formula requires you to add the range of the distribution and the cell containing the specific number:

=Z.TEST(range, value)

If you plan to copy the formula by dragging the box to the lower-right corner of the blue highlighter in Google Sheets, make sure you append $ signs to keep it from changing. For example, our data is from A2 to A100. We want to calculate the Z-score on A3. The actual formula can then be written as follows:

=Z.TEST(A2:A100,A3)

The column now looks like this:

Z.TEST applied to all data.
Z.TEST applied to all data.


Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.

April 10, 2021

How to Calculate Standard Deviation, Variance, and Z-Score in Google Sheets

Chart with arrows pointing up and down

In this tutorial, we will learn how to calculate the standard deviation, the variance and the Z-score using Google Sheets. We talked about these quantities in one of our articles titled Data Analysis 101: Data Analysis Pitfalls To Watch For. Click here to read the article again.

For our tutorial, we will use a generated normal distribution of scores from Social Science Statistics. You can access the generator here.

How to calculate standard deviation

There are several functions that you can use to calculate the standard deviation. The main ones are STDEV() and STDEVP(). To choose which of these functions to use, you need to know the answer to the following question:

Is it the dataset that you have just a slice or a sample of the entire population?

If your answer is yes, then use STDEV(). If your answer is no, as the dataset contains the entire population, then use STDEVP(). Both STDEV() and STDEVP() use the following syntax:

=STDEV(range)

where the range is the range of cells that contain the dataset. You can see the difference in their values below:

STDEV and STDEVP.
STDEV and STDEVP.


Finally, if the dataset contains text that should otherwise not belong there, both STDEV() and STDEVP() ignore them.

How to calculate variance

In a similar fashion, Google Sheets offers two main functions to calculate variance: VAR() and VARP(). Just like standard deviation, VAR() is used to find the variance of the sample while VARP() is used to find the variance of the population. Similarly, the syntax of VAR() and VARP() are the same as that of STDEV() and STDEVP():

=VAR(range)

where the range is the range of cells that contain the dataset. You can see the difference in their values below:

VAR and VARP.
VAR and VARP.


Similarly, VAR() and VARP() ignore cells containing text.

How to calculate z-score

Finally, Google Sheets has a convenient formula for calculating the Z-score: Z.TEST(). This formula requires you to add the range of the distribution and the cell containing the specific number:

=Z.TEST(range, value)

If you plan to copy the formula by dragging the box to the lower-right corner of the blue highlighter in Google Sheets, make sure you append $ signs to keep it from changing. For example, our data is from A2 to A100. We want to calculate the Z-score on A3. The actual formula can then be written as follows:

=Z.TEST(A2:A100,A3)

The column now looks like this:

Z.TEST applied to all data.
Z.TEST applied to all data.


Suscribe to get more data and analytics tips!

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.