Key Takeaways
You can calculate sample variance in Excel using a VAR, VAR.S, or VARA function. For population variance VARP, VAR.P, or VARPA. Just put your the cell range containing your dataset in the formula, like “VAR(B2:B12)”, and you’ll get the calculated variance.
Variance is used to measure the spread of data values against the mean in a data set. Microsoft Excel can be used to calculate this variance to help you analyse your data, determine its quality, and make important decisions.
What is Variance?
Variance measures how far the values in a data set are against the mean (average) value. The higher the variance, the more spread out your data is. In contrast, if the variance is low, it means there isn’t much of a spread in your data.
If you’re calculating the variance manually, you’ll first need to calculate the mean value for your data set. Next, you’ll calculate the difference between each number in your set and the mean value, and then square the result. You’ll then add the squared values together and divide them by the total number of values in the set to find the variance.
It’s possible to calculate the variance in a data set like this by using a small subset of your large data set (sample variance) or by calculating the variance using the entire data set as a whole (population variance).
Variance (and the statistical topics that surround it) can be quite difficult concepts to understand. To make it easier, let’s use a basic scenario. Let’s assume you have a list of enrolled pupils in a school.
You can use population variance to determine the spread of ages against the average age of the entire class. Alternatively, you can use sample variance by taking the list of pupils in a single class and using that smaller subset to determine the spread.
You use population variance to consider all of the pupils at once, while sample variance uses the smaller subset (in this example, a class). Depending on the size of your data set, it can be quicker and more practical to calculate the sample variance using a smaller subset of data to work out the mean.
How Variance Functions Are Used in Excel
In Excel, it’s simple to calculate either sample or population variance (as long as the data is available for you to use). There are six main functions you can use.
For sample variance using numerical values only, you can use VAR, or VAR.S. VAR was the original function, but this has been deprecated in favor of VAR.S in newer Excel versions. However, VAR is still available in Excel for compatibility reasons, including the version of Excel included with Microsoft 365 subscriptions.
To calculate population variance, you’ll need to use VARP or VAR.P. Like VAR and VAR.S, VARP is the original (deprecated) function, while VAR.P is the newer (and currently supported) function.
If your data set contains text strings or a logical test, you’ll need to use VARA (for sample variation) or VARP (for population variance) instead.
How to Calculate Sample Variance in Excel Using VAR, VAR.S, or VARA
To help you calculate sample variance in Excel, we’ll use an example data set below. You’ll need to replace any data we’re using here with your own.
Make sure to choose the correct function before you begin. For instance, you should use VAR.S for data that only contains numerical values, VARA for data sets that contain a variety of other forms of data, and VAR to maintain compatibility with older spreadsheets.
To begin, open your Excel spreadsheet and select an empty cell. Select the formula bar and begin to type your VAR, VAR.S, or VARA function. For example:
=VAR(=VAR.S(=VARA(
After the opening parenthesis, type the cell range containing your sample subset (eg. B2:B10 for cells between B2 and B10) or select it manually using your mouse. For example:
=VAR(B2:B10)=VAR.S(B2:B10)=VARA(B2:B10)
Once the formula is complete, close the formula with a closing parenthesis, and press the Enter key.
The value returned (in this example, 11.11) is the sample variance. You can increase the range of values used, which may improve the accuracy of the result, depending on how large your sample is compared to your full data set.
How to Calculate Population Variance in Excel using VARP, VAR.P, or VARPA
Excel calculates sample and population variance differently. For the user, however, there isn’t much difference between the functions you use to calculate sample and population variance in your spreadsheet. The only difference (for you) is the size of the data set you’re referring to and the functions you’ll use.
Make sure to choose VAR.P to calculate population variance with a data set that only contains numbers. Alternatively, use VARPA for a data set that contains numbers, text, and logical operators, or VARP to maintain compatibility in an older spreadsheet.
To start, open your Excel spreadsheet and select an empty cell. From the formula bar, begin to type your formula using VARP, VAR.P, or VARPA:
=VARP(=VAR.P(=VARPA(
After the opening parenthesis, enter the cell range that contains your full data set (eg. B2:B21 for cells between B2 and B21). Alternatively, use your mouse to select the data manually. For example:
=VAR(B2:B21)=VAR.P(B2:B21)=VARPA(B2:B21)
When you’re ready, close the formula with a closing parenthesis and press the Enter key or select another cell to view the result.
The value returned is the population variance for the full data set.
