How to Compare 3 Years of Data in Excel

Trying to see how this year’s performance stacks up against the previous two? Comparing multiple years of data in Excel is one of the best ways to spot long-term trends, identify patterns, and get a clear picture of your business's health. This guide will walk you through several practical methods, from building informative charts to calculating year-over-year growth, helping you turn your raw numbers into meaningful insights.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

First Things First: Structure Your Data for Success

Before you build any charts or write any formulas, the single most important step is to organize your data correctly. A well-structured source table will save you countless headaches down the road. For time-series analysis like this, the best format is often called "long format" or a simple database-style table.

Your goal is to have a continuous list, not separate blocks of data for each year. Ideally, you want at least two columns:

• A Date column (e.g., "Month", "Date")
• A Value column (e.g., "Sales", "Revenue", "Traffic", "Units Sold")

Here’s an example of what it should look like:

Correct Data Structure:

Avoid organizing your data in separate columns by year, like this:

Incorrect Structure for Analysis:

While the second structure seems organized, it’s much less flexible for Excel’s powerful analysis tools like PivotTables. Trust us, taking a few minutes to set up your data in a continuous list will make everything that follows dramatically easier.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Method 1: Visualize Trends with a Multi-Year Line Chart

The line chart is the classic tool for comparing performance over time. It makes it incredibly easy to see trends, seasonality, and overall growth at a glance. We’ll use a PivotChart to create this, as it's the most flexible and robust way to handle the data.

Let's say we want to see monthly sales for 2021, 2022, and 2023 on a single chart.

Step-by-Step Guide:

1. Create a PivotTable: Click anywhere within your properly formatted data table. Go to the Insert tab on the Ribbon and click PivotTable. Excel will likely autoselect your data range. Just click OK to place the PivotTable on a new worksheet.
2. Arrange the PivotTable Fields: In the "PivotTable Fields" pane on the right, drag your fields to the following areas:
3. Clean Up Your Row Labels: In the "PivotTable Fields" pane, remove "Quarters" and "Date" from the Rows area, leaving only "Months." Now your table has months down the side and years across the top—perfect for a chart.
4. Insert the PivotChart: With your PivotTable selected, go to the PivotTable Analyze tab and click on PivotChart. Choose the first Line chart option and click OK.

You now have a dynamic line chart comparing your three years of data, month by month. Each line represents a different year, making it easy to see which years outperformed others and whether seasonal patterns (like a summer dip or a holiday spike) are consistent.

Method 2: Compare Specific Periods with a Clustered Column Chart

While a line chart shows trends over time, a clustered column chart is excellent for a direct, side-by-side comparison of specific periods. It helps answer questions like, "How did Q4 of this year compare to the last two Q4s?" or "In which year did we have the best January sales?"

The setup is very similar to the line chart.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Step-by-Step Guide:

1. Arrange Your PivotTable: Create a new PivotTable or modify your existing one. For a column chart, you typically want your time periods on the X-axis.
2. Insert the Column Chart: Select your PivotTable, go to the PivotTable Analyze tab, and click PivotChart.
3. Select a Clustered Column Chart: In the chart menu, select Column and choose Clustered Column. Click OK.

This chart will now show a cluster of three bars for each month (one for 2021, one for 2022, and one for 2023). This visual makes it instantly clear, for example, that December sales were far higher in 2023 than in the previous two years.

Pro Tip: Use your chart’s formatting options to add a clear title (e.g., "Monthly Sales Comparison: 2021-2023") and ensure the legend is easy to read. A well-labeled chart tells its own story.

Method 3: Calculate and Analyze Year-Over-Year (YoY) Growth

Visualizing raw numbers is great, but calculating the percentage of change provides deeper context. Year-over-Year (YoY) growth tells you how much a metric increased or decreased compared to the same period in the previous year. This is a common and powerful Key Performance Indicator (KPI).

To do this, you first need to prepare your data in a side-by-side format (the one we warned against earlier for general analysis is perfect for this specific calculation).

Step-by-Step Guide:

1. Create a Comparison Table: Either manually or using a PivotTable (as arranged in the previous methods), create a simple table with Months in the first column and your three years of sales data in the next three columns.
2. Add Columns for YoY Calculation: Next to your 2022 data column, create a new column header called "YoY Growth 2022 vs 2021." In the column next to 2023, create one called "YoY Growth 2023 vs 2022."
3. Write the Growth Formula: Assuming your 2021 data is in column B and 2022 in column C. For January (row 2), in the first cell of your YoY column, enter:
4. Format and Fill Down: Press Enter, then select the cell, go to the Home tab, and click the Percentage Style (%) button to format the number. Drag the fill handle down to copy the formula for all months. Repeat for the 2023 vs 2022 calculation.

You can then create a line or column chart from these YoY growth percentages to visualize momentum. For example, a chart could reveal that while total sales are growing, the rate of growth is slowing down — an invaluable insight for business planning.

Method 4: Use Slicers for Interactive Filtering

For a more dynamic report, you can add Slicers to your Pivot Chart. Slicers are user-friendly buttons that allow you (or anyone you share the report with) to quickly filter the data and focus on specific combinations of years without having to change the PivotTable itself.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step-by-Step Guide:

1. Create Your Pivot Chart: Build a line or column PivotChart as described in the previous methods.
2. Insert a Slicer: Click on your PivotChart to select it. On the PivotTable Analyze tab, click Insert Slicer.
3. Choose Your Field: A dialog box will appear showing your available fields. Check the box for Years (or your primary Date field) and click OK.

A neat little "Years" slicer box will appear on your screen. Now you can simply click on "2021" to see only that year's data. Or, hold down the Ctrl key and click on "2022" and "2023" to compare only those two years. Slicers make your report feel like an interactive dashboard and are perfect for presentations where you might get asked follow-up questions.

Final Thoughts

As you can see, Excel provides a fantastic toolkit for multi-year analysis. Whether you choose a simple line chart for visualizing trends, a clustered column chart for direct comparisons, or calculating YoY growth for deeper insights, structuring your data correctly and leveraging PivotTables is the foundation for turning numbers into strategy.

We know that even with these steps, pulling data from multiple sources, cleaning it, and manually building these reports in Excel every week or month takes hours of valuable time. We built Graphed to automate all of that. You can connect your data sources (like Google Analytics, Shopify, your CRM, etc.) just once, and then use simple, plain-English commands to create the same reports in seconds. Instead of wrestling with PivotTable fields, you can just ask, "Show me a line chart of monthly revenue for the last three years," and get a live, automated dashboard instantly.