In this article, we will learn to calculate Year-over-Year (YoY) Growth with a formula in Excel. We need to calculate the year-over-year growth to show the performance of a company. It is used often as people can compare a company’s current sales with the previous year. It also expresses the financial progress of a company. So, today, we will show 2 formulas that you can use to compute Year-over-Year (YoY) Growth in Excel.
Download Practice Book
Download the practice book here.
Concept of Year over Year (YoY) Growth
Year-over-Year (YoY) Growth refers to the calculation that compares the value of one year with the previous year. Mainly, it is used to compute the growth rate of a company in terms of sales. It is one of the criteria that a company uses to judge its performance. To calculate the Year-over-Year Growth, we use the formula below:
=(New Value-Old Value)/Old Value
To represent the percentage, we need to multiply the formula by 100.
=[(New Value-Old Value)/Old Value]*100
We can find two types of results of Year-over-Year Growth.
- Positive Growth: This indicates the profit or increase in sales of a company.
- Negative Growth: This indicates the loss or decrease in sales of a company.
2 Ways to Calculate Year over Year Growth with Formula in Excel
To explain the methods, we will use a dataset that contains information about the Sales Amount from 2014 to 2019 of a company.
1. Use Traditional Formula in Excel to Calculate Year over Year Growth
In the method, we will use the traditional formula to calculate Year-over-Year (YoY) Growth in Excel. The traditional formula refers to the conventional way to calculate year-over-year growth. Let’s follow the steps below to learn more.
- In the first place, create a column to calculate the year-over-year growth.
- Secondly, select Cell D6 and type the formula:
Here, the first cell of the YoY Growth column is empty because we don’t have any old value for that cell.
- Now, hit Enter to see the result.
- After that, we need to convert the number format of the cell to percentage. To do so, go to the Home tab and select Percentage from the Number section.
- Then, you will see the result of Cell D6 in percentage.
- Finally, drag the Fill Handle down to see year-over-year growth till 2019.
Read More: How to Calculate YTD (Year-to-Date) in Excel [8 simple ways]
2. Compute Year over Year Growth with Modern Formula in Excel
We can also compute the Year-over-Year Growth with a different formula. Here, we will calculate the ratio of the new value and old value and then subtract it from 1. In general form, the formula looks like the below:
=(New Value/Old Value)-1
Here, we will use the same dataset and structure. Let’s pay attention to the steps below to learn more.
- Firstly, select Cell D6 and type the formula:
- After that, press Enter to see the result.
- Now, go to the Home tab and select Percentage from the Number field.
- Then, you will see the growth rate in percentage like the picture below.
- Finally, use the Fill Handle to auto-fill formulas till 2019.
Read More: Growth Formula in Excel with Negative Numbers (3 Examples)
Bonus: Calculate Growth Based on a Specific Year in Excel
Now, you need to evaluate a company’s growth based on a specific year. For example, you need to know the growth from 2015 to 2019 based on 2014. In that case, follow the steps below.
- To begin with, select Cell D6 and type the formula:
Here, we have used absolute cell reference using the dollar ($) sign. It helps us to lock the value of the denominator.
- After that, hit Enter to see the result.
- In the end, change the number format to percentage and then, use the Fill Handle to calculate growth till 2019.
Read More: Growth Over Last Year Formula in Excel (A Step-by-Step Analysis)
Things to Remember
In the above methods, we have seen negative growth in the year 2015. It generally represents the loss or the decrease in sales compared to the previous year. Moreover, you can also use the same formulas to calculate growth over a monthly period.
We have demonstrated 2 easy methods to Calculate Year-over-Year (YoY) Growth with Formula in Excel. Here, we have used different formulas and also discussed the method to calculate growth based on a specific year in the bonus section. I hope these methods will help you to perform your tasks easily. Furthermore, we have also added the practice book at the beginning of the article. You can download it to learn more. Last of all, if you have any suggestions or queries, feel free to ask in the comment section below.