How to Calculate Variance in Excel
(Step-by-Step)

Variance (as it comes from the word varies) tells how much the values in a dataset vary from the central point of that data.

Excel offers a variety of statistical functions to calculate variance in Excel. In the tutorial below, we are going to cover all these functions (when and how to use them).

Plus, we will also see how you use variance and related statistical measures in practical life. So get your hands on the sample workbook for this tutorial here and continue reading 📚

Table of Contents

What is variance (central tendency and data spread)

Variance is the statistical measure of variability (or spread) within a data set. It explains how far the values of the dataset lie from the mean of that dataset.

Let’s say we have a group of 6 people with varying heights.

The mean of this dataset is 173.99 cm (which we also call the central tendency).

But how different or dispersed are these heights from this mean? Variance helps us know this.

The variance for this dataset is 138.71. This is a general squared measure of the spread in this dataset. Taking the square root of 138.71, we get the standard deviation of 11.77.

This tells that the heights of people in the city are 173.99 cm + or – 11.77.

The formula of Variance is as follows 📝

Within this,

• X represents each value from the dataset
• x̄ is the mean of the dataset
• ∑ denotes the sum of (x- x̄)^2
• N is the number of values in the dataset

Variance sums up the squared difference between each value of a dataset and the mean of that dataset. Then divide it by the number of values of the dataset.

More discussion on this formula follows in the tutorial but, this gives you a broad idea of how the variance formula works.

Sample Vs. Population

The kind of dataset that you’re using to calculate variance in Excel makes an important difference.

That’s because the formula of variance for a sample dataset and an entire population differs as follows:

The formula of Variance for a sample:

The formula of Variance for the entire population:

Though slight, there does exist a difference between both formulas in terms of the denominator. This difference is important since you do not always use a population to calculate variance.

For example, if you want to calculate the variance of the ages of people residing in a city. It might not be possible for you to seek a record of every individual living within that city.

For such datasets, we draw a sample from the entire population, and whatever results are achieved based on the sample, are then projected to the entire population 💡

So, knowing your data type is important to calculate the correct variance for it.

Variance functions of Excel

In total, Excel offers 6 different functions to calculate variance. Three of these functions calculate the variance for a sample dataset, and three calculate it for the entire population.

Pro Tip!

The VAR.S and VAR.P functions are upgraded versions of the old VAR and VARP functions, respectively.

Although the VAR and VARP functions are still available in Excel for backward compatibility, the VAR.S and VAR.P functions are more powerful and offer better precision.

If you’re using Excel 2010 and later versions, it is advisable to use the VAR.S and VAR.P functions.

Talking about sample variance, the main difference between the VAR.S and VARA functions is how they treat non-numeric values.

If your dataset has any non-numeric values, like the one below:

Very clearly, the variance for this dataset using VAR.S and VARA will be different.

That’s because VARA ignores the non-numeric values and takes the “n” for variance calculation as 7 (whereas the number of values in the given dataset is 10).

Whereas the VARA function treats ABC (the text value) as 0, TRUE (the Boolean value) as 1, and FALSE (the Boolean value) as 0. So, the “n” for VARA remains 10.

However, if we remove the non-numeric values from this dataset, the results for both these functions will be just the same.

The same science follows for VAR.P and VARPA.

P.S: All these functions ignore any empty cells within the given dataset, if any.

To know the right Excel formula to be used for your dataset, ask yourself the following questions:

• Which Excel version do you have? 2010 or later? This will help you know if you should go for:

                VAR or VAR.S

                VARP or VAR.P

• Are you running a sample data set or the entire population?
• Do you want the non-numeric values of your dataset to be considered in the calculation or not?

How to calculate variance in Excel

Coming to the juicy part – how to calculate variance in Excel. Let me take you through some comprehensive examples to explain to you how.

Sample

The following dataset is a sample of students selected from a school along with their weights 👨‍🏫

Since we are working with a sample population and there are no non-numeric values in our data, guess we can safely use the VAR.S function.

Step 1) Begin writing the VAR.S function.

