Excel Pivot Chart Year Over Year: Setup and Tips

To make an Excel PivotChart compare results year over year, create a PivotTable from dated data, group its date field by Months and Years, put Months on the chart axis and Years in the legend, then insert a PivotChart. For a growth-rate view, set the value field to “% Difference From” the previous year; the steps below explain the setup and the important limits of that comparison.

What a year-over-year PivotChart shows

A year-over-year (YoY) chart compares a measure for equivalent periods in different years. For example, it can show sales in January 2024 beside sales in January 2025, then repeat that comparison for each month. It can also show the percentage change from one year to the next. A PivotChart is useful because you can change the fields, filter categories, and refresh the results without rebuilding a conventional chart from scratch.

There are two common views, and they answer different questions:

Actual values: Compare the amounts, such as monthly sales of $12,000 versus $13,800. This makes differences in scale visible.
Year-over-year change: Compare the percentage or amount of change, such as 15% growth. This emphasizes the rate of change rather than the underlying totals.

A chart with months on the horizontal axis and separate year series is usually the clearest starting point. It makes seasonality—such as a recurring holiday peak—easier to spot. A chart showing only annual totals can answer a different question, but it hides the month-by-month pattern.

Prepare the source data

PivotTables work best when each row represents one record and each column contains one kind of information. A sales table might have columns named Date, Region, Product, and Sales. Use a real Excel date in Date, and a number in Sales. Avoid merged cells, blank header cells, subtotal rows inside the data, and separate columns for every year; those structures make filtering and grouping less dependable.

Turn the source range into an Excel Table before creating the PivotTable. Select a cell in the data and use Insert > Table, confirm that the table has headers, and choose OK. On Windows, Ctrl+T is a shortcut; on Mac, use Command+T. A Table is useful when new records are added because the table range can expand to include them. You still need to refresh the PivotTable after adding records.

Check that every value in the Date column is a valid date rather than text that merely looks like one. Blank dates, text dates, or mixed date formats can prevent Excel from grouping the field correctly. Also decide whether the data includes a full year. Comparing a complete prior year with only the first few months of the current year can make the newer year look artificially low.

Create the PivotTable and group dates

Select a cell in the source Table, then choose Insert > PivotTable.
In the dialog, confirm the table or range and choose where to place the PivotTable, commonly New Worksheet. Select OK.
In the PivotTable Fields pane, drag the Date field into Rows. Drag the measure, such as Sales, into Values. If Excel counts sales records instead of summing their amounts, open the value field menu, choose Value Field Settings, and select Sum.
In the PivotTable, right-click one of the date entries and choose Group. Select Months and Years, then choose OK.

Excel creates grouped date fields for the selected units. The original dates remain the source; grouping changes how the PivotTable summarizes them. If Group is unavailable or Excel reports that the field cannot be grouped, inspect the source for blank or non-date entries, correct them, and refresh the PivotTable before trying again.

Grouping by Months alone combines January from every year into one January total. That can be useful for an all-years seasonal summary, but it is not a year-over-year comparison. Select both Months and Years so the PivotTable can keep each year’s January, February, and other periods distinct.

Arrange fields for a monthly comparison

For a chart that compares monthly values across years, arrange the generated fields like this:

Rows (category axis): Months
Columns (series or legend): Years
Values: Sum of Sales, or the measure you want to compare
Filters, optional: Region, Product, or another field used to narrow the view

Depending on the Excel layout, the field areas may be labeled Rows, Columns, Values, and Filters, while a chart describes the same placements as Axis (Categories) and Legend (Series). If Excel places Years on the axis and Months in the legend, move the fields to the positions above. The PivotTable should have one row for each month and a separate value column for each year. That layout makes the chart’s comparison easy to read.

Consider the meaning of the measure as well as its field placement. Summing transaction amounts is appropriate for revenue, while averaging may be more useful for a measure such as order value. For inventory snapshots or headcount, summing repeated records may be misleading; choose a summary that matches how the source records were collected.

Insert and format the PivotChart

Click inside the PivotTable and choose PivotTable Analyze > PivotChart. In the chart dialog, select a chart type and choose OK. For monthly comparisons, a Clustered Column chart is a practical choice: each month has a group of columns, with one column per year. A line chart can make the direction and seasonal shape easier to follow, particularly when there are many months or several years.

Give the chart a title that states both the measure and the period, such as “Monthly Sales by Year.” Check that the legend identifies each year and that the month labels are in calendar order. If the chart includes too many series, filter the PivotTable to a smaller set of years or use a line chart carefully; a crowded display can obscure rather than reveal a pattern. Use consistent number formats and avoid an unnecessary 3-D effect, which can make values harder to compare.

PivotCharts include controls tied to the PivotTable. You can use them to filter fields, or manage fields through the PivotChart Fields pane. To reduce visual clutter, select the chart and use PivotChart Analyze > Field Buttons to hide field buttons. The chart remains connected to its PivotTable, so changing a filter or refreshing the table can update the chart too.

