In Microsoft Excel, you can use the most common & basic formula directly to calculate Variance Percentage. Here Iâ€™m going to show how you can do this calculation. But letâ€™s first review what Variance Percentage is & how it works. Following we will learn how to calculate variance percentage in Excel with 3 Easy Methods.

**Watch Video â€“ Calculate Variance Percentage in Excel**

## What Is Variance Percentage?

**Variance Percentage** indicates the percentage difference between a new value & an old value subject to the old value. It represents the percentage of change between two values.

**Formula(s) for Variance Percentage:**

**=(New Value â€“ Old Value) / Old Value * 100% **

or,

**=(New Value / Old Value-1) * 100%**

**Variance Percentage** is widely used in business accounting & economics. It can determine the percentage of profit & loss under a given dataset. To find the differences or changes in the values of temperature, product sales, budgetary estimates & expenses, this term needs to be mentioned. In Excel, you can find this variance percentage for a huge range of data only in minutes.

Letâ€™s think about a business company that wants to know the percentage variance between **Actual Sales** & **Estimated Sales** for **12 months** in the year **2021**. In **column E**, weâ€™re going to calculate this **percentage variance**.

## 1. Applying a Simplified Formula to Determine the Variance Percentage in Excel

As we described above, we will first apply the simplified formula to find the variance percentage. Letâ€™s see the process below.

- First, select
**cell E5**& type this formula.

`=(D5-C5)/C5`

- Then, press
**Enter**& youâ€™ll get the**variance**for**January**.

- Now, under the
**Home**ribbon, choose**Percentage**format from the drop-down in the**Number**group of commands.

- Finally, the value in
**cell E5**will convert into a percentage & show as a**Percentage Variance**. - Next, point your mouse cursor onto the bottom right corner of
**cell E5**, and youâ€™ll see a**Plus**(**+**) sign called**Fill Handle**. - Now, drag this
**Fill Handle**to**cell E16**& release the button.

- Thatâ€™s it, youâ€™ve just successfully determined all percentage variances for
**12 months**.

**Read More: **How to Calculate Percentage Change with Negative Numbers in Excel

## 2. Calculating Variance Percentage in Excel Using Alternative Formula

Now weâ€™ll use the same dataset but apply an alternative formula that is quite easier to type than the previous one.

- First, select
**cell E5**and type this formula.

`=D5/C5-1`

- Then, press
**Enter**. - Along with it, donâ€™t forget to convert the value into
**Percentage**format.

- Next, use
**Fill Handle**once again to fill down to**cell E16**like before. - Finally, youâ€™ll get the desired result as shown in the picture below.

## 3. Inserting Excel IFERROR Function to Calculate Variance Percentage

Letâ€™s think about a scenario where you have to divide the difference between **Actual** & **Estimated Sales** by **Zero** (**0**). Youâ€™ll see an error like **cell E11** in the following figure.

You can check what type of error it is by clicking on the **Error Checking** option attached to **cell E11**. Itâ€™s showing **Divide by Zero Error **here. So we have to correct this error now.

- First, type this formula in
**cell E5**.

`=IFERROR(D5/C5-1,0)`

- Now, press
**Enter**to get the same value as the percentage variance for**January**as before. - Next, fill down
**cell E5**to**cell E16**by using**Fill Handle**. - Finally, youâ€™ll find
**cell E11**without having any error message as youâ€™ve already fixed the formula considering this occurrence.

**the IFERROR function**is used to avoid the error

**#DIV/0**in the dataset. It is a beneficial function to trap and manage errors. Lastly, we typed

**0**in the formula to get an exact match.

## How to Calculate Variance Percentage for Negative Numbers in Excel

In some older Excel versions, you may find error messages while dividing with negative values. In that case, you have to use **the ABS Function** to enclose the divisor. This function turns a negative value into a positive one. Letâ€™s check the process below.

- First, select
**cell E5**and type this formula.

`=(D5-C5)/ABS(C5)`

- Then, press
**Enter**and you will see that it is showing the correct value despite having negative numbers.

- Lastly, use the
**Fill Handle**tool to get results in the**cell range E5:E16**.

**ABS**function is used to return the absolute value of percentage variance between the

**cells C5**and

**D5**.

**Note:**The

**ABS**function will show misleading results if one of the values in your dataset is positive and another one is negative.

**Read More: **How to Put Negative Percentage Inside Brackets in Excel

You can download the worksheet to practice yourself that weâ€™ve used to prepare this article.

## Conclusion

So, these are all basic & common techniques on how to calculate variance percentage in Excel. I have tried my best to make the instructions as easy as possible. I hope this article has guided you with proper & convenient instructions. If you have any questions, suggestions, or feedback regarding this article then youâ€™re welcome to comment here.

**Related Articles**

- How to Calculate Percentage for Multiple Rows in Excel

- How to Calculate Percentage of Filled Cells in Excel
- How to Calculate Percentage above Average in Excel
- How to Apply Percentage Formula in Excel for Marksheet
- How to Apply Percentage Formula for Multiple Cells in Excel
- IF Percentage Formula in Excel
- How to Calculate Contribution Percentage with Formula in Excel
- How to Use Food Cost Percentage Formula in Excel
- Calculate Percentage Using Absolute Cell Reference in Excel
- How to Find the Percentage of Two Numbers in Excel
- Calculate Percentage in Excel VBA

**<<Go Back to Calculating Percentages in Excel | How to Calculate in Excel | Learn Excel**