Excel Line Chart Comparing Two Years: A Clear Setup

To make an Excel line chart comparing two years, put the shared time periods in the first column and each year’s values in its own column, select the whole table, then choose Insert > Insert Line or Area Chart > 2-D Line. Excel uses the first column for the horizontal axis and draws one line for each year, making it easy to compare trends across matching months, weeks, or other periods.

Arrange the data so periods line up

The most important preparation is making sure each row represents the same point in time for both series. For example, a monthly sales comparison should have one row for January, one for February, and so on. A useful layout is:

Column A: Month
Column B: 2024 sales
Column C: 2025 sales

Enter the column names in the first row and the periods in the rows below. Keep the two years side by side, and put their values in the same units. A blank or text value in one year should not shift the other year’s numbers into a different month. The chart compares cells on the same row, so matching row positions matter more than the labels looking similar.

For a full-year monthly comparison, keep the periods in calendar order from January through December. Excel generally plots categories in the order they appear in the source data; it does not need to sort month names alphabetically. If your records are daily, decide how to align the years before charting. Comparing March 1 in one year with March 1 in another is different from comparing the same weekday or the same numbered day of a 365-day period.

For an annual comparison that includes February 29, choose a consistent rule: include February 29 as its own category, omit it from both years, or compare totals using a documented method. Do not silently shift later dates to compensate for the leap day, because that can pair unlike periods and make the trend misleading.

Create the line chart in Excel

In Excel for Microsoft 365 on Windows, follow these steps:

Arrange the data with period labels in the first column and one column for each year, including clear headers.
Select the full data range, including the headers and all period labels.
Choose Insert > Insert Line or Area Chart, then select a 2-D Line option. Use Line with Markers if you want a visible point at every period.
Check that the horizontal axis shows the periods and that the legend identifies the two year columns.
Use the chart title and axis titles to explain what is being measured and the units, if they are not obvious.

If Excel puts the years on the horizontal axis and creates a separate line for each month, the chart has interpreted rows and columns the other way around. Select the chart and choose Chart Design > Switch Row/Column. You can also use Chart Design > Select Data to check the series names, values, and horizontal axis labels. In the Select Data Source window, each year should appear as a series and the period labels should supply the horizontal axis.

In Excel for Mac, the chart commands are available on the Insert and Chart Design tabs, though their placement can vary slightly with the ribbon layout. Excel for the web also supports inserting and editing line charts, but some detailed formatting controls may differ from the Windows desktop version.

Choose the right line-chart type

A standard 2-D Line chart is usually the clearest choice when the horizontal axis consists of named periods such as months, quarters, or stages. It treats those labels as categories and spaces them evenly. This is helpful when the point is to compare January with January and February with February, rather than to show the exact number of days between observations.

A Line with Markers chart adds a symbol at each observation. Markers help readers see the individual monthly values, especially when there are only a few periods. With many daily or hourly observations, markers can overlap and clutter the picture; a plain line is often easier to read. A Stacked Line chart is generally not suitable for comparing two years’ actual values, because it stacks the series and changes what the line heights represent.

If your observations are irregularly spaced—for example, readings taken after 1, 3, and 20 days—a line chart’s equally spaced category axis can imply equal gaps. Consider an XY (Scatter) chart when the actual numeric or date spacing matters. A scatter chart uses numeric horizontal values, so it is a better fit for showing the relationship between actual elapsed time and measurements than for a simple month-by-month category comparison.

Make the comparison easy to read

Use a descriptive title such as “Monthly Revenue: 2024 and 2025” rather than leaving the default title or writing only “Comparison.” Include the measurement units when readers might not know them. A vertical axis title such as “Revenue (dollars)” or “Temperature (°C)” gives useful context without requiring a reader to infer the scale.

Give each year a distinct, accessible color and keep the legend labels specific. Avoid relying on color alone if the chart may be printed in grayscale; markers or different line styles can help distinguish the series. Keep gridlines light enough that they support estimating values without competing with the data. For a small chart, a legend at the bottom or side is often easier to read than labels placed over the lines.

Think carefully about the vertical-axis scale. A zero baseline is appropriate when showing quantities where the size of the difference is central, such as sales volume. A line chart may use a nonzero minimum to make small movements visible, but that can exaggerate apparent differences. If you change the axis bounds, make the choice clear and consider whether readers may mistake a modest change for a dramatic one. Avoid a secondary axis just to make the lines appear closer together; separate scales can obscure the size of the actual difference.

