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

Create a Scatter Chart in Excel with openpyxl

Plot one numeric column against another with openpyxl: ScatterChart and Series, markers without lines, axis titles and scaling, a trendline, and multiple series.

A scatter chart is the right choice whenever both variables are numeric — price against volume, headcount against cost, forecast against actual — because it places points by value rather than treating one axis as evenly spaced categories. openpyxl builds one from a worksheet's own cells, so the chart stays live: edit the numbers and it redraws. This guide plots a single series, styles it as markers, adds axis titles and a trendline, then extends to several series on one chart. It belongs to Creating Charts in Excel with openpyxl.

Scatter versus line: what each axis means A scatter chart positions each point by its numeric x value so uneven gaps are visible, while a line chart spaces categories evenly and hides the gaps. scatter: x is a number line: x is a label the gap between 140 and 255 is real evenly spaced — the gap disappears

Prerequisites

Bash
pip install openpyxl

Write data and add a scatter chart

A chart references cells, so the data has to be on the sheet first:

Python
"""Plot marketing spend against revenue."""
from openpyxl import Workbook
from openpyxl.chart import Reference, ScatterChart, Series

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

ws.append(["spend", "revenue"])
for spend, revenue in [(1200, 8400), (1800, 11200), (2400, 13100),
                       (5200, 24800), (6100, 26400), (9800, 31200)]:
    ws.append([spend, revenue])

chart = ScatterChart()
chart.title = "Revenue against marketing spend"
chart.style = 13
chart.x_axis.title = "Spend (GBP)"
chart.y_axis.title = "Revenue (GBP)"

x_values = Reference(ws, min_col=1, min_row=2, max_row=ws.max_row)
y_values = Reference(ws, min_col=2, min_row=1, max_row=ws.max_row)
series = Series(y_values, x_values, title_from_data=True)
chart.series.append(series)

ws.add_chart(chart, "D2")
wb.save("scatter.xlsx")

Note the asymmetry in the two Reference calls, which is the single most common source of an off-by-one chart. The y-values range starts at row 1 because title_from_data=True consumes that first cell as the series name; the x-values range starts at row 2 because it is pure data.

Show markers, not lines

By default openpyxl joins the points, which turns a scatter chart back into a line chart. Two lines fix it:

Python
from openpyxl.chart.marker import Marker

series.marker = Marker(symbol="circle", size=7)
series.graphicalProperties.line.noFill = True      # points only

Available symbols include circle, square, diamond, triangle, x and star. Use a different symbol per series when a chart carries more than one, so it stays readable in a monochrome printout.

Title the axes and control the range

Unlabelled axes make a chart unusable to anyone who did not build it:

Python
chart.x_axis.title = "Spend (GBP)"
chart.y_axis.title = "Revenue (GBP)"
chart.x_axis.scaling.min = 0
chart.y_axis.scaling.min = 0
chart.height = 9          # centimetres
chart.width = 16

Anchoring both axes at zero matters for honesty as much as clarity: an axis starting at 8,000 exaggerates differences that a full-range view shows as marginal. Set scaling.max too when successive months' reports should be visually comparable, otherwise Excel rescales each one and a flat trend looks dramatic.

How the Reference ranges map onto the sheet The x-values reference covers the data rows only, while the y-values reference includes the header cell because title_from_data consumes it as the series name. Two ranges, two different starting rows A1: spend B1: revenue A2: 1200 B2: 8400 A3: 1800 B3: 11200 x values start at row 2; y values start at row 1, where the header becomes the series name Start both at row 1 and the first point plots the header as zero

Add a trendline

Excel computes the trendline itself, so the chart stays correct if the data changes:

Python
from openpyxl.chart.trendline import Trendline

series.trendline = Trendline(trendlineType="linear", dispEq=True, dispRSqr=True)

trendlineType accepts linear, poly (with order), exp, log, power and movingAvg (with period). Displaying the equation and R² is worth doing when the chart is making an argument — a visible R² of 0.31 stops a reader over-reading a line that fits nothing.

Plot several series on one chart

Python
from openpyxl.chart import Reference, ScatterChart, Series
from openpyxl.chart.marker import Marker

chart = ScatterChart()
chart.title = "Spend against revenue by region"
chart.x_axis.title = "Spend (GBP)"
chart.y_axis.title = "Revenue (GBP)"

x_values = Reference(ws, min_col=1, min_row=2, max_row=ws.max_row)
for col, symbol in ((2, "circle"), (3, "diamond"), (4, "triangle")):
    values = Reference(ws, min_col=col, min_row=1, max_row=ws.max_row)
    series = Series(values, x_values, title_from_data=True)
    series.marker = Marker(symbol=symbol, size=7)
    series.graphicalProperties.line.noFill = True
    chart.series.append(series)

ws.add_chart(chart, "F2")

All three series share one x-axis column here. When each series has its own x values — different campaigns measured at different spends — build a separate Reference per series and pass it as the second argument.

Build the sheet from a DataFrame

In a real report the data comes from pandas, so write it first and chart the written range:

Python
import pandas as pd
from openpyxl import load_workbook
from openpyxl.chart import Reference, ScatterChart, Series

df = pd.DataFrame({"spend": [1200, 1800, 2400, 5200], "revenue": [8400, 11200, 13100, 24800]})
df.to_excel("campaigns.xlsx", index=False, sheet_name="Campaigns")

wb = load_workbook("campaigns.xlsx")
ws = wb["Campaigns"]

chart = ScatterChart()
chart.title = "Revenue against spend"
x_values = Reference(ws, min_col=1, min_row=2, max_row=ws.max_row)
y_values = Reference(ws, min_col=2, min_row=1, max_row=ws.max_row)
chart.series.append(Series(y_values, x_values, title_from_data=True))
ws.add_chart(chart, "D2")
wb.save("campaigns.xlsx")

Writing with pandas and charting with openpyxl afterwards is the normal division of labour, since to_excel has no chart support of its own.

Style the chart to match the report

A generated chart that clashes with the workbook's palette looks like it came from somewhere else. openpyxl exposes the underlying drawing properties, so the marker and trendline colours can follow the same theme as the rest of the report:

Python
from openpyxl.chart.marker import Marker
from openpyxl.drawing.fill import PatternFillProperties, ColorChoice
from openpyxl.drawing.line import LineProperties

BRAND = "5B5CF0"
ACCENT = "0F9488"

series.marker = Marker(symbol="circle", size=8)
series.marker.graphicalProperties.solidFill = BRAND
series.marker.graphicalProperties.line.solidFill = BRAND
series.graphicalProperties.line.noFill = True

series.trendline.graphicalProperties = series.trendline.graphicalProperties or None
chart.style = None                      # stop Excel's own style overriding the colours

Setting chart.style = None matters: an Excel chart style can override explicit colours, so a chart that looked right in code comes out in the default blue. Define the hex codes once in a constants module and reuse them across every chart and cell format in the report — the approach in Apply a reusable style theme across an Excel report.

Label the interesting points

A scatter chart with forty unlabelled points invites the question "which one is that?". Excel can label every point, which is usually too much, so label only the outliers by putting their names in a helper column and adding a second, labelled series:

Python
from openpyxl.chart.label import DataLabelList

series.dLbls = DataLabelList()
series.dLbls.showVal = False
series.dLbls.showSerName = False
series.dLbls.showCatName = True     # the x-value column supplies the name

For a genuinely selective approach, split the data into two series — "typical" and "notable" — and enable labels on the second only. That also lets the notable points carry a different marker colour, which does more for comprehension than any amount of labelling.

Labelling every point versus labelling the notable ones Labels on all points overlap into an unreadable cluster, while splitting the data into a plain series and a highlighted series labels only what the reader needs to identify. every point labelled two series, one labelled labels collide and overlap the reader gives up plain markers for the cluster named markers for the outliers The second version answers the question the chart was drawn to raise

Common pitfalls and gotchas

  • Lines you did not ask for. Set line.noFill = True on every series in a scatter chart.
  • Off-by-one ranges. The y reference includes the header when title_from_data=True; the x reference does not.
  • Text in a numeric column. A single "n/a" makes Excel drop the point silently. Coerce with pd.to_numeric(..., errors="coerce") first.
  • A chart anchored over the data. add_chart(chart, "D2") places the top-left corner there; pick a cell clear of the table.
  • Charts lost on a template round trip. openpyxl drops charts it did not create when re-saving some files — see Keep charts and images when filling an Excel template.

Performance and scale notes

A native chart stores references, not pixels, so the file grows by a few kilobytes regardless of how many points it plots. Excel's rendering is the limit instead: a scatter series of more than a few thousand points is slow to draw and impossible to read. Aggregate or sample before charting — a hex-bin style summary, or a sample plus a trendline over the full data — and keep the raw rows on a separate sheet. If the visual is genuinely dense, render it with matplotlib and embed the image instead, as covered in Embed a matplotlib chart in an Excel report.

Conclusion

A scatter chart takes two Reference ranges, a Series and an add_chart call — with the header row included in the y range and excluded from the x range. Turn off the connecting line, name both axes, anchor the scales when reports must be comparable, and add a trendline with its R² when the chart is arguing for a relationship. Above a few thousand points, aggregate first or embed an image instead.

Frequently asked questions

Why does my scatter chart draw lines between the points? openpyxl's default series style joins the markers. Set series.graphicalProperties.line.noFill = True and give the series an explicit marker symbol so only the points render.

Why is the chart empty when I open the file? Usually the Reference ranges point at the wrong rows — remember the y range includes the header only when title_from_data=True — or the values column contains text rather than numbers.

What is the difference between a scatter chart and a line chart? A scatter chart plots numeric x against numeric y, so the horizontal spacing reflects the values. A line chart treats the categories as evenly spaced labels, which misrepresents uneven intervals.

Can I add a trendline? Yes. Assign a Trendline object to the series — openpyxl supports linear, polynomial, exponential and moving-average types, and Excel computes it on open.

How do I control the axis range? Set chart.x_axis.scaling.min and .max (and the same on y_axis). Leaving them unset lets Excel choose, which is usually fine but can hide an outlier's context.