How to Calculate Percentage Variance Between Two Numbers in Excel

Percentage variance, change, or difference, measures the change between two values revealing the percentage increase or decrease from an initial value to a final one. It helps to make strategic decisions in various fields. In finance, it aids the analysis of investment returns, budget adherence, and financial performance, while in project management, it supports resource allocation and project progress evaluation.

In this Excel tutorial, you will explore 2 ways to calculate percentage variance between two numbers in Excel: Combining Simple Formula and the ABS Function, and using an Arithmetic Formula. You will also learn how to tackle possible errors while calculating percentage change.

The following overview image illustrates the calculation of percentage variance between values in Estimated Revenue and Actual Revenue columns. The Variance column shows the percentage change for each year.

2 Ways to Calculate Percentage Variance Between Two Numbers in Excel

Percentage variance measures the percentage increase or decrease between two values. The generic formula to calculate percentage change is as below:

Let’s say you started off with $500 in your savings account and it has increased to $650 in a few months. Using the formula: (650−500/500)×100 = 30%. Therefore, your savings increased by 30%.

Now, let’s learn how to calculate percentage variance between two numbers in Excel using the methods below.

Combining Simple Formula & ABS Function

In this section, a simple formula and the ABS function will be combined to calculate percentage variance which can be used for both positive and negative values. To calculate percentage change between two numbers using simple formula and the ABS function, follow these steps:

1. Select the output column > Home tab > Number group > Number Format drop-down > Percentage.
   
2. Select a blank cell.
3. Insert the formula: =(D5-C5)/ABS(C5)
   Replace C5 and D5 with your initial value and final value respectively.
4. Press Enter.
   Note: If you want the absolute value of percentage variance, use: =ABS((D5-C5)/C5)
5. Drag the Fill Handle down the column.
   Thus, the column will be filled with percentage variance accordingly.
   Note: You can change the number of decimal places from: Home tab > Number group > Decrease Decimal/Increase Decimal option.

Using Arithmetic Formula

This arithmetic formula has been derived from the previous formula, however, this formula can only be used for positive values. Using this formula for negative values may produce inaccurate results. The arithmetic formula can be used by following these steps:

1. Select the output column > Home tab > Number group > Number Format drop-down > Percentage.
2. Select a blank cell.
3. Insert the formula: =(D5/C5)-1
   Replace C5 and D5 with your initial value and final value respectively.
4. Press Enter.
5. Use the Fill Handle to copy formula down the column.
   Finally, the percentage changes will be calculated accordingly.
   

Handling #DIV/0! Error While Calculating Percentage Variance Between Two Numbers in Excel

A division process is involved in calculating the percentage change between two values. If the denominator is 0, Excel will show a #DIV/0! error. The nested IF function along with the IFERROR function is used to tackle this error. Here’s how:

1. Select your output cell.
2. Insert the formula: =IF(D5<>0, IFERROR((D5-C5)/C5, 1), IFERROR((D5-C5)/C5, 0))
   Replace C5 and D5 with your initial value and final value respectively.
3. Press Enter.
   Thus, the error will be tackled to show the percentage change.
   Note: When both the values are 0, the percentage change will be considered 0%. If the initial value is 0 but the final value is not, the percentage change will be considered 100%.

Download Practice Workbook

You can download the Excel file from the following link and practice along.

Conclusion

In conclusion, you can improve your data analysis abilities by learning how to compute percentage variance between two numbers in Excel. This article discusses two methods: one combines a basic formula and the ABS function, while the other employs an arithmetic formula. Additionally, the article addresses the issue of handling #DIV/0! error during percentage variance calculations. The ability to calculate percentage change gives you a valuable tool for well-informed decision-making. If you have any questions, feel free to leave a comment down below.

Frequently Asked Questions

How do you change a set of values to percent in Excel?

Select the range of values > Home tab > Number group > Percent Style. Alternatively, you can use the shortcut: Ctrl + Shift + %

Why do we calculate percentage variance?

We calculate percentage variance to measure and comprehend the relative change between two values and get insight into trends, performance shifts, and the size of differences.

<< Go Back to Calculate Variance in Excel | Excel for Statistics | Learn Excel