How to Calculate Percentage Change in Excel (4 Examples)

Get FREE Advanced Excel Exercises with Solutions!

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 in Excel overview

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.

sales increase

To calculate the percentage increase:

  1. Select a blank cell.
  2. Apply the formula: =(D7-C7)/C7
    D7 is the sales for Apple in February and C7 is sales in January.
  3. Use AutoFill to copy the formula to the cells below.percentage change formula
  4. Now select the formula output cell range.
  5. Go to the Home tab > Number group > Percent Style.select % format

Percentage change outputs will appear accordingly.

percent change output

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:

  1. Select a blank cell.
  2. Apply the formula: =(D7-C7)/C7
    Here, D7 is the sales for Apple in February and C7 is the sales in January.
  3. Use AutoFill to copy the formula to the cells below.
  4. 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.percentage change formula decrease

If you want the percentage change value only, ignoring the negative sign, use the formula: =ABS((D7-C7)/C7)

absolute formula

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:

  1. Select a blank cell.
  2. Apply the formula: =IFERROR((D7 - C7) / C7, 1)
    D7 is sales for Apple in February and C7 is sales in January.
  3. Use AutoFill to copy the formula to the cells below.
  4. 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.percentage change with zero

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.

percentage change both values negative

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.

percentage change when new value negative

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.

only old value negative

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.

price increased by percent change

To calculate value after percentage change in Excel:

  1. Select a blank cell.
  2. Apply the formula: =C7*(1+D7)
    D7 is the percentage change and C7 is the old price for Apple.
  3. Use the AutoFill to copy the formula to the cells below.
    In this way, you’ll get the new values.new price

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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Aung Shine
Aung Shine

Aung Shine completed his bachelor’s in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. It has been almost 2 years since he joined SOFTEKO and actively working on the ExcelDemy project. Currently he works as a Team Leader where he guides his team members to create technical content. He has published 150+ articles and reviewed 50+ articles. He has also solved various user problems before. He has interests in Data Analysis, Power Query, Advanced Excel,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo