How to Compare Two Years of Data in Excel Graph

Comparing performance across different years is one of the most fundamental tasks in data analysis, allowing you to see how you are progressing over time. With Excel, you can transform rows of numbers into a clear, visual story showing your growth, identifying seasonal patterns, and pinpointing areas for improvement. This guide will walk you through, step-by-step, how to create a graph that effectively compares two years of data.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

Why You Should Compare Year-Over-Year Data

Before jumping into the "how," let's quickly touch on the "why." Year-over-year (YoY) comparison is a powerful way to gauge the health and momentum of your business or project. It helps you:

• Measure Growth: The most obvious benefit is observing if you're growing, stagnating, or declining. Are sales up? Is website traffic increasing? A YoY graph provides immediate answers.
• Identify Seasonality: Most businesses have a natural rhythm. Your sales might surge during the holidays or dip in the summer. Comparing years helps you distinguish predictable seasonal trends from genuine performance changes.
• Evaluate Strategic Impact: Did you launch a major marketing campaign in March? A YoY comparison showing a spike in leads for that month (compared to the previous March) helps validate your strategy's effectiveness.
• Forecast More Accurately: Understanding historical trends and growth rates allows you to make more educated predictions about future performance, which is vital for budgeting and resource planning.

Step 1: Prepare Your Data for Comparison

A good chart starts with well-organized data. If your data isn't structured properly, Excel will struggle to interpret what you want to visualize. For a year-over-year comparison, the ideal setup is simple and clean.

Arrange your data in columns. Typically, you'll have one column for the time period (like months), followed by a column for each year you're comparing.

For example, if you're comparing 2023 sales versus 2024 sales by month, your table should look like this:

Data Preparation Tips:

• Keep it Consistent: Ensure the time periods are identical for both years. Don't compare a full 12 months of one year to only 6 months of another unless you're explicitly doing a year-to-date (YTD) analysis. Labeling your data clearly (e.g., "H1 2023 vs H1 2024") is critical.
• Clean Your Data: Check for any typos, inconsistent formatting (e.g., text instead of numbers), or blank cells that could throw off your graph. Empty cells for future months are fine, but ensure historical data is complete.
• Keep It Simple: Your source table for the chart should only contain the data you plan to visualize. Keep other calculations or notes separate to avoid confusing Excel.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Step 2: Create the Year-Over-Year Graph Using a Line Chart

Once your data is prepped, creating the graph itself is straightforward. A line chart is usually the best choice for comparing time-series data, as it clearly shows trends over time. A column chart is also a good option if you want to emphasize the magnitude of each month's metric.

Let's use a line chart for this walkthrough.

Instructions:

1. Select Your Data: Click and drag your cursor to highlight the entire data range you prepared, including the column headers (Month, 2023 Sales, 2024 Sales).
2. Insert the Chart: Go to the Insert tab on Excel's ribbon. In the Charts section, click on the "Insert Line or Area Chart" icon. From the dropdown, select the "Line with Markers" chart type. This type places a dot on each data point, making it easier to read specific monthly values.

Excel will instantly generate a chart and place it on your worksheet. It might look good already, but we need to refine it to ensure it's easy to read and tells a clear story.

Step 3: Format and Refine Your Chart for Clarity

A default Excel chart is a starting point, not the final product. A few quick formatting changes can turn a basic chart into a professional-looking visual that communicates insights effectively.

1. Fix the Title

The default title will likely be something generic like "Chart Title." Click on it and change it to something descriptive. For our example, "Monthly Sales Performance: 2023 vs. 2024" is clear and concise.

2. Check the Horizontal Axis Labels

Excel is usually smart enough to pick up the months for the horizontal (X) axis from your first column. However, sometimes it uses numerical placeholders (1, 2, 3...) instead. If this happens, here's how to fix it:

• Right-click anywhere on the chart and choose Select Data.
• In the "Select Data Source" window that pops up, look for the "Horizontal (Category) Axis Labels" box on the right and click the Edit button.
• An "Axis Labels" window will appear. Click into the text box, then select the range in your worksheet that contains the month names (e.g., A2:A13).
• Click OK on both windows to apply the changes.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

3. Add Axis Titles

Your chart needs labels so viewers know what the numbers on the vertical and horizontal axes represent.

• Click on your chart to activate it.
• Go to the Chart Design tab.
• On the far left, click Add Chart Element > Axis Titles.
• Choose Primary Horizontal and Primary Vertical one at a time. This adds text boxes to your axes.
• Click each new text box and type in the appropriate label, such as "Month" for the horizontal axis and "Sales ($)" for the vertical axis.

4. Customize Colors and Styles

Use colors to help your audience differentiate between the years easily. You can click on one of the lines to select it, then use the Format tab to change its color, thickness, and marker style. Choose contrasting colors that are easy to distinguish, like a solid blue for 2023 and a dashed orange for 2024.

Advanced Y-o-Y Analysis: Creating a Combination Chart

If you want to enhance your annual reporting, don't stop at just comparing the raw numbers. Calculating and visualizing the variance - the difference or percentage growth between the two years - adds another layer of insight.

A combination chart is perfect for this. We can show the sales for each year as columns and plot the percentage growth as a line on a secondary axis.

Step 1: Calculate the Variance

In your data table, add a fourth column called "% Growth." In the first cell of that column (next to your January data), enter the following formula:

=(C2-B2)/B2

This calculates the percentage difference between the 2024 value (C2) and the 2023 value (B2). Drag the fill handle down to apply this formula to all months. Format this column as a percentage.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Step 2: Create the Combo Chart

1. Select all your data, including the new "% Growth" column.
2. Go to Insert > Charts and click on the "Insert Combo Chart" icon.
3. Choose the "Clustered Column - Line on Secondary Axis" option.
4. Excel will try to guess which series should be the line, but we need to tell it specifically. In the "Create Combo Chart" window, do the following:
5. Click OK.

After some formatting adjustments and labeling of your axes, you'll have created a descriptive visual. It will show not only how each of the years' performances has played out, but also highlight exactly where and by how much performance fluctuated. In other words, you have created a powerful visualization that elucidates both the occurrence and the specifics of trends.

Final Thoughts

By arranging your data thoughtfully and taking a few simple actions, you can transform rows into a clear, visual story of powerful comparative and dynamic analysis. This allows your audiences to see data with their own eyes instead of making assumptions. You learned how to craft a simple line chart and an advanced combination graph for visual analysis. This is just the beginning of what is possible.

Creating these YoY Excel reports can eventually become a repetitive and manual process where data needs to be exported, cleaned, and visualized. That's where we come in to support you, so if your team wants to move past tedious spreadsheet work and have instant insights, we've got you covered with Graphed. With our tools, you can see live updating dashboards anytime with accurate, timely insights, empowering marketing teams to build great brands. This means everyone on your staff will understand how their work improves the company's mission instead of waiting for weekly reports to find the most critical numbers.