How to Calculate Variance on Excel

Variance measures the amount of variability in data by factoring in the number of data points and each point's deviation from the mean. High variance indicates that data varied widely from the calculated average, or that there weren't enough data points to know if the mean is closely suggestive of future measurements.

Video of the Day

As an example, if sales figures were exactly $150 each day of the year, the average would be $150 and the zero variation gives confidence that upcoming sales will also be close to $150. However, if the figures ranged from zero to $300, the average might still be $150, but the high variance tells you upcoming sales could fall into a large range of possible results. Likewise, if you only measured two days of sales --say $130 in one and $170 in the other -- the average is the same, but the low number of measurements illustrates lower confidence in the mean.

Excel 2013 integrates six functions to calculate variance of your data. In most cases, you probably only need the Var.S or Var.P functions, depending on if your data is a sample or the entire population, respectively.

Determine if your data is a sample or a population. A sample is a subset of a larger population of data, such as measuring daily sales data over a certain period of time to help predict future sales. However, if you were measuring sales every day of the year to produce an average and accompanying variance for only that year, that would be a population, because you're including every possible data point for the intended measurement. That is, you're not inferring future values and are instead measuring the entirety of data. As you might expect, sample variance tends to be larger than population variance, because there's more uncertainty due to the uncollected data points.

Enter the data points in an Excel spreadsheet. As an example, you might enter total daily sales in cells A1 through A60.

Use the VAR.S or VAR.P function to calculate sample or population variance, respectively, using the format =VAR.S(range) or =VAR.P(range) where "range" references the previously entered data. If the example was being used as a sample, enter =VAR.S(A1:A60). Alternatively, enter =VAR.S(A:A) if no other data are included in the column.

Show Comments