How to Calculate Year over Year Percentage Change in Excel

Need to calculate year over year percentage change for your company? You can do that easily with Microsoft Excel. In today’s session, we will show you how to calculate Year over Year percentage change in Excel. For conducting the session, we’re going to use Microsoft 365 version. You can choose your preferred version. Now without further ado let’s begin today’s session.


Download Practice Workbook

Here, we have shared the Excel sheet. So, you can download it from the link below.


4 Ways to Calculate Year over Year Percentage Change in Excel

Today we will see how to calculate Year over Year change percentage in Excel. Furthermore, we will see how to do that in both the conventional way and the advanced way as well. Now, let’s dive into the session.

But, before diving into the big picture, let’s get to know about today’s Excel sheet first.

The Excel sheet is about revenue earned each year from 2015 to 2020. There are two columns, Year, and Earning Amount. Now, we will calculate the percentage changes year over year.

Dataset for How to Calculate Year over Year Percentage Change in Excel


1. Conventional Way to Calculate Year over Year Percentage Change

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

= (New Amount – Old Amount)/Old Amount

Actually, we use this formula for any kind of percentage changes or to find out the change rate.

  • Firstly, you have to select a new cell D6 where you want to keep the result.
  • Secondly, let’s write the formula given below in the D6 cell on the Excel sheet.
=(C6-C5)/C5

Conventional Way to Calculate Year over Year Percentage Change in Excel

It’s obvious that we cannot calculate the change for the first one, as there is nothing before that. Thus, we started counting from the second one. Here, we subtracted the amount of 2015 from the amount earned in 2016 and divided the results by the amount of 2015. Additionally, all our calculations will be done by using Cell Reference.

  • Subsequently, press ENTER.

  • At this time, for getting the result in percentage format, explore the Number section on the Home tab >> then select Percentage.

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

Lastly, you will get all the YoY (Year over Year) percentage changes. Here, you are seeing some negative values, that happened because for every year you may not earn the amount higher than the previous year. These negative values indicate the losses from last year.

Result for using Conventional Way to Calculate Year over Year Percentage Change in Excel


2. Advanced Way to Calculate Year over Year Percentage Change

Now let’s see an advanced formula to calculate Year over Year percentage change. The formula is as follows.

= (New Value / Old Value) – 1

Basically, we use this formula for any kind of percentage changes or to find out the change rate.

  • Firstly, you have to select a new cell D6 where you want to keep the result.
  • Secondly, let’s write the formula given below in the D6 cell on the Excel sheet.
=(C6/C5)-1
  • Thirdly, press ENTER.

Advanced Way to Calculate Year over Year Percentage Change in Excel

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.

  • At this time, for getting the result in percentage format, explore the Number section on the Home tab >> then select Percentage.

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

  • Now you can write the formula for the rest of the rows or exercise the Excel AutoFill feature.

Lastly, you will get all the YoY (Year over Year) percentage changes.


3. Cumulative Year over Year Percentage Change Calculation

Rather than calculating changes year by year, you may need to see the changes over a certain period of time.

When you are Calculating Cumulative Changes, you need to have a common base value. Basically, you need to calculate the changes using that base value. The formula is as follows.

= (New value / Base Value) – 1

Now, let’s see how to do that.

  • Firstly, you have to select a new cell D6 where you want to keep the result.
  • Secondly, let’s write the formula given below in the D6 cell.
=(C6/$C$5)-1
  • Thirdly, press ENTER.
  • Lastly, for getting the result in percentage format, explore the Number section on the Home tab and select Percentage.

Cumulative Year over Year Percentage Change Calculation in Excel

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. While doing it by the formula in Excel, we used the Absolute Cell Reference of the cell containing the amount of 2015.

  • Now, use the Excel AutoFill feature to AutoFill the corresponding data in the rest of the cells D7:D10. .

Lastly, you will get all the YoY (Year over Year) percentage changes.


4. Use of IFERROR Function

You can apply the IFERROR function to calculate Year over Year change percentage in Excel. Let’s do something different. Here, we will rewrite the dataset in a different way. Furthermore, in this method, we will find the changes first then we will find out the percentages. The steps are given below.

Steps:

  • Firstly, write down the revenue earned each year in the C column. Also, include the known amount of last year in the B5 cell.
  • Secondly, you should use the cell value of C5 in the B6 cell. Which will be the last year’s amount.

Use of IFERROR Function to Calculate Year over Year Percentage Change in Excel

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

  • Now, you have to select a new cell D5 where you want to keep the change amount.
  • Then, you should use the formula given below in the D5 cell.
=IFERROR(((C5/B5)-1),)
  • After that, 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.

  • Now, you can drag the Fill Handle icon to AutoFill the corresponding data in the rest of the cells D6:D10.

Lastly, you will get all the changes.

  • Again, write the D5 cell value in the E5 cell.
  • Then, format it as Percentage.

  • After that, you can drag the Fill Handle icon to AutoFill the corresponding data in the rest of the cells E6:E10.

Finally, you will get all the YoY (Year over Year) percentage changes.


Calculate Year over Year Percentage Increase in Excel

In this section, we will see the calculation of year over year percentage incremental change in Excel. Actually, when the previous year’s amount is less than the present year’s amount then it will be an incremental change. Or, you can say the company made a profit.

Now, let’s talk about the steps. Here, we’re going to use the Conventional way for the calculation.

  • Firstly, you have to select a new cell D6 where you want to keep the result.
  • Secondly, let’s write the formula given below in the D6 cell on the Excel sheet.
=(C6-C5)/C5
  • Thirdly, press ENTER.
  • Subsequently, change the Number format to Percentage.

Calculate Year over Year Percentage Increase in Excel

  • After that, you can drag the Fill Handle icon to AutoFill the corresponding data in the rest of the cells D7:D10.

Finally, you will get all the YoY (Year over Year) growth percentage. Which are positive in the result.


Calculate Year over Year Percentage Decrease in Excel

Now, we will see the calculation of year over year percentage decremental change in Excel. Basically, when the previous year amount is more than the present year amount then it will be a negative change. Or, you can say the company made a loss. Similarly, we’re going to use the Conventional way for the calculation.

Now, let’s talk about the steps.

  • Firstly, you have to select a new cell D6 where you want to keep the result.
  • Secondly, let’s write the formula given below in the D6 cell on the Excel sheet.
=(C6-C5)/C5
  • Thirdly, press ENTER.
  • Subsequently, change the Number format as Percentage.

Calculate Year over Year Percentage Decrease in Excel

  • After that, you can drag the Fill Handle icon to AutoFill the corresponding data in the rest of the cells D7:D10.

Finally, you will get all the YoY (Year over Year) growth percentage. Which are negative in result.


Useful Tips

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

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

Useful Tips for Calculation of Year over Year Percentage Change in Excel

As a result, you can see by increasing the decimal places the value has been updated. Excel will do this Rand calculation by itself. According to your need, you can use Increase Decimal or Decrease Decimal.


Practice Section

Now, you can practice the explained method by yourself.

Practice Section for How to Calculate Year over Year Percentage Change in Excel


Bonus

You can use today’s practice workbook as a calculator. Here, cells are descriptive in nature, insert amounts in respective fields (C column, and B5 cell) it will calculate the change.


Conclusion

That’s all for today’s session. We have tried listing a couple of ways to calculate Year over Year percentage change in Excel. Hope you will find this helpful. Feel free to comment if anything seems hard to understand. Let us know which of the methods you are going to use. You can also write your own way to do the task.


Further Readings

Shakil Ahmed

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

Leave a reply

ExcelDemy
Logo