Calculating percentage change is an everyday undertaking when dealing with data in Excel. Proficiency in this fundamental skill empowers individuals to improve decision-making and enhance data analysis within the realm of Excel.

Fortunately, MS Excel offers an array of exceptional features and functions that facilitate this process, making it remarkably effortless. Hence, I shall elucidate the method of calculating percentage change in Excel, enabling users to discern the percentage increase or decrease that occurs over a specified time period.

This post will also show you how to use an Excel formula to calculate percent increase or decrease for both positive and negative numbers in Excel 2019/2016/365.

1. What is Percent Change?

Percent change offers us a easy-to-understand way to get the relative difference between two values or two periods.  This valuable information allows us to delve into data analysis with a deeper understanding of the scale of change involved.

The percent change formula serves as a valuable tool in quantifying the relative shift between two periods, expressed in terms of a percentage. This formula enables us to precisely measure variations in a wide range of scenarios.

You can determine variance between sales from the current year and the previous year. And you can also use percent change formula to measure the disparity between a forecasted temperature and the observed temperature. When comparing budgeted costs to actual expenditures, the percent change formula proves indispensable.

For example, In September, your earnings amounted to $5,000, and in October, they increased to $5,300, resulting in a difference of $300. You can use a percent change formula to get percentage as you need.

2. Excel Percent Change Formula

Normally, I think that there are two math formulas to get the percentage difference between two given numbers. The first is the classic percentage variance formula, which is commonly utilized to calculate the percent change.

See below formula:

=(new value - old value)/old value

• New value:  the value that you want to compare with the old value. For example, if you want to get the difference for sales results for two different years, and the new value would be the  sales value for the recent years, such as, this year or last year.
• Old value:  this is original value that you want to compare to new value.

This formula will subtract the old value from the new value to get difference result, divide the difference by the old value to get the final percent difference.

Another basic formula is as below:

=new value/old value -1

The new value will be divided by the old value, and then subtract 1 from the result of the division. Then you can multiple the result by 100 to get the percentage result.

3. Calculate Percentage Increase Between Two Values

Assuming you have a dataset as below, with old value in Cell A2 and new value in Cell B2.  To get the increase value, you can use the following formula:

=B2-A2

To calculate percentage increase between those two values (new value and old value), type the below formula in Cell C2, and press Enter key to apply it.

=(B2-A2)/A2

You may get the final result in decimal format, it may not be the format you expected. To present it in the appropriate percentage format, just follow these simple steps:

Step1: select cell C2 where contain the decimal value that you want to convert.

Step2: go to Home tab, and find the Number group, and click on the percentage icon.

Step3: now you would get the right format in Cell C2.

Note: if you want to show more decimal places for a more precise value or show fewer decimal places, you can use the Increase decimal or Decrease decimal button, all of these buttons are located in the Number group.

4. Calculate Percentage Decrease Between Two Values

Assuming you have a dataset as below, with the old value in Cell A2 and new value in Cell B2, where the new value is lower than the old value.

You can use the same formula (similar to the above percentage increase) to calculate a percentage decrease.

=(B2-A2)/A2

5. Calculate the Value After Percentage Increase or Decrease

Assuming you have a dataset represented below, containing a range of values in Column A and the corresponding percentage change values in Column B.

You can see Column A holds the original values, while Column B contains the percentage change values.

To calculate the final value after incorporating the percentage change values recorded in Column B, you can use the following formula:

You would obtain the desired results, it reflects the adjusted value after considering the percentage change mentioned in Column B.

=A2*B2 + A2

You need to press Enter key to apply this formula, and drag the AutoFill Hander down to other cells to apply this formula.

6. Calculate an Entire Column with Certain Percentage Value

Assuming you have a dataset represented below. In Column A, you have the original values, and  you want to calculate the entire column A with a certain percentage value, such as, 20%.

To achieve this, just follow these steps:

Step1: you need to create a new column adjacent to column A, which will place the calculated new values.

Step2: in Cell B2, enter the following formula:

=A2+A2*0.2

 or

=A2*(1+0.2)

This formula multiplies the original value in Cell A2 by 1.2 to increase it by 20%.

If you want to decrease the entire column A by 20%, you can use the following formula:

=A2*(1-0.2)

Step3: Then you need to click the AutoFill Hander in Cell B2 down to other cells to apply this formula.

The new column B will now display the calculated values that representing a 20% increase over the corresponding original values in Column A.

7. Percentage Change for Negative Numbers

when you are working with negative numbers in your dataset, whatever old value is negative or new value is negative or positive, you need to know the traditional percent change formula may get incorrect results. You can use the ABS function in combination with math formula to address this issue.

The ABS function can make sure the denominator in the formula becomes a positive number.

The below is a generic formula to calculate the percentage change with negative numbers:

=(new value - old value)/ABS(old value)

For example, the old value is in the Column A, and the new value is in the Column B, the real formula is as below:

=(B2-A2)/ABS(A2)

8. Percentage Change for Zero Value (#DIV/0 Error)

when you are working with zero numbers in your dataset, you should know a potential challenge that dividing a number by zero, and it can lead to a divide by zero error(#DIV/0!).

Assuming that old value is zero, and new value is 20, if you use the above formula:

=(20-0)/0

You absolutely know that it will cause the #DIV/0! error in Excel.  As it is not allow divide a number by zero in math.

To get the correct result, you can use the following formula:

=(20-0)/20

You can also use the IFERROR function to ignore the #DIV/0! error and get an expected result.

=IFERROR((B2-A2)/A2,1)

You can see that it will return the final result as 100%, and it should be a correct result.

9. Calculate the Absolute Value of a Percentage Change

By default, you should know the percentage variance formula will return a positive value for percent increase and a negative value for percent decrease. Sometimes, you may want to get the percentage change as an absolute value, disregarding its sign, you can use the ABS function to achieve this.

You just need wrap the percentage variance formula within the ABS function. It can make sure the final result is always positive, regardless of whether it represents an increase or decrease.

Let’s see the below generic formula:

=ABS((New value – Old value) / Old value)

Also see the real formula in our case:

=ABS((B2-A2)/A2)

Type this formula in Cell C2, and press Enter key to apply it.

You can use the above formula to quickly and easily calculate percent change for you any type of dataset.

Hope you can like this post.