What Is the Variance Percentage?
The Variance Percentage indicates the percentage of change between two values.
=(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.
How to Calculate the Variance Percentage for Negative Numbers in Excel
- 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.
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
Get FREE Advanced Excel Exercises with Solutions!
