Add a Line Chart to an Excel Report with Python

Add an editable line chart to an Excel report with openpyxl: write time-series data, plot multiple series, set a date x-axis, add markers, label axes, and anchor it.

This page adds a line chart to an Excel report from Python with openpyxl, the right choice when a metric moves over time. You will write a dated time-series, plot a single line, add a second series, switch the x-axis to read real dates, and turn on markers — all producing a native, editable Excel chart. It is the time-series companion to the parent cluster, Creating Charts in Excel with openpyxl, and to its bar chart guide.

Prerequisites

Bash

pip install openpyxl

datetime is used to write real date values into the sheet and comes with the standard library. Any current openpyxl release has the openpyxl.chart API below.

Step 1 — Write time-series data

Write a date column and one or more metric columns, with a header row so each series can take its name from it. Use real datetime.date objects, not strings — that is what lets the x-axis behave as dates later.

Python

# pip install openpyxl
from datetime import date
from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.title = "Traffic"

ws.append(["Date", "Visitors", "Signups"])     # row 1: header
rows = [
    (date(2026, 1, 1), 1200, 90),
    (date(2026, 1, 8), 1340, 110),
    (date(2026, 1, 15), 1290, 105),
    (date(2026, 1, 22), 1510, 130),
    (date(2026, 1, 29), 1625, 142),
]
for r in rows:
    ws.append(r)

for cell in ws["A"][1:]:        # format the date column so Excel shows dates
    cell.number_format = "yyyy-mm-dd"

wb.save("line_step1.xlsx")
print("Wrote 5 weekly rows: Date, Visitors, Signups")

The data sits in A1:C6 — header in row 1, five data rows below.

Step 2 — Plot a single line

Bind one metric column with Reference, include the header for the series name, and set the date column as categories.

Python

# pip install openpyxl
from datetime import date
from openpyxl import Workbook
from openpyxl.chart import LineChart, Reference

wb = Workbook()
ws = wb.active
ws.append(["Date", "Visitors", "Signups"])
for r in [(date(2026, 1, 1), 1200, 90), (date(2026, 1, 8), 1340, 110),
          (date(2026, 1, 15), 1290, 105), (date(2026, 1, 22), 1510, 130),
          (date(2026, 1, 29), 1625, 142)]:
    ws.append(r)

chart = LineChart()
chart.title = "Weekly Visitors"
chart.y_axis.title = "Visitors"
chart.x_axis.title = "Week"

data = Reference(ws, min_col=2, min_row=1, max_row=6)   # Visitors + header
chart.add_data(data, titles_from_data=True)
cats = Reference(ws, min_col=1, min_row=2, max_row=6)   # dates, no header
chart.set_categories(cats)

ws.add_chart(chart, "E2")
wb.save("line_single.xlsx")
print("Saved line_single.xlsx")

As with bar charts, the value Reference includes row 1 (for the name) and the category Reference starts at row 2.

Step 3 — Plot multiple series

To plot Visitors and Signups together, widen the value Reference to cover both columns. add_data creates one series per column, and the header row names them.

Python

# pip install openpyxl
from datetime import date
from openpyxl import Workbook
from openpyxl.chart import LineChart, Reference

wb = Workbook()
ws = wb.active
ws.append(["Date", "Visitors", "Signups"])
for r in [(date(2026, 1, 1), 1200, 90), (date(2026, 1, 8), 1340, 110),
          (date(2026, 1, 15), 1290, 105), (date(2026, 1, 22), 1510, 130),
          (date(2026, 1, 29), 1625, 142)]:
    ws.append(r)

chart = LineChart()
chart.title = "Visitors vs Signups"
chart.y_axis.title = "Count"
chart.x_axis.title = "Week"

# Two metric columns (B and C) -> two series.
data = Reference(ws, min_col=2, max_col=3, min_row=1, max_row=6)
chart.add_data(data, titles_from_data=True)
chart.set_categories(Reference(ws, min_col=1, min_row=2, max_row=6))

print("series:", len(chart.series))   # -> 2, legend appears automatically
ws.add_chart(chart, "E2")
wb.save("line_multi.xlsx")

With more than one series the legend appears on its own. The key is that the value Reference spans min_col=2 to max_col=3 while the category Reference stays on the single date column.

Step 4 — A date axis, markers, and smoothing

