A year-on-year comparison chart Excel template puts the same measures for two or more years side by side, calculates the change, and displays the results in a chart. In Microsoft Excel for Microsoft 365 on Windows, set up a summary table with categories in rows and years in columns, add formulas for the amount and percentage change, then select the year columns and choose a clustered column chart.
What a year-on-year comparison shows
Year-on-year, often shortened to YoY, means comparing a measure with the corresponding period in the previous year. A business might compare annual sales by region, monthly website visits, or quarterly expenses. The comparison is useful because it puts current results in context: a figure of $92,000 is easier to interpret when last year’s comparable figure was $80,000.
A chart is only as meaningful as its comparison period. Comparing January with January is different from comparing a full year with a partial year. For a monthly report, line up January against January, February against February, and so on. If the current year is incomplete, label it clearly and avoid presenting the partial total as though it were a full-year result.
A template is usually a reusable workbook layout rather than one special Excel feature. It can be a simple table and chart, or a fuller report with source data, calculations, filters, and notes about the reporting period. The right level of detail depends on who will update and read it.
Choose a layout before building the chart
For a compact report, use one row per category and one column per year. For example, rows could be North, South, East, and West, while columns show 2024 and 2025. Add two more columns for the absolute change and percentage change. This structure makes both the chart and the underlying calculations easy to inspect.
For a template that will be refreshed regularly, keep the original records in a separate data table. A practical source layout is Category, Year, and Value, with one record per category and year. A summary table can then total matching records. This avoids manually copying totals into the report and helps when the source contains several transactions for the same category.
- Use consistent labels: Choose one spelling for each category and year so Excel does not treat minor variations as separate items.
- Keep units clear: Identify whether values are dollars, units sold, visits, or another measure.
- Separate inputs from calculations: This makes it easier to identify data that needs updating and formulas that should remain intact.
- Include a period note: State whether figures are full-year, year-to-date, or for a particular month or quarter.
Build a reusable summary table in Excel
Start by placing the source records in columns A through C: Category in A, Year in B, and Value in C. Put the year headings in the summary table, with category names in the first column. For example, put 2024 in B1, 2025 in C1, and North in A2. Add the matching source records beneath the source headings, such as North, 2024, 40000 and North, 2025, 46000.
With source categories in A2:A100, source years in B2:B100, source values in C2:C100, a category in A2 of the summary, and the year heading in B1, enter this in the summary cell B2:
=SUMIFS($C:$C,$A:$A,$A2,$B:$B,B$1)
For the example records above, the formula returns 40000 in the North/2024 cell and 46000 in the North/2025 cell. SUMIFS adds values that meet all the stated conditions; the dollar signs keep the source columns fixed when you copy the formula across and down. Copy it across for the other year headings and down for the other categories. Check that your summary headings match the source values, including whether years are stored as numbers or text.
In Microsoft 365 for Windows, you can make source data easier to extend by selecting a cell in it and choosing Insert > Table. Confirm whether the table has headers. A table expands as records are added and can be a convenient source for later reporting. On Mac, use the same Insert > Table ribbon path; the interface may look slightly different. Ctrl+T is the Windows shortcut for creating a table, while Command+T is commonly used in Excel for Mac.
Calculate amount and percentage change
The absolute change answers how much the measure rose or fell. Assuming the prior-year result is in B2 and the current-year result is in C2, put this formula in D2:
=C2-B2
With 80000 in B2 and 92000 in C2, the result is 12000, meaning the measure increased by 12,000. If the current value were 74000 instead, the result would be -6000, a decrease of 6,000. Apply an appropriate number format, such as currency or a whole number, to match the measure.
Percentage change expresses the difference relative to the previous year. With the same prior-year value in B2 and current-year value in C2, put this formula in E2:
=IF(B2=0,"",C2/B2-1)
For 80000 followed by 92000, the result is 0.15, which displays as 15% after you apply Percentage format. The formula leaves the cell blank when the earlier value is zero, because dividing by zero does not produce a meaningful percentage change. If a prior value is zero, report the new amount separately or explain the change in a note rather than implying a standard percentage increase. Percentage comparisons can also be misleading when the prior value is negative, so show the underlying amounts and describe the context.
Select a chart that fits the comparison
For a handful of categories and two or three years, a clustered column chart is a useful starting point: each category gets a cluster of adjacent year columns. Select the category labels and year-value columns in the summary table, then choose Insert > Insert Column or Bar Chart > Clustered Column. In Excel for Mac, the chart gallery is also available from the Insert tab, although its appearance can vary by release.
Choose the chart type based on the question the report needs to answer:
- Clustered column: Compare discrete values across categories, such as yearly sales by region.
- Line chart: Show a trend across ordered periods, especially monthly or quarterly values over multiple years.
- Bar chart: Compare categories when names are long or there are many of them.
- Table with conditional formatting: Keep exact values prominent when a chart would be crowded or when readers need to look up individual figures.
For monthly YoY trends, arrange months in order and use separate series for each year. A line chart then lets readers compare the seasonal pattern as well as the level of activity. Do not use a pie chart to compare years: slices make it difficult to judge changes between separate periods.
Format the report for quick reading
Give the chart a descriptive title such as Annual Sales by Region, 2024–2025, rather than a generic title like Chart 1. Label the value axis with the unit, and keep the year names consistent between the table and chart. In Excel, select the chart and use Chart Design > Add Chart Element to add a chart title, axis titles, or data labels where needed. Too many labels can make a chart hard to read, particularly when categories are numerous.
Use number formats that suit the data. Currency values may need a currency symbol and thousands separators; percentages should use Percentage format; counts may work best as whole numbers. Avoid changing the underlying values merely to make them look shorter. For large numbers, a clearly labeled axis in thousands or millions can be easier to read than cramped data labels.
Keep colors consistent: assign the same color to a given year throughout the workbook, and use a restrained palette that remains legible when printed. If color carries meaning, do not rely on color alone; include year labels and a clear legend. Consider freezing the top row of a long source table using View > Freeze Panes, and protect formula cells if other people will enter data. Protection should be treated as an editing aid, not as a substitute for secure storage.
Common errors and how to prevent them
A chart can look polished and still tell the wrong story. Check the data and the period definitions before sharing it. A common error is comparing mismatched periods, such as a full prior year against only the first six months of the current year. Another is using an average in one year and a total in the other. The measure and time span need to be comparable, or the difference needs to be disclosed.
- Missing categories: Check whether a blank means zero, unavailable, or not applicable. Those meanings are not interchangeable.
- Duplicate source rows: Decide whether repeated records are separate transactions that should be summed or accidental duplicates that should be removed.
- Text instead of numbers: Values imported with symbols or spaces may not calculate as numeric data. Inspect the source and convert data carefully rather than hiding the issue with formatting.
- Misleading axis scale: A column chart whose value axis starts well above zero can exaggerate differences. Use a sensible scale and make any nonzero baseline obvious.
- Unexplained blanks or zeros: Add a note describing missing data or new categories so readers do not assume the result is a confirmed zero.
When the workbook is updated, verify at least one category by hand and compare the report totals with the source. Also check that the chart includes all intended rows and years. If a chart does not expand when new data is added, review its source range or use an Excel table as the source. These checks are especially important before copying a chart into a presentation or sending a static PDF.
Template options and when to use each
A simple worksheet with a summary table, change formulas, and one chart is often enough for a recurring two-year report. It is quick to maintain and easy to print. A more detailed workbook can use one sheet for raw data, another for the summary, and a third for notes or a dashboard. Separating these roles helps prevent accidental edits to the source while keeping the report readable.
Excel templates can be saved as ordinary workbooks for repeated use or as template files when a fresh copy is needed for each reporting period. Before reusing a file, clear only the intended input cells; do not delete formulas or chart source data by mistake. If colleagues use different Excel versions, test the workbook in the oldest version they need to support. Basic tables, SUMIFS, and standard charts are broadly supported, while newer functions may not be available in older releases.
A pivot table is another option when the source has many categories, dates, or transaction records. It can group and summarize data without manually building every total. For a predictable, small report, a formula-based summary is often more transparent. For a large or changing dataset, a pivot table may be easier to refresh and explore. Neither choice removes the need to confirm that the source records and reporting periods are correct.
Frequently Asked Questions
How do I make a year-on-year chart in Excel?
Arrange comparable periods in columns and categories in rows, calculate any required changes, select the category labels and year values, then choose Insert > Insert Column or Bar Chart > Clustered Column. Add a descriptive title and verify that the chart covers the intended periods.
What is the formula for year-over-year percentage change?
With the prior-year value in B2 and current-year value in C2, use =IF(B2=0,"",C2/B2-1) and format the result as a percentage. A zero prior-year value has no ordinary percentage-change result, so show the absolute change or explain the case instead.
Should I use a bar chart or a line chart for YoY data?
Use clustered columns or bars to compare a small number of year totals across categories. Use a line chart when the main purpose is to show an ordered trend, such as monthly results across several years. The chart should match the comparison the reader needs to make.
Can I compare a partial year with a full year?
You can, but label the current figure as year-to-date and avoid treating it as a full-year comparison. A fairer view may compare the same elapsed months in both years, such as January through June against January through June.
Why does my percentage change show an error?
The most common cause is a prior-year value of zero, which makes the usual percentage formula divide by zero. Other causes include numbers stored as text or blank cells that do not mean what the formula assumes. Check the source values and decide how zero, missing, and not-applicable results should be reported.
Related
- Vendor Comparison Excel Template: A Practical Setup Guide
- Excel Drop-Down Validation: Setup, Lists, and Fixes
- Vacation Meal Planning Template Excel: Plan Every Day
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Power Pivot Column Formatting: Set Formats That StickIn Excel for Microsoft 365 on Windows, open the Power Pivot window, select the column, then choose a…
- Excel Pivot Table: Group Dates into Months and YearsYes. In Excel for Microsoft 365 on Windows, right-click any date in the PivotTable, choose Group, select Months,…
- How to Change the Key on a Pie Chart in Excel Step by StepTo change the key, or legend labels, on a pie chart in Excel, edit the category names in…
- Excel Pivot Table: Percentage of Two ColumnsTo show one PivotTable column as a percentage of another in Excel for Microsoft 365 on Windows, add…
- Excel Grouped Stacked Bar Chart: How to Create OneExcel does not offer a built-in chart type that combines clustered bars with stacked segments. You can create…
- Treemap Chart in Excel: How to Create OneTo create a basic treemap chart in Excel, arrange your categories in columns from broadest to most specific,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.