Step 2) Refer to the cell range that contains the dataset.

Step 3) Press Enter.

The variance for this dataset is 99.07. There’s quite some dispersion in this data which comes off clear from the fact that some students are as slim as only 22 kgs and some are as obese as 50 kgs.

Population

Okay, now let’s see how to calculate variance in Excel for a population.

So, the date below represents all the items on the menu card of a restaurant along with their prices 🍜

We see some items on the list are complimentary (which means we have text values in the dataset, too).

Now, I want to include these text values in our variance calculation since they make a part of the menu. So, I will be using the VARPA function instead of the VAR.P function.

Step 1) Begin writing the VARPA function.

Step 2) Refer to the cell range that contains the dataset.

Step 3) Press Enter.

The variance for the prices of the entire menu list for this restaurant is $49.69. And within this number of variance, the two text values (Complimentary) are included.

Hope this shows you how easy is it to calculate variance in Excel.

Double-check the variance for accuracy

Good to trust spreadsheet software with numbers – but it’s even better to know the math that runs behind them.

In this section, I will quickly show you how can you manually calculate variance in Excel, and as a by-product, we will also check if Excel calculated the correct variance in the example above.

To calculate variance in Excel for a population.

Step 1) Calculate the mean of the dataset by using the AVERAGEA function as follows:

Step 2) Now we will subtract the mean from each value of our dataset below.

Step 3) For the cells that contain text values, I have manually rewritten the above formula as :

This is because had I dragged and dropped the above formula through it, Excel would have given a #VALUE! Error as it cannot deduct a numeric value from a non-numeric value.

We have used zero, as the VARPA function treats text values as zero.

Step 4) Next, take the square of each of these values as below.

Step 5) Take the sum of the squared values using the SUM function as below.

Step 6) Now divide the above sum by 10.

And look at the answer – precisely equal to the variance calculated by the VARPA function of Excel.

With this, you know how a single variance function of Excel saves you so many steps to calculate variance 🥂

Variance vs. Standard Deviation

In the above example, we have calculated the variance as 49.69 – cool.

But how do you interpret this number? The variance shows how far spread are the values within a dataset. However, simply saying 49.69 won’t convey the meaning.

To make meaning out of variance, we need to calculate the standard deviation.

Square Root the variance

We’ve already calculated the variance. Now:

Step 1) Find the Square root of the variance using the SQRT function as follows:

The standard deviation comes out as 7.05.

Use the standard deviation function

Another way to calculate the standard deviation is using the STDEVPA function.

Step 1) Write the STDEVPA function.

Step 2) Refer to the range of cells containing the dataset.

The standard deviation again comes out as 7.05.

Now, to interpret the results, the standard deviation of 7.05 tells that the majority of data points within this dataset lie at a diversion of 7.05 points from its mean of 12.54.

In other words, the majority of the items on this menu are priced between 5.49 (12.54 – 7.05) and 19.59 (12.54+7.05).

The standard deviation gives us a deviation range of 5.49 – 19.59 within which most of the values of the dataset lie 💪

Tips for using the Variance functions in Excel

The tutorial above covers almost all that you need to know to calculate variance in Microsoft Excel (for a sample and population). Moving forward, note the following important pointers too:

• If you see the variance functions giving back the #DIV/0! The error is probably because you have supplied too few values to it.

Make sure to give at least one numeric value to VARP, VAR.P, and VARPA functions. And at least two numeric values to VAR, VAR.S, and VARA functions.

• Be mindful of the items in your dataset. Use the relevant function based on whether your dataset contains any text or logical values.

Conclusion

The concept of variance is an important statistical one that you will see applied in many instances around you. A good understanding of how it works and how is it calculated can prove super useful.

In addition to the above tutorial, my following blogs explain other key concepts that orbit around variance and related concepts.

• How to calculate a correlation coefficient in Excel
• How to Calculate Standard Deviation in Excel
• How to Calculate a Confidence Interval in Excel

These blogs will give you a detailed understanding of many statistical concepts. We hope you enjoy reading them as much as I did writing them.