How to Calculate and Find Variance in Excel

This article explains data summarization and how to use deviation and variance formulas in Excel for Microsoft 365, Excel 2019, 2016, 2013, 2010, 2007, 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 standard 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 most straightforward 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. 

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. Several functions let you calculate variance in Excel. Below, we'll explain how to decide which one to use and how to find variance in Excel.

Standard Deviation and Variance Formula

Both the standard deviation and the variance mesure 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.

Doing so would give the variance, a kind of average for all the squared differences. Taking the variance's square root corrects the fact that all the differences were squared, resulting in the standard deviation. You will use it to measure the spread of data. If this is confusing, don't worry. Excel does 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). It can be confusing, as this formula provides 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 your 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, the 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(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.