April 10, 2021

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.

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:

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

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:

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

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:

Suscribe to get more data and analytics tips!

Thank you! Your submission has been received!

Oops! Something went wrong while submitting the form.