In statistics, *Variance* gives the measurement of how the numbers of data are distributed around the *mean/expected* value, and measures the distribution by looking at all the data points. In this article, we will analyze different methods and functions for calculating *variance* in Excel.

## Introduction to Variance

Normally, variance is the difference between an expected and actual result. In statistics, the variance is calculated by dividing the square of the deviation about the mean by the number of the population. To calculate the deviation from the mean, the difference of each individual value from the arithmetic mean is taken, and then all the differences are summed up.

## Classification of Variance

*Variance* can be classified into *3* categories:

**Percentage Variance****Population Variance****Sample Variance**

In this tutorial, we will learn to calculate all of them using different methods.

## How to Calculate Variance in Excel: 3 Handy Approaches

We will use both the manual process and built-in Excel functions to calculate the variance.

### Method 1 – Using the ABS Function to Calculate Percentage Variance

In the first approach, we will calculate the percentage variance between two numbers in Excel using **the ABS function**. The main formula for determining *percentage variance* is:

`Variance = (New value-Original value)/Original value`

If the *original value* is greater than the *new value*, we will get a **negative** result. To avoid the *negative* result, we will use the function **ABS** in the formula, which makes the *negative* number *positive.* So the formula will become:

`Variance = ABS(New value-Original value)/Original value`

Suppose we have a dataset (**B4:E8**) that contains the *Expected & Actual Sales* for some months. We need to calculate the **% Variance** of those *Expected & Actual Sales*.

**Steps:**

- Select cell
**E5**. - To calculate the
*variance,*enter the following formula in cell**E5**:

`=ABS(C5-D5)/D5`

- Press
**Enter**.

- Double-click on the
**plus**(**+**) sign located at the bottom right corner of cell**E5**to**AutoFill**the rest of the cells (see screenshot).

Hence, we will get all the *variances* (**E5:E8**). In the following picture, we can see that the *variances* are *decimal* numbers, but we need a *percentage variance*.

- To get the variances in
**percentage**format, select range**E5:E8**> go to the**Home**tab >**Number**group > click on the ‘**%**’ symbol.

We have the *percentage variances *(**E5:E8**) like in the screenshot below.

**Read How to Calculate Mean Variance and Standard Deviation in ExcelMore:** How to Do Variance Analysis in Excel

### Method 2 – Calculating Population Variance in Excel

Now, we will calculate population variance in Excel. First we’ll apply *manual calculation* and then use built-in Excel functions. The two approaches are explained below.

#### 2.1 – Manual Calculation

Suppose we have a dataset containing the *Sales* values in some months (see the screenshot below). Now, we need to calculate the *population variance* of the **Sales** (**C5:C8**) in cell **C13**. We will use the **COUNT**, **AVERAGE** & **SUM** functions to do so.

The main formula for calculating population variance is:

Here,

**μ**is the arithmetic mean.**X**is the individual value.**N**is the size of the population.**σ**is the population variance.^{2}

First, we’ll calculate the *population variance* manually.

**Steps:**

- For easier calculation, make two helper columns for (
**X-μ**) and**(X-μ)**.^{2}

- To calculate the
*total number of data*, use the following formula in cell**C11**:

`=COUNT(C5:C8)`

- Press
**Enter**.

The resultant value in cell **C11** will be **N** for the above formula for the *population variance*.

- To calculate the
*arithmetic mean*for the individual values, enter the following formula in cell**C12**:

`=AVERAGE(C5:C8)`

- Press
**Enter**to return the result.

- To calculate the
*deviation about the mean*(**X-μ**), enter the formula below in cell**D5**:

`=C5-$C$12`

- Press
**Enter**to find the result.

- Drag the
**Fill Handle**to copy the formula down to cell**D8**.

- To square the
*deviation about the mean*(**X-μ**), enter the following formula in cell**E5**:

`=D5^2`

- Press
**Enter**. - Drag the
**Fill Handle**down to cell**E8**to copy the formula.

- Calculate the sum of the square of the deviation about the mean
**(X-μ)**using the formula below in cell^{2}**E9**:

`=SUM(E5:E8)`

- Press
**Enter**to get the sum.

Finally, we calculate the *population variance* by dividing the square of the deviation about the mean **(X-μ)**** ^{2}** by the total number of data (

**N**).

- To do so, enter the formula below in cell
**C13**:

`=E9/C11`

- Press
**Enter**to return the desired output.

#### 2.2 – Using Built-in Excel Functions

Assuming we have a dataset (**B4:C8**) containing the *Total Sales* in some months, we will use 3 built-in Excel functions to calculate the *population variance* of the sales: the** VARP** function, the** VAR.P** function & the** VARPA** function.

**VARP Function**

The **VARP** function is a built-in Excel function that can calculate the population variance at once. The steps for finding the *population variance* are below.

**Steps:**

