Create a Step Chart in Excel with Python

Some numbers do not drift from one value to the next; they jump. A list price is 4.50 until 15 March and 4.80 from then on. A base rate holds for months and then moves a quarter point overnight. Headcount, contract value and stock on a shelf after a delivery behave the same way. A normal line chart joins each point to the next with a sloping line, which suggests the price was 4.65 on 1 March — a value it never had. A step chart draws such data honestly: flat lines while the value holds and vertical jumps where it changes. Excel has no step chart type, but a scatter chart with straight lines drawn through doubled-up points produces one exactly. This page prepares the data with pandas, builds the chart with openpyxl on a real date axis, extends the final value to the end of the period, compares two series, and renders the result to check it. It belongs to Creating Charts in Excel with openpyxl.

Prerequisites

pip install openpyxl pandas pillow

LibreOffice (soffice) and pdftoppm are used only to render the chart for checking. The data is a price history: the dates on which a product's list price changed and the new price from each date, plus the same for a competitor.

Step 1 — Why a line chart misleads

import pandas as pd

ours = pd.DataFrame({"date": pd.to_datetime(["2026-01-01", "2026-03-15", "2026-06-01", "2026-08-20"]),
                     "price": [4.50, 4.80, 4.65, 5.10]})
theirs = pd.DataFrame({"date": pd.to_datetime(["2026-01-01", "2026-02-10", "2026-05-05", "2026-09-01"]),
                       "price": [4.70, 4.60, 4.95, 4.85]})
PERIOD_END = pd.Timestamp("2026-09-30")

on_1_march = ours.set_index("date")["price"].reindex(pd.date_range("2026-01-01", "2026-09-30")).ffill()["2026-03-01"]
interp = ours.set_index("date")["price"].reindex(pd.date_range("2026-01-01", "2026-09-30")).interpolate()["2026-03-01"]
print(f"price on 1 March: actual {on_1_march:.2f}, what a line chart implies {interp:.3f}")

On 1 March the price was still 4.50 — the change came on the 15th. A line chart joining 1 January at 4.50 to 15 March at 4.80 passes through 4.74 on 1 March, which never happened. For prices, rates and levels the question readers ask is "what was it on date X?", and only a step chart answers it correctly.

Step 2 — Double up the change points

A step is two points at the same date: the old value and the new value. Build the step series by repeating each change date with the previous price, then add a final point at the end of the period so the last price is drawn for as long as it applied:

def steps(df, end):
    rows = []
    for i, (date, price) in enumerate(zip(df["date"], df["price"])):
        if i:
            rows.append((date, df["price"].iloc[i - 1]))      # the old value up to the change
        rows.append((date, price))                              # the new value from the change
    rows.append((end, df["price"].iloc[-1]))                    # hold the last value to the period end
    return pd.DataFrame(rows, columns=["date", "price"])

ours_steps, theirs_steps = steps(ours, PERIOD_END), steps(theirs, PERIOD_END)
print(ours_steps.to_string(index=False))

Four price changes become eight points: the first price, then for each later change one point with the old price and one with the new price on the same date, and a final point on 30 September. Joined with straight lines, consecutive points with the same date draw the vertical jump and consecutive points with the same price draw the flat segment. Forgetting the last point is the most common mistake — the chart then stops at the last change, as if the price did not exist afterwards.

Step 3 — A scatter chart on a date axis

A line chart in Excel treats its categories as evenly spaced labels, so two points on the same date would sit side by side. A scatter chart places points by their x value, so both points of a step land on the same date. Write the step points and draw them with straight lines and no markers:

from openpyxl import Workbook
from openpyxl.chart import Reference, ScatterChart, Series
from openpyxl.drawing.line import LineProperties

wb = Workbook()
ws = wb.active
ws.title = "Prices"
ws.append(["Our date", "Our price", "", "Their date", "Their price"])
for i in range(max(len(ours_steps), len(theirs_steps))):
    row = [None] * 5
    if i < len(ours_steps):
        row[0], row[1] = ours_steps["date"][i].to_pydatetime(), ours_steps["price"][i]
    if i < len(theirs_steps):
        row[3], row[4] = theirs_steps["date"][i].to_pydatetime(), theirs_steps["price"][i]
    ws.append(row)
for col in "AD":
    for cell in ws[col][1:]:
        cell.number_format = "dd mmm"

chart = ScatterChart()
chart.title = "List price, ours and competitor's"
chart.style = 13
for x_col, y_col, colour, name in [(1, 2, "1F6FEB", "Ours"), (4, 5, "D4A72C", "Competitor")]:
    count = len(ours_steps) if x_col == 1 else len(theirs_steps)
    xs = Reference(ws, min_col=x_col, min_row=2, max_row=1 + count)
    ys = Reference(ws, min_col=y_col, min_row=2, max_row=1 + count)
    s = Series(ys, xs, title=name)
    s.marker.symbol = "none"
    s.smooth = False
    s.graphicalProperties.line = LineProperties(solidFill=colour, w=31750)
    chart.series.append(s)
chart.x_axis.number_format = "mmm"
chart.x_axis.scaling.min = pd.Timestamp("2026-01-01").to_julian_date() - 2415018.5
chart.x_axis.scaling.max = PERIOD_END.to_julian_date() - 2415018.5
chart.x_axis.majorUnit = 31
chart.y_axis.scaling.min, chart.y_axis.scaling.max = 4.4, 5.2
chart.y_axis.number_format = "0.00"
chart.x_axis.delete = chart.y_axis.delete = False
chart.legend.position = "b"
chart.width, chart.height = 18, 9
ws.add_chart(chart, "A12")
wb.save("step_chart.xlsx")
print("x axis from", chart.x_axis.scaling.min, "to", chart.x_axis.scaling.max)

The x values are real dates, written as Excel date serials with a month format, so the axis spaces them by time: the step on 15 March sits halfway through the month. The axis bounds are set as serial numbers — 46023 for 1 January 2026 and 46295 for 30 September — computed here from pandas timestamps, and a major unit of 31 days gives one tick, labelled with its month, per month — a unit of 30 would label two ticks Jan, because 31 January is still January. smooth = False and marker.symbol = "none" keep the lines straight and clean. Each series has its own x and y columns, so the two price histories can change on different dates. Dates on chart axes in general are covered in Use a Date Axis in an Excel Chart with openpyxl.

Step 4 — Render and check

import subprocess
from PIL import Image

subprocess.run(["soffice", "--headless", "--convert-to", "pdf", "step_chart.xlsx"], check=True, capture_output=True)
subprocess.run(["pdftoppm", "-png", "-r", "80", "-f", "1", "-l", "1", "step_chart.pdf", "step"], check=True)
img = Image.open("step-1.png").convert("RGB")
counts = {colour: n for n, colour in img.getcolors(maxcolors=2_000_000)}
for label, hexcode in [("ours (blue)", "1F6FEB"), ("competitor (gold)", "D4A72C")]:
    rgb = tuple(int(hexcode[i:i + 2], 16) for i in (0, 2, 4))
    print(f"{label:18} {counts.get(rgb, 0):>6} px")

Both series appear in the render, and the chart sits below the data at A12 so it prints whole on the first page rather than being cut at the page edge. Looked at as an image, the blue line holds at 4.50 until mid-March, jumps to 4.80, steps down to 4.65 in June and up to 5.10 in late August, and runs flat to the end of September; the competitor's line steps at its own dates, and the chart shows at a glance that they were cheaper in February and dearer from May until our August increase.

Variations

The same doubled-points technique covers related charts. If the history does not start at the beginning of the period — the first recorded change is in March, but the price before it is known — add a first point at the period start with that opening value, just as the last point extends to the period end. For many items, such as the stock level of each product, build the step points per item with a groupby and give each its own pair of columns, or one chart per item as in Create One Chart per Group with openpyxl. And when the analysis needs the value on every day — to average a price over a quarter, say — expand the change points with a daily reindex and ffill(), as the first code block did to look up 1 March.

Common pitfalls

• Using a line chart. Its sloping lines imply values that never existed.
• Category axes. Two points on one date need a scatter chart's value axis.
• No final point. Add the period end so the last value is drawn.
• Smoothed lines or markers. Turn both off for clean steps.
• Axis bounds as dates. Set them as Excel serial numbers.

Conclusion

For values that hold until they change, build a step chart: duplicate each change date with the previous value and the new value, add a point at the end of the period with the last value, and draw the points as a scatter chart with straight lines, no markers and no smoothing, on a date axis with bounds set as Excel serial numbers. Give each series its own x and y columns so histories with different change dates can share the chart.

Frequently asked questions

How do I make a step chart in Excel? Duplicate each change point so the old and new values share the change date, then plot the points as a scatter chart with straight lines.

Why use a scatter chart rather than a line chart for steps? A line chart spaces categories evenly, so two points on the same date sit apart; a scatter chart places them at the same x value, drawing a vertical jump.

Why does my step chart stop at the last change? Add a final point at the end of the period with the last value so the line continues.

How do I set date axis limits on an openpyxl scatter chart? Set x_axis.scaling.min and max to Excel date serial numbers, for example 46023 for 1 January 2026, and use a date number format on the axis.

Related

• Up one level: Creating Charts in Excel with openpyxl — charts from Python.
• Create a Scatter Chart in Excel with openpyxl — scatter chart basics.
• Create a Slope Chart in Excel with Python — another line-based chart.
• Fill Gaps in Per-Item Time Series with a Staleness Limit — forward-filling values that hold.