Show year-over-year growth instead of totals

To chart the percentage change rather than the sales totals, open the menu for the value field in the PivotTable Fields pane and choose Value Field Settings. On the Show Values As tab, select % Difference From. Set Base field to Years and Base item to (previous), then choose OK. Excel calculates each year’s percentage change against the preceding year for the corresponding month. The earliest year has no prior year in the displayed comparison, so its result is blank.

For example, if January sales were $12,000 in 2024 and $13,800 in 2025, the 2025 result is a 15% increase: the difference is $1,800, and $1,800 divided by $12,000 is 0.15. Make sure the selected base field is the grouped Years field, not Months. If the result looks unexpected, verify that the year order is chronological and that the PivotTable has the intended month and year layout.

This setting changes how the value field is displayed; it does not create an independent, permanent growth calculation in the source data. To show both totals and growth, add the Sales field to Values a second time. Keep one copy as Sum and set the other to % Difference From. Rename the displayed value fields if needed so the reader can distinguish dollars from percentages. A separate worksheet calculation is another option when you need a custom definition or a presentation layout that should not change with PivotTable settings.

Choose a fair comparison period

A year-over-year chart is only meaningful when it compares like with like. If the current year contains data through June but the prior year includes all twelve months, comparing annual totals is not a fair measure of performance. Filter both years to the same completed months, or label a partial-year comparison clearly. The same caution applies to daily data: compare equivalent dates or business periods if weekends, holidays, or different month lengths affect the measure.

Also consider changes in the business or data collection. A new region, product line, pricing method, or reporting system can cause a jump that looks like organic growth. A PivotChart summarizes what is present in the source; it cannot determine whether a change in totals has a business explanation. Add a note or use a filter when the scope of the data differs between years.

Refresh, filter, and troubleshoot

After adding or changing source records, click the PivotTable and choose PivotTable Analyze > Refresh. To refresh multiple PivotTables, use Data > Refresh All. If new rows do not appear, confirm that the data is inside the source Table and that the PivotTable uses that Table as its source. Refreshing updates the summary, but it does not fix invalid dates or incorrect source values.

Common problems and useful checks include:

Dates will not group: Look for blanks, text, errors, or mixed values in the date field. Correct the source and refresh.
Only one January total appears: The PivotTable is likely grouped by Months without Years. Group by both fields and place Years in Columns.
Values are counted, not summed: Check whether the Sales column contains numbers stored as text, then select Sum in Value Field Settings.
New records are missing: Check the source Table boundary, then refresh. A PivotTable does not necessarily update itself as soon as source data changes.
The chart seems to compare unequal periods: Apply matching date filters and check whether the latest year is partial.

In Excel for Microsoft 365 on Windows, the ribbon paths above are the standard desktop workflow. Excel for Mac has PivotTables and PivotCharts, although ribbon layout and some field controls can differ; use the equivalent PivotTable or PivotChart ribbon tabs. Excel for the web can be useful for viewing and filtering workbook results, but some grouping or chart-editing controls may vary. If a command is missing, open the workbook in the desktop app.

PivotChart versus a regular chart

A PivotChart is a good fit when the reader needs to interact with categories, switch years, or filter the summarized data. It stays connected to a PivotTable and inherits its field structure. That connection is also a constraint: its data series and categories are controlled by the PivotTable, so it is not as freely rearranged as a chart based on an ordinary worksheet range.

A regular chart may be preferable for a fixed report with a carefully selected set of series, custom helper calculations, or a layout that should not change when someone filters the PivotTable. You can copy or reference summarized PivotTable results in a separate reporting area and chart that range, but make sure those references continue to cover the intended rows and columns after refreshes. For an interactive analysis, start with a PivotChart; for a tightly controlled presentation, compare its behavior with a regular chart.

Frequently Asked Questions

Can Excel show year-over-year percentage change in a PivotChart?
Yes. In Value Field Settings, choose Show Values As > % Difference From, then select Years as the base field and (previous) as the base item. The PivotChart can display those percentages, provided the PivotTable is arranged with the periods you want to compare.

Why does my PivotChart combine the same months from different years?
The date field is probably grouped by Months only. Group the dates by both Months and Years, then put Months on the category axis and Years in the legend or Columns area.

Can a PivotChart compare this year with the previous year only?
Yes. Filter the Years field to the two years you want to compare. The filter changes the PivotTable and its connected chart; make sure the displayed months cover equivalent periods.

Does a PivotChart update automatically when I add source rows?
Usually you need to refresh it. If the source is an Excel Table, new rows can be included in the Table, but the PivotTable still needs a refresh to incorporate them into its summary.

Should I use a line chart or a column chart for YoY data?
Use clustered columns when comparing the actual value for each month across a small number of years. Use lines when the trend or seasonal pattern matters most. Either can work for percentages; choose the one that remains readable with your number of years and categories.

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.