Use a Date Axis in an Excel Chart with openpyxl

A line chart of measurements taken on irregular dates tells a lie by default. Eight inspections — three in the first week of January, two in March, two in June, one in September — are drawn as eight evenly spaced points, so a nine-month improvement looks like a steady decline over eight equal steps, and the long gaps between inspection rounds disappear. The fix is a date axis, which places each point at its actual date. openpyxl charts use a text (category) axis unless told otherwise; switching takes a few properties that are easy to get wrong. This page is part of Creating Charts in Excel with openpyxl.

Prerequisites

pip install openpyxl

measurements.xlsx has an Inspections sheet with eight quality inspections on irregular dates and a defect rate for each, and a Readings sheet with temperature readings at irregular times within one day. Both sheets store real Excel dates, not text — a date axis only works with real date values.

Step 1 — The default: a text axis

For comparison, build the chart the default way:

from openpyxl import load_workbook
from openpyxl.chart import LineChart, Reference

wb = load_workbook("measurements.xlsx")
ws = wb["Inspections"]

def base_chart(title):
    ch = LineChart()
    ch.title = title
    ch.add_data(Reference(ws, min_col=2, min_row=1, max_row=9), titles_from_data=True)
    ch.set_categories(Reference(ws, min_col=1, min_row=2, max_row=9))
    s = ch.series[0]
    s.smooth = False
    s.marker.symbol = "circle"
    s.marker.size = 7
    ch.legend = None
    ch.width, ch.height = 15, 7
    return ch

text_chart = base_chart("Defect rate — text axis")
text_chart.x_axis.number_format = "dd mmm"
text_chart.x_axis.delete = text_chart.y_axis.delete = False
print(type(text_chart.x_axis).__name__)

The default x_axis is a TextAxis: each category gets one equal slot in row order, whatever the values are. smooth = False matters too — openpyxl writes line series as smoothed curves unless told otherwise, and a smoothed curve through measurements implies values between them that were never measured.

Step 2 — Switch to a date axis

Replace the x axis with a DateAxis, and pair it with the value axis through their axis IDs:

from openpyxl.chart.axis import DateAxis

date_chart = base_chart("Defect rate — date axis")
date_chart.y_axis.crossAx = 500
date_chart.x_axis = DateAxis(crossAx=100)
date_chart.x_axis.number_format = "mmm"
date_chart.x_axis.majorTimeUnit = "months"
date_chart.x_axis.majorUnit = 1
date_chart.x_axis.delete = date_chart.y_axis.delete = False

ws.column_dimensions["A"].width = 13
ws.add_chart(text_chart, "A12")
ws.add_chart(date_chart, "A27")
wb.save("measurements_charts.xlsx")
print(date_chart.x_axis.axId, date_chart.y_axis.axId, date_chart.y_axis.crossAx)

Every chart axis has an ID, and each axis names the axis it crosses. openpyxl's default value axis has ID 100 and a DateAxis has ID 500, so the value axis must be told to cross 500 and the date axis to cross 100 — skip either line and Excel cannot pair them, which shows up as a chart with a missing axis or a repair prompt. majorTimeUnit and majorUnit together set a tick every month; with number_format = "mmm" each tick is labelled Jan, Feb, Mar.

Step 3 — Render and compare

soffice -env:UserInstallation=file://$PWD/lo_profile --headless --convert-to pdf measurements_charts.xlsx > /dev/null 2>&1
pdftoppm -png -r 60 -f 1 -l 1 measurements_charts.pdf preview && ls preview*

On the text axis the defect rate falls in eight even steps. On the date axis the January points bunch together at the left edge, the rate drops sharply between January and March, and the last point sits alone in September after a two-and-a-half-month gap. That is the real story — most of the improvement happened in the first two months — and only the date axis tells it.

Step 4 — Choose tick units and label formats

majorTimeUnit is "days", "months" or "years"; majorUnit is how many of those between ticks. Match them to the span of the data and the label format to the unit:

SETTINGS = {
    "a few weeks":  ("days", 7, "dd mmm"),
    "a year":       ("months", 1, "mmm"),
    "several years": ("months", 3, "mmm yy"),
    "a decade":     ("years", 1, "yyyy"),
}
for span, (unit, step, fmt) in SETTINGS.items():
    print(f"{span:14} majorTimeUnit={unit!r:9} majorUnit={step:<2} number_format={fmt!r}")

A tick every seven days on a few weeks of data lands on the same weekday each time, which reads naturally. Labels that repeat — Jan on a chart spanning three Januaries — are the usual sign that the format is missing a year. Too many ticks is the other common problem: a daily tick on a year of data prints 365 overlapping labels, which Excel thins out unpredictably. Choose a unit that gives somewhere between six and twelve labels across the chart.

Step 5 — Times within a day need a scatter chart

A date axis works in whole days: its base unit is at most a day, so readings at 06:00, 09:00 and 15:00 on the same date are all placed at that date. For timestamps within a day, use a scatter chart, whose x axis is an ordinary number axis — and Excel times are just fractions of a day:

from openpyxl.chart import ScatterChart, Series

rs = wb["Readings"]
scatter = ScatterChart()
scatter.title = "Temperature on 28 September"
xs = Reference(rs, min_col=1, min_row=2, max_row=rs.max_row)
ys = Reference(rs, min_col=2, min_row=1, max_row=rs.max_row)
series = Series(ys, xs, title_from_data=True)
series.marker.symbol = "circle"
series.smooth = False
scatter.series.append(series)
scatter.style = 13
scatter.x_axis.number_format = "hh:mm"
scatter.x_axis.scaling.min = rs["A2"].value.toordinal() - 693594      # Excel serial of midnight
scatter.x_axis.scaling.max = scatter.x_axis.scaling.min + 1
scatter.x_axis.majorUnit = 3 / 24
scatter.x_axis.delete = scatter.y_axis.delete = False
scatter.legend = None
scatter.width, scatter.height = 15, 7
rs.add_chart(scatter, "A12")
wb.save("measurements_charts.xlsx")
print(scatter.x_axis.scaling.min, scatter.x_axis.majorUnit)

The x axis runs from midnight to the next midnight in Excel's serial-number terms — day number 46293 for 28 September 2026 — with a tick every three hours (3/24 of a day), labelled hh:mm. The gap between the 00:00 and 03:00 readings and the cluster at 06:00–09:00 are drawn to scale. Create a Scatter Chart in Excel with openpyxl covers scatter charts in general.

Getting real dates into the sheet

Everything above depends on the category cells holding dates, not text that looks like dates. Exports often deliver "05/01/2026" as a string; written to Excel unchanged, it stays text, and a date axis has nothing to position. Convert with pandas before writing, stating the format so day and month are not swapped:

import pandas as pd

raw = pd.DataFrame({"Date": ["05/01/2026", "07/01/2026", "02/03/2026"], "Defect rate %": [4.2, 4.0, 2.1]})
raw["Date"] = pd.to_datetime(raw["Date"], format="%d/%m/%Y")
with pd.ExcelWriter("from_pandas.xlsx", engine="xlsxwriter", datetime_format="dd mmm yyyy") as xw:
    raw.to_excel(xw, index=False)
check = load_workbook("from_pandas.xlsx").active
print(type(check["A2"].value).__name__, check["A2"].number_format)

The cell now holds a datetime with a date format, which is what the chart axis reads. Two details about the display format. to_datetime produces a datetime column, and ExcelWriter formats those with datetime_format — its date_format applies only to plain date objects. And in pandas 3.0 the format is honoured by the xlsxwriter engine but not by the openpyxl engine, which writes YYYY-MM-DD HH:MM:SS whatever you pass; with openpyxl, set number_format on the date cells after writing. The chart itself reads the underlying date either way, but its tick labels inherit the cell format unless the axis sets its own number_format — one more reason to set that explicitly. Parse Excel Dates into Python datetimes with pandas covers messier inputs.

When to keep the text axis

A date axis is not always right. Trading days, business-day reports and anything where "the next row" is the natural step read better on a text axis, because a date axis leaves an empty slot for every weekend and holiday. Monthly or quarterly totals are evenly spaced anyway, so either axis draws them the same. Use the date axis when the spacing between dates carries meaning: inspections, incidents, measurements, orders from a small customer.

Common pitfalls

• Dates stored as text. A date axis needs real date values; convert with pd.to_datetime before writing.
• Missing crossAx settings. Set y_axis.crossAx = 500 and DateAxis(crossAx=100) or the axes are not paired.
• Smoothed lines. Set series.smooth = False; smoothing invents values between measurements.
• Timestamps within a day on a date axis. They collapse onto the day; use a scatter chart.
• Hidden axes in Excel. Set delete = False on both axes, as for every openpyxl chart.

Conclusion

When the gaps between dates matter, replace the default text axis with DateAxis(crossAx=100), set y_axis.crossAx = 500, pick majorTimeUnit, majorUnit and a matching number_format, and turn smoothing off. Keep the text axis for trading days and regular periods, and use a scatter chart with a time-formatted number axis for readings within a day. Render the result to check the spacing tells the real story.

Frequently asked questions

How do I use a date axis in an openpyxl chart? Set chart.x_axis = DateAxis(crossAx=100) and chart.y_axis.crossAx = 500, then set x_axis.number_format, majorTimeUnit and majorUnit. The dates in the category range must be real date values.

Why are my dates evenly spaced on the chart? openpyxl line charts use a text axis by default, which gives each row one equal slot. Switch to a DateAxis to space points by their actual dates.

How do I stop the chart line from curving? Set series.smooth = False on each line series. openpyxl writes line series as smoothed unless told otherwise.

How do I chart times within a single day? Use a ScatterChart with the times as x values. Excel stores times as fractions of a day, so set the x axis number_format to hh:mm and a majorUnit such as 3/24 for three-hourly ticks.

Related

• Up one level: Creating Charts in Excel with openpyxl — every chart type openpyxl supports.
• Add a Line Chart to an Excel Report with Python — line charts in general.
• Create a Stock Chart with openpyxl — where a text axis is the right choice.
• Fix Excel Serial Numbers Showing Instead of Dates — dates as serial numbers, explained.