To make a yearly comparison chart in Excel, put each year in its own column and each measure or category in a row, select the summary table, then choose Insert > Insert Column or Bar Chart > Clustered Column. If your data is individual transactions, summarize it by year first with a PivotTable or a formula such as SUMIFS; a chart of raw transaction dates usually will not give a clear year-to-year comparison.
What a yearly comparison chart shows
A yearly comparison chart puts values from different years side by side so you can see how a measure changes over time. Common examples include annual sales, expenses, website visits, production volumes, or the number of service requests. The chart is only as meaningful as the measure and time period behind each value: a calendar-year total, a fiscal-year total, and a year-to-date figure are not interchangeable.
For example, a business might compare annual revenue for 2022, 2023, and 2024, with separate bars for each year. If it has several product lines, each product line can be a separate series in the chart. This helps answer both how the overall result changed and which categories contributed to the change. A simple chart can also make a misleading comparison look convincing, so it is worth checking the dates, units, and completeness of the data before interpreting the picture.
Prepare the source data
Keep the source data in a consistent, rectangular table: one header row, one record per row, and one field per column. For transaction-level data, a practical layout is Date, Category, and Amount. Store dates as real Excel dates, not text that merely looks like a date, and store amounts as numbers. Avoid blank rows inside the data and merged cells in the header.
For a small, already summarized comparison, arrange the chart data with years across the top and measures or categories down the side. For instance, columns B, C, and D might hold the values for 2022, 2023, and 2024, while column A lists product names. If years are stored as numeric year values, Excel can use them as labels without treating them as dates. Keep units consistent: do not mix dollars and thousands of dollars in the same series without clearly converting and labeling them.
- Use one clear heading per column, such as Date, Region, and Sales.
- Decide whether the comparison uses calendar years or fiscal years and apply the same rule to every record.
- Check for duplicate transactions, missing dates, and inconsistent category names such as North and N. that might split one group into two.
- Turn a growing source range into an Excel Table with Insert > Table. Tables make data easier to filter and can expand when new records are added.
Build an annual summary with a formula
For a straightforward total by calendar year, SUMIFS can add values whose dates fall within the year shown in a header cell. This approach is useful when you want a compact summary table that feeds a standard chart. The date criteria use the first day of the selected year and the first day of the following year, excluding the latter. That boundary method also handles dates that include times.
Assume A2:A7 contains real Excel dates, C2:C7 contains amounts, and D1 and E1 contain the numeric years 2024 and 2025. The example records have amounts of 100, 150, and 200 in 2024, and 250, 300, and 350 in 2025. With those headers and ranges, enter this in D2 and copy it to E2:
=SUMIFS($C$2:$C$7,$A$2:$A$7,">="&DATE(D$1,1,1),$A$2:$A$7,"<"&DATE(D$1+1,1,1))
The formula returns 450 for 2024 and 900 for 2025. It sums the amount range only when the matching date is on or after January 1 of the header year and before January 1 of the next year. Add another year heading to the next column and copy the formula across to extend the summary. To compare categories separately, add a category criterion to the SUMIFS formula or create one summary row per category; make sure the category labels match the source data exactly.
Use a PivotTable for larger or changing data
A PivotTable is often easier than writing formulas when records are numerous, categories change, or you need several summaries. Select a cell in the source data, choose Insert > PivotTable, confirm the data range, and choose where to place the PivotTable. In the field list, place the date field in Rows and the amount field in Values. Check that the Values field summarizes by Sum rather than Count if you are comparing totals.
Excel may group dates automatically. If it does not, right-click a date in the PivotTable, choose Group, and select Years. If you also want a breakdown, put the category field in Columns or beneath Years in Rows. Then select a cell in the PivotTable and choose Insert > PivotChart, or use the PivotChart command available from the PivotTable ribbon. A PivotChart follows its PivotTable’s filters and layout, which is handy for exploring regions or product groups without rebuilding the chart.
Grouping can fail when the date field contains blanks, text dates, or invalid entries. Correct those source values first, then refresh the PivotTable using PivotTable Analyze > Refresh. New rows added to a properly constructed Excel Table are generally easier to include in a refreshed PivotTable than rows added beyond a fixed source range.
Choose a chart type that fits the question
A clustered column chart is a strong default when the goal is to compare a few annual values or compare categories within each year. The bars start from a common baseline, so differences are relatively easy to judge. A line chart can also work for a continuous time trend, especially when the chart includes many years, but a line connecting only a handful of annual totals may suggest a level of continuity that the data does not show.
- Clustered column: Compare separate annual totals or category values side by side.
- Line: Emphasize the direction of a trend across a longer sequence of years.
- Stacked column: Show how categories contribute to each year’s total, while remembering that only the bottom series shares a consistent baseline.
- 100% stacked column: Compare proportions when the total size is less important than each category’s share.
Choose a chart based on the question, not just appearance. If you want to compare both total sales and the percentage contributed by each product, separate charts may be clearer than forcing both messages into one chart. A secondary axis can be appropriate when two measures have very different scales, but label it clearly; otherwise, it can make unrelated movements seem comparable.
Create and format the chart
For a summary table, select the year headings and the values you want to compare, including category labels if present. Choose Insert > Insert Column or Bar Chart > Clustered Column. If Excel plots years as series and categories as labels when you intended the reverse, select the chart and choose Chart Design > Switch Row/Column. Add a descriptive title, such as Annual Sales by Product, and label the vertical axis with units when those units are not obvious.
Keep formatting restrained. Use the same color for the same category in every year, use readable labels, and avoid three-dimensional effects that distort apparent bar size. If values differ greatly, consider whether the axis scale is appropriate; a truncated vertical axis can exaggerate small differences in a column chart. For very small changes, a data table or a carefully labeled line chart may communicate the result more honestly than dramatic bars.
When the chart is built from a PivotTable, use the PivotChart’s field buttons and filters to control what is displayed. When it is built from a regular summary, verify that the selected range includes all the intended years and categories. In either case, update the source and refresh or extend the chart when new periods are added. On Excel for Mac, the chart and PivotTable concepts are similar, but ribbon tab names and command placement can differ slightly by release; use the Insert tab and the PivotTable or chart commands available in that version.
Make fair year-to-year comparisons
The most common interpretation mistake is comparing a complete year with an incomplete one. For example, January through September 2025 should not be presented as though it were a full-year total beside all twelve months of 2024. Label a partial year as year to date and compare matching periods, such as January through September in both years. A forecast for the rest of the year should be clearly distinguished from recorded results.
Also check whether the values are nominal or adjusted for inflation, whether accounting rules changed, and whether the data covers the same locations or products in every year. A jump can reflect a new reporting system or expanded coverage rather than a real change in performance. If a category did not exist in an earlier year, explain that rather than allowing a blank or zero to imply the same thing. Use zero only when the measured value was actually zero; use a blank or a note when the value is unknown or not applicable.
Common problems and fixes
- The PivotTable counts instead of sums: In the Values area, open the field settings and change the summary to Sum. If Sum is unavailable, the source values may be stored as text.
- Dates will not group by year: Look for blanks, text, or invalid dates in the date column. Convert or repair them, refresh the PivotTable, and group again.
- The chart skips a year: Check that the summary range includes its column and that the year heading is a value rather than a blank or unintended text label.
- New rows do not appear: Confirm that the source is an Excel Table or update the PivotTable’s source range, then refresh. A normal chart may also need its data range extended.
- Bars look almost identical despite large totals: Check the axis bounds and number formatting. For a column chart, a baseline that starts above zero can overstate visual differences.
Frequently Asked Questions
How do I compare two years in Excel?
Select a summary with one value for each year and insert a clustered column chart using Insert > Insert Column or Bar Chart > Clustered Column. If the source has many dated records, summarize them by year first with a PivotTable or SUMIFS.
Can Excel make a year-over-year comparison automatically?
A PivotTable can group valid dates by year and summarize a measure, and a PivotChart can display that summary. It will not decide whether the years are comparable: you still need to check that periods, categories, and units match.
How do I compare fiscal years instead of calendar years?
Define the fiscal-year start and group records using that same period consistently. A standard PivotTable’s date grouping by Years follows calendar years, so a fiscal-year comparison may need a fiscal-year field in the source or a separate summary designed around the organization’s fiscal calendar.
Should I use a PivotChart or a regular chart?
Use a PivotChart when you want to filter or rearrange a large, changing dataset. Use a regular chart when you already have a small, stable summary and want precise control over the displayed range and formatting.
Why does my yearly chart show dates instead of years?
The chart may be using individual date entries as category labels. Summarize the dates by year in a PivotTable or create a yearly summary table, then chart the summary instead of the transaction-level date column.
Related
- Supplier Contact List Template in Excel: Fields and Setup
- Supply List Excel Template: How to Build and Use One
- Excel Superscript Shortcut: Apply Superscript to Text
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Stacked Bar Chart: How to Create and Read OneTo make a stacked bar chart in Excel for Microsoft 365 on Windows, select your category labels and…
- Excel Pivot Table Filter Not Refreshing? Fixes That WorkIf an Excel pivot table filter is not refreshing, first select the PivotTable and choose PivotTable Analyze >…
- Excel Histogram Tutorial: Create and Customize ChartsTo make a histogram in Microsoft Excel for Microsoft 365 on Windows, select the numeric data, then choose…
- Excel Pivot Table Sort by Sum Value: Full GuideYes, you can sort a pivot table by the sum of a value field. Click the arrow in…
- Radar Charts in Excel: Why and When to Use ThemA radar chart in Excel is used to compare several measures for one or more items across the…
- Excel Sparkline Location Range: Meaning and Setup GuideIn Excel, the sparkline Location Range is the cell or cells where the miniature charts will appear; the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.