How to Find Variance in Excel

Calculating the spread of your data using variance and standard deviation

Calculating Variance and Standard Deviation in Excel

A sample of data is often summarized using two statistics: its average value and a measure of how spread out it is. Variance and standard deviation are both measures of how spread out it is. There are several functions in Excel to calculate variance and standard deviation. Below, we'll explain how to decide which one to use, and how to find variance in Excel.

Instructions in this article apply to Excel 2019, 2016, 2013, 2010, 2007, Excel for Office 365, and Excel Online.

Summarizing Data: Central Tendency and Spread

The central tendency tells you where the middle of the data is, or the average value. Some common measures of the central tendency include the mean, the median, and the mode.

The spread of data means how much individual results differ from the average. The simplest measure of spread is the range, but it’s not very useful because it tends to keep increasing as you sample more data. Variance and standard deviation are much better measures of spread. The variance is simply the standard deviation squared. 

Standard Deviation and Variance Formula

Both the standard deviation and the variance are a way of measuring how far, on average, each data point is from the mean.

If you were calculating them by hand you would start by finding the mean for all your data. You would then find the difference between each observation and the mean, square all those differences, add them all together, then divide by the number of observation.

This would give the variance, a kind of average for all the squared differences. Taking the square root of the variance is a way of correcting for the fact that all the differences were squared. This is called the standard deviation and it's what you will usually use to measure the spread of data. If this is confusing, don’t worry, that’s why we’re going to get Excel to do the actual calculations.

Sample or Population?

Often your data will be a sample taken from some larger population. You want to use that sample to estimate the variance or standard deviation for the population as a whole. In this case, instead of dividing by the number of observation (n) you divide by n-1. These two different types of calculation have different functions in Excel:

  • Functions with P: Gives the standard deviation for the actual values you have entered. They assume your data is the whole population (dividing by n).
  • Functions with an S: Gives the standard deviation for a whole population, assuming your data is a sample taken from it (dividing by n-1). This can be confusing, as this formula is giving you the estimated variance for the population; the S indicates the dataset is a sample, but the result is for the population.

Using the Standard Deviation Formula in Excel

To calculate the standard deviation in Excel follow these steps.

  1. Enter you data into Excel. Before you can use the statistics functions in Excel you need to have all your data in an Excel range: a column, a row, or a group matrix of columns and rows. You need to be able to select all the data without selecting any other values.

    For the rest of this example, it's assumed your data is in the range A1:A20.

  2. If your data represents the entire population, enter the formula "=STDEV.P(A1:A20)." Alternatively, if your data is a sample from some larger population, enter the formula "=STDEV.P(A1:A20)."

    If you're using Excel 2007 or earlier, or you want your file to be compatible with these versions, the formulas are "=STDEVP(A1:A20)," if your data is the entire population; "=STDEV(A1:A20)," if your data is a sample from a larger population.

  3. The standard deviation will be displayed in the cell.

How to Calculate Variance in Excel

Calculating variance is very similar to calculating standard deviation.

  1. Ensure your data is in a single range of cells in Excel.

  2. If your data represents the entire population, enter the formula "=VAR.P(A1:A20)." Alternatively, if your data is a sample from some larger population, enter the formula "=VAR.S(A1:A20)."

    If you're using Excel 2007 or earlier, or you want your file to be compatible with these versions, the formulas are: "=VARP(A1:A20)," if your data is the entire population, or "=VAR(A1:A20)," if your data is a sample from a larger population.

  3. The variance for your data will be displayed in the cell.