We can agree on the fact that calculating variance is a long process in general. Variance is a statistical measure of calculating the spread of a specified data range. Although you have to make a range of other calculations the old-school way, in Excel, you can find the variance of a range using Excel formulas.

In this article, we will be discussing the different ways you can calculate the variance in Excel. Keep reading to learn more about how you can use Excel to get better at calculations!

## Types of Variance

There are two different ways of calculating variance; by **sample **and **population**. The formulas vary according to if you want to calculate the variance for the entire population or only a sample of the entire population.

In Excel, two different functions are responsible for calculating variance according to population and sample. So, you need to determine if the range you’ve selected is a sample of the whole data or not.

## How to Find Variance in Excel?

You can use exactly four functions to calculate variance in Excel; **VAR.P**, **VAR.S**, **VARPA**, and **VARA**. Each function is quite different from the others and calculates variance differently. Below, we have elaborated on each function and mentioned the format you can follow to use the function in your formula.

### Use VAR.P Function

The **VAR.P** function calculates the variance of an entire population, ignoring the text. You can use this function when no data is missing in your sheets.

For instance, you may want to calculate variance in a classroom’s math scores. If you have access to every student’s math scores, use the **VAR.P **function.

Follow this format to use the **VAR.P** function in your formula:

**=VAR.P(range)**

### Use VAR.S Function

You need to use the VAR.S function to calculate a subset’s variance. Like the VAR.P function, this function also ignores texts in its arguments.

Using the same example above, you can use the **VAR.S** function if you’re calculating the variance in the top three high scores.

If you feel like the **VAR.S** function is the right function for you, refer to this format to use it in a formula:

**=VAR.S(range)**

### Use VARPA Function

If you have one or more data missing from your sheet, you can use the **VARPA **function. The **VARPA **function includes logic and texts while calculating the variance of a population.

If a student failed to turn in their math score, you could not calculate the true variance between all student’s scores. In that case, you can use the **VARPA **function to take the missing score into account.

Use this format to use the **VARPA **function in a formula:

**=VARPA(range)**

### Use VARA Function

The **VARA** function works similarly to the **VARPA** function. This formula also considers logic and texts from the range while calculating the variance of a sample from a population.

You can use this function if you have data missing from your sheets to calculate the true variance between the data set.

Refer to this format to use the **VARA** function in a formula:

**=VARA(range)**

### Manually Calculate Variance (Population)

If you do not want to use the functions to calculate variance, you may also manually calculate variance in Excel. Here are the steps you can refer to calculate the variance for a population in MS Excel manually:

- On an open workbook, enter the data in a row.
- On a difference row, you need to calculate an average of the data you’ve entered. Use the
**AVERAGE**function in this format to calculate the mean value:**=AVERAGE (range)**.

- Head to a different row again and subtract each entered data from the average.
**=cell1-cell2**

- Now, square the differences on another row:
**=cell1*cell1**

- To calculate the variance, add all the squared data and divide it by the number of data you entered:
**=SUM(range)/COUNT(range)**

### Manually Calculate Variance (Sample)

You can also manually calculate the sample variance of a data range in Excel. The steps are similar to calculating populating variance, but you must recalculate the mean, the difference between mean and data, and the squared difference according to your sampled data.

While calculating the variance, too, you must subtract the total number of sampled data to remove the bias. Follow these steps to calculate sample variance in Excel:

- In your workbook, enter the sampled data in a row.
- Calculate their average with this formula:
**AVERAGE(range)**. - Subtract each data from the calculated average:
**cell1-cell2**. - Square the calculated difference:
**cell1*cell1**. - To calculate the sampled variance, add the squared differences and divide it by the number of data used minus 1. Follow this format:
**=SUM(range)/(COUNT(range)-1)**

## Other Functions to Calculate Variance

Older versions of Excel support the other variance functions, such as the VARP function and VAR function.

**VARP function:**It is used to calculate the variance of a population.**VAR function:**It calculates the variance in a population sample.

These functions have, however, been replaced by the VAR.P and VAR.S functions in the newer versions of Excel. So, until you’re using Excel version 2007 or below, we do not recommend you to use these functions.

Formulas with VAR and VARP functions are written in the same way as the VAR.S and VAR.P functions.