How To Calculate Variance In Microsoft Excel

How To Calculate Variance In Microsoft Excel

You can trust PC GuideOur team of experts use a combination of independent consumer research, in-depth testing where appropriate – which will be flagged as such, and market analysis when recommending products, software and services. Find out how we test here.

Last Updated on

Variance is a useful analytical tool when it comes to measuring data, and helps you to calculate the range of numbers in a set of data. This is handy for a multitude of reasons, from calculating probability to assessing the spread of data values.

Luckily, Microsoft Excel has a handy function designed to calculate the variance of a set of numbers automatically, so you can organize your data in no time! But how exactly do you do it?

If you want to calculate the variance of a set of numbers but you don’t know where to start, don’t worry! This article has got you covered.

In this easy-to-follow guide, we’ll walk you through the process of calculating variance in Microsoft Excel. So read on, and learn all there is to know about how to calculate variance in Excel!

Calculating Variance In Excel: 3 Easy Steps

Step

1

Input Your Values

Start off by opening a fresh Excel spreadsheet. Don’t worry about using any templates – just a basic blank workbook is fine.

From here, you can start inputting the values in your data set. These need to be kept in a singular range of cells – this means they must be limited to a single column or row.

For example, if you want to calculate the variance of 25 numbers, you would input one value each into cells A1 through A25.

The numbers in your list don’t need to be in any particular order, as Excel will sort them automatically while calculating the variance of your data set.

Step

2

Use The Variance Function

Now you need to use Excel’s variance function. To do this, you need to input a specific formula, which is different depending on how many numbers you’re calculating the variance of as well as the type of data you’re analyzing.

There are two different formulas that you can use depending on whether your data set is representative of the whole population or if it is a sample of a larger population.

Here’s what you need to input in either case, sticking with the same 25-number example.

If you’re calculating the variance for an entire population, enter this formula exactly into cell B1 (minus the quotation marks): “=VAR.P(A1:A25)”.

If the data is a sample of a population, enter this formula exactly into B1 (again, not including the quotation marks): “=VAR.S(A1:A25)”.

Step

3

Calculate The Variance Of Your Numbers

Once you’ve input the formula into cell B1, press the Enter key on your keyboard.

The variance of your data set will then appear in cell C1, while the formula will be replaced with one of two titles (depending on which formula you used); this will either display “Variance for Population” or “Variance for Sample”.

And there you have it – it’s as simple as that!

If you want to change any of the numbers in your data set or add more numbers, then this is easy to do.

Simply add/change the numbers in the list, and adjust the formula to reflect this. Then, press the Enter key again to calculate your new variance!

Conclusion

Microsoft Excel is full of handy tools to help you manage and keep track of your data, and the variance tool is a great example of how to use Excel to your advantage.

Calculating variance is easy in Microsoft Excel, and you can do it in just a few clicks once you know what you’re doing. So next time you’re trying to calculate variance in Excel, follow the steps in this handy guide and you’ll be able to analyze your data in no time!

Kevin is the Editor of PC Guide. He has a broad interest and enthusiasm for consumer electronics, PCs and all things consumer tech - and more than 15 years experience in tech journalism.