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.
| Month | 2018 | 2019 | 2020 |
|---|---|---|---|
| January | 947 | 877 | 894 |
| February | 418 | 427 | 362 |
| March | 658 | 686 | 746 |
| April | 722 | 737 | 846 |
| May | 596 | 577 | 536 |
| June | 687 | 796 | 752 |
| July | 719 | 642 | 716 |
| August | 700 | 842 | 998 |
| September | 690 | 728 | 698 |
| October | 569 | 500 | 537 |
| November | 335 | 306 | 352 |
| December | 528 | 498 | 576 |
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
OR
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.