set_categories treats your dates as text labels. To get a true date-scaled axis — where uneven gaps between dates are spaced proportionally — switch the x-axis to dateAx. Add markers so each data point is visible, and decide on smoothing.

Python

# pip install openpyxl
from datetime import date
from openpyxl import Workbook
from openpyxl.chart import LineChart, Reference
from openpyxl.chart.axis import DateAxis

wb = Workbook()
ws = wb.active
ws.append(["Date", "Visitors", "Signups"])
for r in [(date(2026, 1, 1), 1200, 90), (date(2026, 1, 8), 1340, 110),
          (date(2026, 1, 15), 1290, 105), (date(2026, 1, 22), 1510, 130),
          (date(2026, 1, 29), 1625, 142)]:
    ws.append(r)
for cell in ws["A"][1:]:
    cell.number_format = "yyyy-mm-dd"

chart = LineChart()
chart.title = "Weekly Traffic"
chart.x_axis = DateAxis(crossAx=100)   # treat categories as real dates
chart.x_axis.number_format = "yyyy-mm-dd"
chart.x_axis.majorTimeUnit = "days"
chart.x_axis.title = "Date"
chart.y_axis.title = "Count"

data = Reference(ws, min_col=2, max_col=3, min_row=1, max_row=6)
chart.add_data(data, titles_from_data=True)
chart.set_categories(Reference(ws, min_col=1, min_row=2, max_row=6))

for s in chart.series:
    s.smooth = False          # straight segments; True for curved
    s.marker.symbol = "circle"
    s.marker.size = 7

ws.add_chart(chart, "E2")
wb.save("line_dateaxis.xlsx")
print("Saved line_dateaxis.xlsx with a date axis and markers")

DateAxis requires the category cells to hold real dates (Step 1) — that is why the data was written with datetime.date objects rather than strings. With a plain text axis, January 1 and January 29 would be equally spaced regardless of the real gap; the date axis spaces them by actual time.

Common pitfalls

Symptom	Cause	Fix
X-axis ignores date spacing	Dates stored as strings, or axis left as default category axis	Write `datetime.date` values and assign `chart.x_axis = DateAxis(crossAx=100)`.
Only one line appears for two metrics	Value `Reference` covers one column	Widen it with `max_col` to span every metric column.
The date column itself plotted as a line	Date column included in the value `Reference`	Keep dates out of the values — put them only in `set_categories`.
Series named "Series 1", "Series 2"	Header row omitted or `titles_from_data` not set	Start the value `Reference` at `min_row=1` and pass `titles_from_data=True`.
Last data point missing	`max_row` off by one	With a header plus N rows, `max_row = N + 1`. Five rows → `max_row=6`.
No markers on the line	Markers off by default in openpyxl	Set `series.marker.symbol = "circle"` per series.

Performance and scale

The chart definition is tiny and fixed in size; only the underlying cells scale with the data. A line chart stays readable far longer than a bar chart — hundreds of points per series are fine — but past a few thousand points the rendered line turns to noise, so downsample or aggregate (for example to weekly or monthly buckets) before plotting. Writing the series data is the only per-row cost, and it is negligible at report scale.

Frequently asked questions

Why are my dates evenly spaced even though the real gaps differ? You are on a category axis, which treats every label as equal. Switch to DateAxis and make sure the cells hold real date values.

How do I add a second y-axis for a series on a different scale? Create a second LineChart, add the other series to it, set its y_axis.axId to a new value and y_axis.crosses = "max", then combine with chart += chart2.

Can I mix a line and bars in one chart? Yes — build a BarChart and a LineChart, then add them together with chart1 += chart2. The combined chart keeps both renderings.

Should I use smoothing? Usually no. Smoothing (series.smooth = True) draws curves between points that imply values you don't have. Leave it False for honest reporting.

Conclusion

A line chart for a report is the same Reference-and-categories pattern as any openpyxl chart, with two time-series specifics: write real datetime.date values so you can promote the x-axis to a DateAxis, and widen the value Reference across columns to get one line per metric. Keep the date column out of the values, get max_row right, and add markers so individual points stay visible.

Where to go next

Up to the cluster: Creating Charts in Excel with openpyxl
The sibling guide: Create a Bar Chart in Excel with openpyxl
Related: Building Multi-Sheet Excel Dashboards