How to calculate sample variance using excel

Written by liaqat ali | 13/05/2017

The variance is a statistical term that measures the variability of data. It is used in advanced statistical calculations. If you know how to calculate standard deviation, calculating the variance is very simple. By squaring the standard deviation, the variance value is obtained. For example, if the standard deviation calculated value is 10, the variance will be the square of 10, which is 100. There are different ways to calculate the variance. One of the simplest ways is to use a Microsoft Excel spreadsheet.

Add the numbers in the first column of your Excel 2007 document. For example, add the numbers 100, 200, 300, 400, 500 and 600 in A2, A3, A4, A5, A6 and A7.

For this example, click on the "A9" cell. This is the cell where you will calculate the variance. When you calculate another variance, choose any cell at the bottom of the list of numbers you are using.

Click on the "Formulas" tab while you are in the "A9" cell. Click on "Statistical" on the drop-down menu.

Click "Insert Function" on the top left-hand side of the Excel spreadsheet. The "Insert Function" window will pop open.

Click on the drop-down menu of "Or select a category."

Scroll down the "Select a function" window. Choose "VAR," which is the function of variance based on the sample.

Click "OK." The "Functions Arguments" window will pop open. Ensure that on "Number 1" cell, A2:A7 is populated. If A2:A7 is not populated, enter A2:A7 manually. Click "OK."

The variance has been successfully calculated. In this example, the calculated value of the variance is 35,000.


When variance has been calculated, check twice to ensure your obtained value is correct.

Tips and warnings

  • When variance has been calculated, check twice to ensure your obtained value is correct.

Things you need

  • Microsoft Excel 2007

Show MoreHide

  • All types
  • Articles
  • Slideshows
  • Videos
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the site, you consent to the use of cookies. For more information, please see our Cookie policy.