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:

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:

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: