Python Excel AutomationPractical guides for developers
Start learning
Guide
Formatting And Charting Excel Reports With PythonDeep dive

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 main guide, Creating Charts in Excel with openpyxl, and to its sibling bar chart walkthrough, which share the same Reference-and-categories chart model this page assumes.

A dated time-series column rendered as a two-series line chart A column of dates with two value series is bound into a line chart whose x-axis reads real dates, plotting two lines over time. Time-series data Date A B Jan 1 Jan 8 Jan 15 Jan 22 Jan 29 Reference Jan 1 Jan 15 Jan 29

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. The number_format on column A only changes how Excel displays the stored dates, not the underlying values; for the full set of display codes see Format Dates in Excel Cells with Python.

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.

The same five dated points on a category axis versus a DateAxis Two line charts plot identical values for the dates Jan 1, Jan 8, Jan 10, Jan 28 and Feb 1. On the category axis the points are evenly spaced by index; on the DateAxis they are spaced by real elapsed time, so the 18-day gap between Jan 10 and Jan 28 opens up while the 2-day gap between Jan 8 and Jan 10 compresses. Category axis points spaced evenly, one step per date DateAxis points spaced by real elapsed time 1 step Jan 1 Jan 8 Jan 10 Jan 28 Feb 1 18 days Jan 1 Jan 15 Feb 1

Common pitfalls

SymptomCauseFix
X-axis ignores date spacingDates stored as strings, or axis left as default category axisWrite datetime.date values and assign chart.x_axis = DateAxis(crossAx=100).
Only one line appears for two metricsValue Reference covers one columnWiden it with max_col to span every metric column.
The date column itself plotted as a lineDate column included in the value ReferenceKeep 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 setStart the value Reference at min_row=1 and pass titles_from_data=True.
Last data point missingmax_row off by oneWith a header plus N rows, max_row = N + 1. Five rows → max_row=6.
No markers on the lineMarkers off by default in openpyxlSet 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.

Lines need an ordered category axis

A line chart implies that the horizontal axis has a sequence, so the underlying rows must already be in that order — openpyxl plots them exactly as they sit on the sheet. Sorting by date before writing is therefore part of building the chart, not a separate concern, and an unsorted series produces the zig-zag that makes a reader distrust every other figure on the page.

Gaps are information

A line drawn straight through a month with no data implies continuity that the data does not support. Leaving the gap — rather than filling it with zero — is usually the honest choice, and it is one the chart makes visible at a glance.

Ranges come from the data

The habit that keeps a monthly report correct is deriving every range, row count and column position from what was just written rather than from a number typed once. ws.max_row after the write, a header map built from row one, a table reference rebuilt on each run — each of those removes a class of failure that is silent rather than loud. A hardcoded range does not raise when the data outgrows it; it simply stops covering the rows nobody looks at, and the report is wrong in a way that takes months to notice. Deriving costs a line and removes the whole category.

What a line implies

A line chart's assumptions A line implies a continuous, ordered axis, so the rows must be sorted and evenly spaced. Unordered or irregular categories are better shown as bars. ordered and regular dates in sequence equal intervals a line is honest unordered or irregular categories, not time gaps of varying size use bars instead

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.