How to Calculate Percentage Change in Excel (4 Examples)

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.

Example 1 – Calculating Percentage 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.
4. Select the formula output cell range.
5. Go to the Home tab > Number group > Percent Style.

Percentage change outputs will appear accordingly.

Example 2 – Calculating Percentage Decrease

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.
   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)

Example 3 – Calculating Percentage Change with Zero

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. 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.

Example 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

D7 is sales for Apple in February and C7 is sales in January.

The sales of Mango changed from $250 to -$325 and the percentage change shows -230.00%, while the sales of Cherry changed from $350 to -$325 and the percentage shows -192.86%, less than the Mango percentage. However, the Cherry incurred more loss than the Mango, so the percentage change should have been more than the Mango.

This is a misleading result. This method is not foolproof and results need to be checked for accuracy.

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.

The sales of Apple changed from -$300 to $300 and the percentage change shows 200.00%, while the sales of Strawberry changed from -$400 to $300 and the percentage shows 175.00%, less than the Apple percentage. In reality, the Strawberry made more profit than the Apple, so the percentage change should have been more than the Apple.

This is a misleading result. Check the results for accuracy 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.

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.
   You’ll get the new values.

Download Practice Workbook

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