How do you calculate variance in Excel?

Variance is a measurement of the spread between numbers in a data set. The variance measures how far each number in the set is from the mean.

Using a data set chart, we can observe what the linear relationship of the various data points, or numbers, is. We do this by drawing a regression line, which attempts to minimize the distance of any individual data point from the line itself. In the chart below, the data points are the blue dots, the orange line is the regression line, and the red arrows are the distance from the observed data and the regression line. 

When we calculate a variance, we are asking, given the relationship of all these data points, how much distance do we expect on the next data point? This "distance" is called the error term, and it's what variance is measuring. 

By itself, variance is not often useful because it does not have a unit, which makes it hard to measure and compare. However, the square root of variance is the standard deviation, and that is both practical as a measurement. 

Calculating variance in Excel is easy if you have the data set already entered into the software. In the example below, we will calculate the variance of 20 days of daily returns in the highly popular exchange-traded fund (ETF) named SPY, which invests in the S&P 500.

1. The formula is =VAR.S(select data)

The reason you want to use VAR.S and not VAR.P (which is another formula offered) is that often you don't have the entire population of data to measure. For example, if we had all returns in history of the SPY ETF in our table, we could use the population measurement VAR.P, but since we are only measuring the last 20 days to illustrate the concept, we will use VAR.S. 

As you can see, the calculated variance value of .000018674 tells us little about the data set, by itself. If we went on to square root that value to get the standard deviation of returns, that would be more useful.