How To Calculate Variance in Excel

In this blog post, we will show you how to calculate variance in Excel.

Variance is a measure of how spread out a set of data points are.

It can be used to identify whether a data set is symmetric or not.

In addition, it can also be used to measure the risk of an investment.

We will show you how to calculate variance both with and without the use of Excel functions.

Let’s get started!

How to Calculate Variance in Excel

Suppose this is the dataset you will use for both sample and population variance computation.

Untitled

Method 1. Calculating Sample Variance

Step 1. Open your Excel workbook

Untitled

Step 2. Select the cell where you want the sample variance to appear

The cell you click should appear with a dark green border.

Untitled

Step 3. Locate the cell range of your data

Identify the cell range of your data by checking the cell coordinates, composed of a column letter and row number.

A cell range is then the first and last cell coordinates separated by a colon.

Thus, the cell range of your needed data is A2:A11.

Untitled

Step 4. Input the VAR formula

Since you are only going to calculate the sample variance of the dataset, the function to be utilized is VAR.

Input the following formula into the formula bar:

=VAR(A2:A11)

Untitled

Step 5: Click “Enter” on your keyboard

Clicking enter should automatically add the sample variance into the cell you selected in step 2.

Untitled

Method 2. Calculating Population Variance

The steps mentioned in the first method will also be used to compute population variance.

The only difference is the formula you have to input.

Step 1. Load up your sheet

Untitled

Step 2. Select the cell where you want the population variance to appear

The selected cell should appear with a dark green border.

Untitled

Step 3. Locate the cell range of your data

Identify the cell range of your data by checking the column letter and row number.

It is the location of the first and last cells separated by a colon.

The cell range of the data in this set is A2:A11.

Untitled

Step 4. Input the VARP formula

Unlike the first method, which only uses the VAR function, this method utilizes VARP since it computes the entire data population.

Thus, input the following formula into the formula bar:

=VARP(A2:A11)

Untitled

Step 5: Click “Enter” on your keyboard

Clicking enter should automatically add the population variance into the cell you selected in step 2.

Untitled

Conclusion

Calculating the variance of datasets becomes a simple task through the use of spreadsheet applications like Google Sheets and Excel.

These applications utilize specific functions that automatically calculate the data inside a range of cells once you input the appropriate formula.

This article breaks down the steps to calculate two types of variances: sample and population variance in Google Sheets and Excel.

Summary

That’s the end of this tutorial.

We hope this article helps you learn how to calculate Variance in Excel