How to Show Year Over Year Growth in Excel Chart
Tracking year-over-year (YoY) growth is fundamental to understanding your business's health and trajectory. Seeing a raw number is one thing, but visualizing it brings the story to life. This article will walk you through exactly how to calculate and create compelling year-over-year growth charts in Microsoft Excel, turning your spreadsheets into powerful reporting assets.
What Exactly is Year-Over-Year (YoY) Growth?
Before jumping into the charts, let's quickly clarify what we're measuring. Year-over-year growth compares a metric for a specific period (a month, a quarter) against the same period from the previous year. For example, comparing sales from May 2024 to May 2023.
Why is this so important? Because it smooths out seasonality. Your ice cream shop will always sell more in July than in January, so comparing July sales to June sales isn't very insightful. But comparing July 2024 to July 2023 tells you if your business is genuinely growing, staying flat, or declining, independent of seasonal factors.
Step 1: Calculate YoY Growth in Excel
You can't chart what you haven't calculated. First, we need to add a column in our spreadsheet for the YoY growth percentage. The formula is straightforward:
(Current Period - Previous Year's Period) / Previous Year's Period
Let's use a common example: monthly revenue. Imagine you have your data set up like this, with 2023 revenue in Column B and 2024 revenue in Column C.
We'll add a new column, "YoY Growth," in Column D.
In cell D2, type the following formula to calculate the growth for January:
(C2-B2)/B2
This formula subtracts last year's January revenue (B2) from this year's (C2) to find the difference, then divides it by last year's revenue to get the growth percentage.
Press Enter. Excel will likely display this as a decimal (e.g., 0.15).
Select cell D2, go to the Home tab on the Ribbon, and click the Percent Style (%) button in the Number group. Now it will show as 15%.
To apply this formula to all the other months, click on cell D2 again, then click and drag the small square (the fill handle) in the bottom-right corner of the cell down to the last row of your data.
Your table should now look like this, fully populated with the YoY growth percentage for each month.
Step 2: Choose the Right Chart to Visualize YoY Growth
With our data ready, it’s time to create the chart. There isn't one "perfect" chart, the best choice depends on the story you want to tell. We’ll cover the three most effective options.
Option 1: The Combo Chart (Columns and Line)
When to use it: This is the classic and most comprehensive way to show YoY growth. It's perfect for when you want to see the actual performance numbers (e.g., monthly revenue) and the growth rate on the same chart. It answers two questions at once: "How are we performing?" and "How does that compare to last year?"
How to Create a Combo Chart:
Select all your data, including the headers. In our example, that's range A1:D13.
Go to the Insert tab on the Ribbon.
In the Charts group, click on Recommended Charts. Excel is pretty smart here and will often suggest a Combo Chart. If not, click the All Charts tab.
Select Combo from the list on the left.
Now, you need to configure the chart.
For the 2023 Revenue and 2024 Revenue series, choose Clustered Column as the chart type.
For the YoY Growth series, choose Line as the chart type and — this is the most important part — check the box for Secondary Axis.
Click OK.
Excel will generate a combo chart. The columns show your monthly revenue, easily comparing 2023 vs. 2024, while the line hovers over them, showing the growth trend. The left vertical axis represents the revenue values ($), and the right vertical axis represents the growth rate (%).
Formatting Tips for the Combo Chart
Add a Clear Title: Change the default "Chart Title" to something descriptive, like "Monthly Revenue & YoY Growth (2024 vs. 2023)."
Label Your Axes: Click the "+" icon next to the chart and check "Axis Titles." Label the left axis "Monthly Revenue ($)" and the right axis "YoY Growth (%)."
Adjust Colors: Use contrasting but complementary colors. Make the prior year a lighter shade (e.g., light blue) and the current year a darker shade (dark blue) to create a visual hierarchy.
Option 2: The Dual Line Chart
When to use it: When your main goal is to compare the pattern or trend of performance between the two years, not just the growth percentage. This visual makes it easy to spot things like, "Did our typical summer slump start earlier this year?" or "Is our Q4 ramp-up stronger than last year?"
How to Create a Dual Line Chart:
Select only the months and the two revenue columns. To do this, click and drag to select A1:C13. (We are excluding the YoY Growth column for this chart).
Go to the Insert tab and, in the Charts group, click the Insert Line or Area Chart icon.
Choose the first option under 2-D Line.
Excel immediately creates a chart with two lines, one for each year, plotted against the months. The space between the lines visually represents the growth or decline in revenue. When the 2024 line is above the 2023 line, you’re growing.
Formatting Tip for the Dual Line Chart
Make sure your legend is clear and easy to read. You can click on the legend to move it to the top, bottom, or side of the chart area for better readability.
Option 3: The Dedicated Column Chart for Growth Percentage
When to use it: When the narrative is exclusively about the growth rate itself. This chart doesn't show revenue values at all, it laser-focuses on whether you grew or shrank month-over-month. It's great for high-level management dashboards where stakeholders just need a quick answer to "Did we improve?"
How to Create a Growth % Column Chart:
This time, we'll select non-adjacent columns. First, click and drag to select the months in range A2:A13.
Now, hold down the Ctrl key (or Cmd on a Mac) and select the YoY Growth percentages in range D2:D13. You should now have both ranges selected.
Go to the Insert tab and click the Insert Column or Bar Chart icon.
Choose the first option under 2-D Column.
You'll get a simple column chart showing the positive and negative growth rates for each month. This makes it instantly obvious which months were winners and which were losers.
Formatting Tip for the Growth % Column Chart
Use color to tell the story even faster. Right-click on one of the bars, choose "Format Data Series," and under the Fill options, check the box for "Invert if negative." This will automatically color positive values one color (e.g., blue or green) and negative values another (e.g., orange or red).
Pro Tip: Handle Errors with IFERROR
What if you had no sales in February last year? Your YoY formula =(C3-B3)/B3 would try to divide by zero, resulting in an ugly #DIV/0! error in your cell and a gap in your chart.
To prevent this, wrap your formula in the IFERROR function. It tells Excel what to do if the formula results in an error.
=IFERROR((C2-B2)/B2, 0)
This revised formula says: "Try to calculate the YoY growth. If you get an error (like dividing by zero), just show 0 instead." This keeps your data clean and your charts professional.
Final Thoughts
Mastering year-over-year charts in Excel is an invaluable skill for anyone involved in performance reporting. By first calculating the growth percentage and then choosing the right visualization — a combo chart for detail, a line chart for trends, or a column chart for impact — you can transform rows of data into a clear and compelling story about your business's progress.
While Excel is powerful, the weekly routine of downloading CSVs, cleaning data, and rebuilding these charts costs valuable time that could be spent on strategy. We created Graphed to eliminate that friction entirely. You connect your data sources (like Google Analytics, Shopify, HubSpot) once, and your dashboards update in real-time. Instead of manual formulas and chart formatting, you can ask questions in plain English like, "show me revenue year-over-year as a column chart," and get a live, interactive visualization in seconds.