- Select cell
**F5**. - To get the
*population variance*of the*Total Sales*, enter the following formula in cell**F5**:

`=VARP(C5:C8)`

- Press
**Enter**.

The desired *population variance* is returned in cell **F5**.

**VAR.P Function**

This function returns the variance from a whole population, and the logical and text values are ignored.

**Steps:**

- Select cell
**F6**. - Calculate the
*population variance*using the formula below:

`=VAR.P(C5:C8)`

- Press
**Enter**to see the result in cell**F6**.

**VARPA Function**

This function evaluates the logical and text values in the calculation.

**Steps:**

- Activate cell
**F7**. - To get the
*population variance*, enter the following formula in the selected cell (**F7**):

`=VARPA(C5:C8)`

- Press
**Enter**.

The final output is like in the picture below.

**Read More:** How to Calculate Coefficient of Variance in Excel

### Method 3. Determining Sample Variance in Excel

Now we’ll calculate the sample variance in Excel using two approaches: applying manual calculation and then calculation with built-in Excel functions.

#### 3.1 – Calculating Manually

For calculating the *sample variance* manually, the following formula is used:

Here,

**μ**is the arithmetic mean.**X**is the individual value.**N**is the size of the population.**σ**is the sample variance.^{2}

We will use the same dataset used for calculating the *population variance manually* in **Method 2.1**. There is only one difference between the previous formula (**population variance**) and this, namely that the sample formula has a denominator of **N-1 **where previously the denominator was **N**.

The whole calculation in Excel will be almost the same. The only difference will be dividing the square of the deviation about the mean **(X-μ)**** ^{2}** by

**(N-1)**instead of

**N**. So with the same values of data, the sample variance is always greater than the population variance. The following picture shows the result (in cell

**C13**) and the necessary data table for the calculation of sample variance.

In this case, we inserted the following formula in cell **C13**:

`=E9/(C11-1)`

**Read More:** How to Calculate Pooled Variance in Excel

#### 3.2 – Assigning Built-in Excel Functions

We will now calculate the* sample variance* using three built-in Excel functions: **the VAR function**, the** VAR.S** function and the** VARA** function. To illustrate this method, we will use the Excel dataset (**B4:C8**) below, containing the *Total Sales* of the corresponding months.

**VAR Function**

The **VAR** function is the earliest built-in function in Excel to evaluate variance from a sample. All Excel versions from *2000* through *2019* include this function.

**Steps:**

- Select cell
**F5**. - To calculate the
*sample variance*, enter the following formula:

`=VAR(C5:C8)`

- Press
**Enter**.

The result is returned in cell** F5**.

**VAR.S Function**

The **VAR.S** function is the updated version of the **VAR** function in Excel, and can also be applied to determine the *sample variance*.

**Steps:**

- Enter the formula below in cell
**F6**:

`=VAR.S(C5:C8)`

- Press
**Enter**.

The result is returned in cell **F6**.

**VARA Function**

This function calculates the *sample variance* from a list of numbers and logical text values.

**Steps:**

- To calculate the
*sample variance*, enter the following formula in cell**F7**:

`=VARA(C5:C8)`

- Press
**Enter**.

The *sample variance* is returned.

## Things to Remember

Before using the built-in Excel functions to evaluate *variance* in Excel, there are several factors to consider:

- For population data sets, the more recent function,
**VAR.P**, should be used instead of**VARP**. - Similarly, for calculating
*sample variance*,**VAR.S**should be used in the first case, even if**VAR**and**VAR.S**are functionally equivalent. - If you include a text string as a value in a
*variance*formula, refer to it in another cell.

**Download Practice Workbook**

## Calculate Variance in Excel: Knowledge Hub

- How to Calculate Mean Variance and Standard Deviation in Excel
- How to Find the Variance of a Probability Distribution in Excel
- How to Apply Variance Formula in Excel to Get Plus-Minus Results
- How to Calculate Semi Variance in Excel
- How to Calculate Budget Variance in Excel
- How to Calculate Variance of Stock Returns in Excel
- How to Do Price Volume Variance Analysis in Excel
- How to Calculate Schedule Variance Using Excel Formula
- Budget vs Actual Variance Formula in Excel
- How to Calculate Variance Inflation Factor in Excel
- How to Calculate Portfolio Variance in Excel
- How to Create Minimum Variance Portfolio in Excel

**<< Go Back to Excel for Statistics**

**| Learn Excel**

the formula you have for How to Calculate Percentage Variance in Excel is written Variance = ABS(new value-original value)/original value. But in the example is ABS(new value-original value)/new value. so what is it?

Hi Pete, you are right!

is the correct one.`Variance= ABS(new value-original value)/original value`

We are really grateful for your precious feedback. We have updated the Excel file and main content accordingly.

Best Regards.

-ExcelDemy team.