# How to Calculate the Variance Percentage in Excel – 3 Methods

## 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.

## 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.

The 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.

Download Practice Workbook

Download the worksheet to practice.

## Related Articles

<<Go Back to Calculating Percentages in Excel

Get FREE Advanced Excel Exercises with Solutions!
Nehad Ulfat

NEHAD ULFAT is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET but switched gears, working as a content developer.Â  In this role, he creates techy content all about Excel... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF