In this article, I will show you how to make year over year growth chart in Excel.
In a hurry, download the free template and update it with your figures.
What is Year-Over-Year Growth?
Year-over-year (YOY) growth is a key performance indicator comparing growth in one year against the comparable year.
How to Calculate YOY Growth
Follow the below three steps:
- Take your current month’s growth number and subtract the same measure realized 12 months before. If the difference is positive, your organization experienced growth; if it’s negative, that indicates a loss.
- Next, take the difference and divide it by the prior year’s total number. This will give you the growth rate for your 12-month period.
- Multiply it by 100 to convert this growth rate into a percentage rate or simply click the percentage icon.
Excel Step by Step guide
Copy the below table in a new Excel file.
In this table, I am comparing sales volume for the years 2018, 2019 and 2020.
Step 1: Select the month column and the years, you want your chart to be populated with. I selected 2019 and 2020, because we will be making YOY also for these two years.
Step 2: Go to Insert Tab and click on Recommended charts.
Step 3: Select the chart you like most for your data visualization. I selected cluster column chart.
Your Year over year comparison chart is ready. Cheers!
Add columns for Year over Year growth percentage
|YOY = Current period – Comparative period / Comparative period (Base)|
Use the above formula to calculate Year over Year growth / decline in sales volume.
After you are done, the columns will look like this:
Notice how I add colors automatically. Green for growth and red for decline.
You can do so by navigating Home > Conditional Formatting > Highlight Cell rules > Greater than
Home > Conditional Formatting > Highlight Cell rules > Less than
Hope this article helped. If you need any assistance, feel free to reach out to me through the Contact Form.