Percentage change determines the relative difference between the two values and the difference is expressed as a percentage. From this calculation, one can easily tell if the final value has increased or decreased from the initial or old value. This is helpful in trend analysis between two periods of time and many more.
In this Excel tutorial, you’ll learn how to calculate percentage change in Excel.
The following dataset contains some products (B5:B14) and their sales in January (C5:C14) and February (D5:D14). We have calculated the percentage change between these two months (E5:E14) using a simple formula.
Percentage Change Formula in Excel
Percentage change is usually calculated between two values, where one value is the old or initial one and the other is the new or final value. There is one general formula to calculate the percentage change:
((New value - old value) / old value) * 100%
However, you don’t need to multiply by 100% in Excel, as you can use the Percentage number format.
Another simpler version of the formula is:
(New value / old value) - 1
Using any of these, you can calculate percentage increase or decrease in Excel.
4 Examples to Calculate Percentage Change in Excel
In this article, you’ll learn about different scenarios in the percentage change calculation.
Here are the 4 examples to calculate percentage change in Excel:
1. Calculating Percentage Increase
In the below dataset, we have created a scenario where all the February sales are higher than the January sales. So there will be an increase in the percentage change.
To calculate the percentage increase:
- Select a blank cell.
- Apply the formula:
=(D7-C7)/C7
D7 is the sales for Apple in February and C7 is sales in January. - Use AutoFill to copy the formula to the cells below.
- Now select the formula output cell range.
- Go to the Home tab > Number group > Percent Style.
Percentage change outputs will appear accordingly.
2. Calculating Percentage Decrease
If the new values are lower than the old values, you can still use the same formula as before.
To calculate the percentage decrease:
- Select a blank cell.
- Apply the formula:
=(D7-C7)/C7
Here, D7 is the sales for Apple in February and C7 is the sales in January. - Use AutoFill to copy the formula to the cells below.
- Apply the Percent Style to the formula output cell range.
In this way, the percentage decrease will be calculated. The negative sign correctly represents the decrease.
If you want the percentage change value only, ignoring the negative sign, use the formula: =ABS((D7-C7)/C7)
3. Calculating Percentage Change with Zero
There are possibilities where any of the two values is zero. There will be no problem if the new value is zero. But the #DIV/0! error will occur if the old value is zero. You can easily solve the problem using the IFERROR function.
To calculate the percentage change with zero:
- Select a blank cell.
- Apply the formula:
=IFERROR((D7 - C7) / C7, 1)
D7 is sales for Apple in February and C7 is sales in January. - Use AutoFill to copy the formula to the cells below.
- Now select the formula output cell range and apply the Percent Style from the Home tab.
The accurate percentage change outputs will appear without any errors.
4. Calculating Percentage Change with Negative Values
There are 3 scenarios for calculating percentage change where negative values are present in the old or/and new values. The formula will be changed according to the scenario.
The 3 scenarios for calculating percentage change with negative numbers:
4.1 Both Values Are Negative
Use the ABS function in the formula to get the correct percentage change output if both the old and new values are negative. The formula is: =(D7-C7)/ABS(C7)
D7 is sales for Apple in February and C7 is sales in January.
4.2 Only New Value is Negative
If only the new value is negative, the formula doesn’t need any change. The formula will be the basic formula: =(D7-C7)/C7
Here D7 is sales for Apple in February and C7 is sales in January.
However, if you look closely, the sales of Mango changed from $250 to -$325 and the percentage change shows -230.00%. Whereas, the sales of Cherry changed from $350 to -$325 and the percentage shows -192.86%, less than the Mango percentage. But in reality, the Cherry incurred more loss than the Mango, so the percentage change should have been more than the Mango.
This is a misleading result. Therefore, it’s important to take notice when using this method.
4.3 Only Old Value Is Negative
If only the old value is negative, use the ABS function in the formula to get the correct result. The formula is: =(D7-C7)/ABS(C7)
D7 is sales for Apple in February and C7 is sales in January.
However, if you look closely, the sales of Apple changed from -$300 to $300 and the percentage change shows 200.00%. Whereas, the sales of Strawberry changed from -$400 to $300 and the percentage shows 175.00%, less than the Apple percentage. But in reality, the Strawberry incurred more profit than the Apple, so the percentage change should have been more than the Apple.
This is a misleading result. Therefore, it’s important to take notice when using this method.
How to Calculate Value after Percentage Change in Excel
There are instances of percentage change to the original or old value. In the following dataset, the old price and the percentage change of some products are given. Here, you’ll learn to find out the new price.
To calculate value after percentage change in Excel:
- Select a blank cell.
- Apply the formula:
=C7*(1+D7)
D7 is the percentage change and C7 is the old price for Apple. - Use the AutoFill to copy the formula to the cells below.
In this way, you’ll get the new values.
Download Practice Workbook
Conclusion
This article has shown how to calculate percentage change in Excel. The examples included calculating the percentage increase, decrease, percentage change with zero, and also negative values. Leave a comment for further queries.
Frequently Asked Questions
1. How can I calculate the discount percentage?
You can easily calculate the discount percentage if you have the regular price and the discounted price. The formula is: =(discounted price - regular price) / regular price
As the discounted price is smaller than the regular price, the formula output will be negative. To get the positive number, use the ABS function. The formula will be: =ABS((discounted price - regular price) / regular price)
2. Is there a built-in function for calculating percentage change in Excel?
No, there is no built-in function in Excel to calculate the percentage change between two values. You have to use a formula to get the percentage change.
Percentage Change in Excel: Knowledge Hub
<< Go Back to Calculating Percentages | Calculate in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
