## What Is the Variance Percentage?

**The Variance Percentage** indicates 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%**

In the dataset below, you want to know the percentage variance between **Actual Sales** & **Estimated Sales** for **12 months** in **2021**.

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

- Select
**E5**and enter this formula.

`=(D5-C5)/C5`

- Press
**Enter**.

Youâ€™ll see the **variance** for **January**.

- Go to the
**Home**tab and choose**Percentage**in**Number**.

**E5**will be converted into a percentage and show the**Percentage Variance**.- Drag down the Fill Handle to see the result in the rest of the cells.

- This is the output.

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

## Method 2 – Calculating the Variance Percentage in Excel Using an Alternative Formula

- Select
**E5**and enter this formula.

`=D5/C5-1`

- Press
**Enter**. - Convert the value into
**Percentage**.

- Drag down the Fill Handle to see the result in the rest of the cells.
- This is the output.

## Method 3 – Using the Excel IFERROR Function to Calculate the Variance Percentage

You have to divide the difference between **Actual** and **Estimated Sales** by **Zero** (**0**). Youâ€™ll see an error, as shown below.

Click **Error Checking**. It shows **Divide by Zero Error**. To correct this error.

- Select
**E5**and enter this formula.

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

- Press
**Enter**.

You will see the percentage variance for **January**.

- Drag down the Fill Handle to see the result in the rest of the cells.

**Â E11** won’t display an error message.

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

**#DIV/0**error.

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

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

Use **the ABS Function** to enclose the divisor. This function turns a negative value into a positive one.

- Select
**E5**and enter this formula.

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

- Press
**Enter**.

The correct value is displayed.

- Drag down the Fill Handle to see the result in the rest of the cells.

**ABS**function returns the absolute value of a percentage variance between

**C5**and

**D5**.

**Note:**The

**ABS**function will show misleading results if there are positive and negative values.

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

Download the worksheet to practice.

**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**