Calculate Year over Year Percentage Change in Excel (3 Easy Techniques)

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

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

Excel Sheet - Calculate YoY Percentage Change

The Excel sheet is about revenue earned each year from 2015 to 2020. There are five columns, Year, Earning Amount, YoY Growth (Basic Formula), YoY Growth(Advanced Formula), Cumulative year over year. We will calculate the percentage changes year over year.

Practice Workbook

I have shared the Excel sheet. You can download it from the link below.

Calculate Year over Year Percentage Change in Excel

Today we will see how to calculate Year over Year percentage. We will see how to do that in both the conventional way and with the advanced way as well. Let’s drive into the session.

1. The Conventional Way to Calculate

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

(New Amount – Old Amount )/Old Amount

Let’s write on the Excel sheet.

Conventional method - Calculate YoY Percentage Change

It’s obvious that we can not calculate the change for the first one, as there is nothing before that. We will start 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. All our calculations will be done by using Cell Reference.

If you don’t get the result in percentage format, then explore the Number section on the Home tab and Select Percentage.

Percentage formation

You will get the value in your desired format. Now you can write the formula for the rest of the rows or simply use Excel AutoFill feature.

AutoFill Conventional way

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.

2. The Advanced Way to Calculate

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

( New Value / Old Value ) – 1

Let’s write it on the Excel sheet.

Advanced Formula - Calculate YoY Percentage Change

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

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

AutoFill Advanced method

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. Now let’s see how to do that.

When you are calculating cumulative changes, you need to have a common base value. You need to calculate the changes using that base value.

(New value / Base Value) – 1

Cumulative YoY

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 Absolute Cell Reference of the cell containing the amount of 2015.

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.

Percentage Increase-Decrease

You can choose what you prefer to use. Here you can see, for 2018, YoY Growth(Basic Formula) and YoY Growth(Advanced Formula) not looking same.

Decimal Place

We can rectify these.Use Decrease Decimal option and these two will be looked same.

Decrease Decimal

You can see by decreasing 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.

Conclusion

That’s all for today’s session. I 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 me know which of the methods you are going to use. You can also write your own way to do the task.

Bonus

You can use today’s practice workbook as a calculator.

Calculator

Cells are descriptive in nature, insert amounts in respective fields it will calculate the change.

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