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.
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.
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.
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.
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.
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.
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.
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
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.
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, for 2018, YoY Growth(Basic Formula) and YoY Growth(Advanced Formula) not looking same.
We can rectify these.Use Decrease Decimal option and these two will be looked same.
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.
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.
You can use today’s practice workbook as a calculator.
Cells are descriptive in nature, insert amounts in respective fields it will calculate the change.
- How to Calculate Sales Growth Percentage in Excel
- Calculate Average Percentage in Excel [Free Template+Calculator]
- How to Calculate Percentage of Sales in Excel
- How to Calculate Growth Percentage Formula in Excel
- How to Calculate Variance Percentage in Excel
- How do you Calculate Percentage Increase or Decrease in Excel
- How to calculate salary increase percentage in Excel [Free Template]
- Excel formula to calculate percentage of grand total (4 Easy Ways)