For a quick numeric check, add the value labels only to selected points, such as the start, end, maximum, or latest month. Labeling every point may make a 12-month chart crowded and a daily chart unreadable. If precise values are important, keep the source table available or add a compact data table beneath the chart rather than filling the plot with labels.

Handle missing values and incomplete years

A comparison is often made before both years are complete. It is fine to show a year-to-date line beside the same months from the previous year, but label the chart or subtitle clearly—for example, “January–August, year to date.” Do not fill future months with zero unless zero is the true value. Doing so creates an artificial drop and can change the impression of the chart.

For periods with no observation, distinguish between “not recorded” and “recorded as zero.” A blank cell indicates missing data; a cell containing 0 indicates a measured value of zero. Excel’s treatment of empty cells can be reviewed from the chart’s Select Data Source dialog by choosing Hidden and Empty Cells. Depending on the chart settings, empty cells can appear as gaps, zeros, or connected lines. A gap is often the most honest choice when the value is unknown, while connecting points can imply continuity that the data does not establish.

If a year has fewer periods than the other, retain the shared period labels and leave unavailable observations blank rather than deleting rows. That preserves alignment and makes the incomplete coverage visible. If the source data contains formulas that return an empty text value, check how the chart displays those results; they may not behave exactly like genuinely empty cells in every chart setup.

Common chart problems and fixes

The legend shows months instead of years: Select the chart and use Chart Design > Switch Row/Column. Then confirm the series in Chart Design > Select Data.
The horizontal axis shows numbers or the wrong labels: In Select Data, edit the horizontal axis label range and select the cells containing the periods, not the value columns.
The months appear out of order: Check the source rows and arrange them in calendar order. A category chart follows the source order, so alphabetic sorting of month names produces the wrong sequence.
One line looks much larger than the other: Check that both series use the same unit and that no values were stored as text. Also inspect the vertical-axis bounds and confirm you did not choose a stacked chart.
The line has an unexpected drop to zero: Look for zeros, formulas returning zero, or empty-cell settings that plot blanks as zero. Correct the source or chart setting according to what the missing value means.
The chart omits new rows: If you add periods after creating the chart, check whether the chart’s data range includes them. Converting a changing source range to an Excel table can make chart data easier to extend as rows are added.

Line chart versus other ways to compare years

A line chart is especially useful for showing direction and turning points across an ordered sequence. It lets a reader see whether one year generally ran above the other, when the gap widened, and whether the two series followed similar seasonal patterns. It does not automatically explain why the values changed, and a visual crossing is not proof of a meaningful change without considering the underlying numbers and context.

A clustered column chart can be a better choice when the main task is comparing discrete values for each month. It places two bars beside each other for each category, which can make exact month-to-month contrasts more obvious, though many categories can make the chart wide and crowded. A table with a difference column is more suitable when readers need exact differences rather than a visual trend. The best format depends on the question: use a line to emphasize movement over time, bars for discrete period comparisons, and a table for precise values.

Another useful view is an indexed comparison, where both years start from a common baseline such as 100. This highlights relative change rather than the original amounts, which can help compare trends when starting levels differ. It also changes the meaning of the vertical axis, so it should be labeled clearly and should not replace the original-value comparison when readers need actual amounts.

Frequently Asked Questions

How do I add a second year to an existing Excel line chart?
Select the chart, choose Chart Design > Select Data, and add the second year’s values as a series. Set its series name to the year header and confirm that the horizontal axis labels match the existing periods. If the years are already in adjacent columns, expanding the chart’s source range to include the new column may be simpler.

Should I use a line chart or a bar chart to compare two years?
Use a line chart when the emphasis is on trends and the order of periods. Use a clustered column chart when readers should compare the two separate values within each month or category. Neither format is automatically more accurate; the clearest choice depends on what readers need to notice.

Can I compare two years when one year has missing months?
Yes. Keep all shared month labels in the table and leave unknown values blank. Explain that the year is incomplete, and use a chart setting that represents missing values honestly rather than treating them as zero.

Why does Excel show the years along the bottom instead of the months?
Excel has likely plotted rows as series and columns as categories, or vice versa. Select the chart and choose Chart Design > Switch Row/Column, then review the series and axis-label ranges in Select Data.

Can I compare daily values for two calendar years?
Yes, but decide how to align dates, particularly around February 29 and when the years begin on different weekdays. Use matching month-and-day categories for calendar-date comparisons, or use a scatter chart if actual elapsed time and uneven spacing matter.

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.