# How to Calculate Year over Year Percentage Change in Excel (with Advanced Technique)

Our Excel sample sheet is about revenue earned in each year from 2015 to 2020. There are two columns: Year, and Earning Amount. We will calculate the percentage changes year over year.

## Method 1 – Using the Conventional Way to Calculate Year over Year Percentage Change in Excel

For the basic way of calculation, we will use the formula below.

= (New Amount – Old Amount)/Old Amount
• Select a new cell D6 where you want to keep the result.
• Copy the formula given below in cell D6:
`=(C6-C5)/C5`

Since we cannot calculate the change for the first one—it’s the starting value—we started counting from the second one. All our calculations will be done by using cell references.

• Press Enter.

• To get the result in percentage format, go to the Number section on the Home tab and select Percentage.

• You will get the value in your desired format.

• You will get all the YoY (Year over Year) percentage changes. Negative values indicate the losses from last year.

## Method 2 – An Advanced Way to Calculate Year over Year Percentage Change

We can slightly modify the conventional formula so it becomes:

= (New Value / Old Value) – 1
• Select a new cell D6 where you want to keep the result.
• Copy the formula given below in cell D6:
`=(C6/C5)-1`
• Press Enter.

Here, 1 is the decimal equivalent of 100%. Now, when we are dividing two values, it gives us a decimal value. Eventually, every decimal value has an equivalent percentage value. So, it seems, we are subtracting two percentage values instead of decimal values.

• To get the result as a percentage, select Percentage in the Number group in the Home tab.

• Finally, you will get the value in your desired format.

• You can write a similar formula for the rest of the rows or use the Excel AutoFill feature.

## Method 3 – Performing a Cumulative Year over Year Percentage Change Calculation in Excel

The formula is as follows.

= (New value / Base Value) – 1

Now, let’s see how to do that.

• Select a new cell D6 to keep the result.
• Copy the following formula in cell D6.
`=(C6/\$C\$5)-1`
• Press Enter.
• To get the result as a percentage, select Percentage in the Number group in the Home tab.

Here, our base value is the amount earned in 2015. Our changes have been measured using that amount. We divided the amounts of each year by the amount of 2015 and subtracted 1 from the result. In this formula, we used the absolute cell reference to fix the vase value.

• Use the Excel AutoFill feature to AutoFill the corresponding data in the rest of the cells of range D7:D10.

## Method 4 – Using the Excel IFERROR Function to Calculate Year Over Year Percentage Change

Steps:

• Write down the revenue earned each year in column C. Include the known amount of last year in cell B5.
• Use the cell value of C5 in cell B6, which will be the last year’s amount. You can apply a formula for cells after the first.

• This should be the last year’s amount and the new year’s amount column.

• Select a new cell D5 where you want to keep the change amount.
• Use the formula given below in cell D5:
`=IFERROR(((C5/B5)-1),)`
• Press Enter.

Formula Breakdown

• Here, C5/B5—> In this formula, when we are dividing two values, it gives us a decimal value.
• Output: 2
• Then, we subtract 1 from the output.
• Output: 1
• Lastly, the IFERROR function will return the result which is valid. If the output has any error, then it will return a blank space.
• Output: 1

• Drag the Fill Handle icon to AutoFill the corresponding data in the rest of the cells of range D6:D10.

• Write the value of cell D5 value in cell E5.
• Format it as Percentage.

• Drag the Fill Handle icon to AutoFill the corresponding data in the rest of the cells of range E6:E10.

## Calculate Year over Year Percentage Increase in Excel

• Select cell D6 and copy the formula given below:
`=(C6-C5)/C5`
• Press Enter.
• Change the data format from Number to Percentage.

• Drag the Fill Handle icon to AutoFill the corresponding data in the rest of the cells of range D7:D10.

Finally, you get all the YoY (Year over Year) growth percentages with positive results.

## Calculate Year over Year Percentage Decrease in Excel

• Write the formula given below in cell D6:
`=(C6-C5)/C5`
• Press Enter.
• Change the Number format to Percentage.

• Drag the Fill Handle icon to AutoFill the corresponding data in the rest of the cells of range D7:D10.

## Useful Tips

You may need to increase or decrease the decimal places. You can do it in a simple way. In the Number section of the Home tab, you will find the Increase Decimal and Decrease Decimal options.

You can choose what you prefer to use. Here you can see, we use the Increase Decimal option.

As a result, you can see by increasing the decimal places the value has been updated. Excel will do the recalculation by itself.

## Bonus

You can use today’s practice workbook as a calculator. Insert any amounts in respective fields (column C, and cell B5) to calculate the changes.

<< Go Back to Percentage ChangeCalculating Percentages | Calculate in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Shakil Ahmed

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF