Variance is a statistical measurement that looks at how far numbers are distributed from the average. It’s a tedious manual calculation, but easy to do in a spreadsheet. Let’s learn how to calculate variance in Microsoft Excel.
How to Calculate Variance in Excel
Excel offers a pair of built-in functions to help to calculate variance. To decide which one to use, you need to know whether you’re working with a sample or population. Let’s look at both.
Imagine you’re working with a table of sample data, and you want to calculate the variance. Since this is sample data, you’ll need to use Excel’s VAR.S function. The S, of course, stands for sample.
To get started, click into any empty cell on your worksheet. Type an = sign. This tells Excel that you’re inputting a formula. Then, type in your function for variance of a sample: VAR.S. Then, type (.
So far, your variance formula should read:
=VAR.S(
Now, it’s time to select the data itself. In this example, it lies in cells B2:B6. To make it easy, you can simply click and drag your cursor to select this range. Finally, type ) to complete your formula, which will now read:
=VAR.S(B2:B6)
Hit Enter on your keyboard, and Excel will calculate the variance and display it to you in the empty cell where you placed your formula. It’s that simple.
Now, imagine for a moment that this same data represented the entire population, instead of just a sample. Excel can help you here, too. You just need to make a slight change to your formula.
The function to calculate variance for a population in Excel is VAR.P, where P stands for population. So, if this data was a population, your formula would be:
=VAR.P(B2:B6).
As you can see, you change only a single letter. Thanks to Excel, it’s incredibly easy to calculate variance.
Page Breaks in Excel