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

Embed a matplotlib Chart in an Excel Report

Render a figure to PNG and anchor it in a worksheet: sizing and DPI that stay sharp, in-memory buffers so nothing hits the disk, keeping the source data on a sheet, and when a native chart is the better answer.

Excel's own charts cover the common shapes well, and where they do, a native chart is the better answer because it recalculates when a reader edits the numbers. But some plots have no Excel equivalent — a heatmap, a distribution, a regression fit, a grid of small multiples — and for those, rendering with matplotlib and embedding the result is the practical route.

This guide covers the mechanics that matter: getting the figure into the workbook without touching the disk, sizing it so it is sharp rather than blurry, and keeping the numbers on a sheet so the picture is not the only record. It is part of Inserting Images and Logos into Excel.

From DataFrame to an image anchored in a worksheet The DataFrame is plotted with matplotlib using the Agg backend, saved as a PNG into an in-memory buffer, wrapped as an openpyxl image and anchored at a cell. The same DataFrame is also written to a data sheet, so the workbook still contains the numbers behind the picture. DataFrame the numbers matplotlib Agg backend BytesIO PNG in memory anchored at a cell ws.add_image(img, "E2") the same numbers also go onto a data sheet — an image alone is not a record

Prerequisites

Bash
pip install matplotlib openpyxl pandas

matplotlib pulls in a plotting stack of a few tens of megabytes, which is worth noting if the script is destined for a packaged executable — it is one of the biggest single contributors to bundle size.

Step 1: Render without a display

On a server or in a scheduled job there is no window system, and matplotlib's default backend will try to find one. Select the file-only Agg backend before importing pyplot:

Python
import io

import matplotlib
matplotlib.use("Agg")                    # must come before pyplot is imported
import matplotlib.pyplot as plt
import pandas as pd

df = pd.DataFrame({
    "month": pd.period_range("2026-01", periods=12, freq="M").astype(str),
    "actual": [412, 388, 501, 466, 590, 623, 641, 598, 655, 702, 688, 731],
    "budget": [400, 400, 480, 480, 560, 600, 620, 620, 660, 680, 700, 720],
})


def render_chart(frame, width_in=8.0, height_in=4.0, dpi=150):
    """Return a PNG of the figure in an in-memory buffer."""
    fig, ax = plt.subplots(figsize=(width_in, height_in), dpi=dpi)
    ax.plot(frame["month"], frame["actual"], marker="o", linewidth=2,
            color="#5B5CF0", label="Actual")
    ax.plot(frame["month"], frame["budget"], linestyle="--", linewidth=2,
            color="#0F766E", label="Budget")
    ax.fill_between(frame["month"], frame["actual"], frame["budget"],
                    where=frame["actual"] >= frame["budget"],
                    color="#0F766E", alpha=0.12)
    ax.set_ylabel("Revenue (£000)")
    ax.legend(frameon=False)
    ax.grid(axis="y", color="#E4E8F2")
    ax.spines[["top", "right"]].set_visible(False)
    fig.autofmt_xdate(rotation=45)
    fig.tight_layout()

    buffer = io.BytesIO()
    fig.savefig(buffer, format="png", dpi=dpi)
    plt.close(fig)                       # release the figure, or memory climbs
    buffer.seek(0)
    return buffer

plt.close(fig) is the line that matters in a loop. matplotlib keeps every figure alive until it is closed, so a job generating one chart per region leaks them all and eventually warns about too many open figures — shortly before it runs out of memory.

Returning a buffer rather than a path keeps the whole operation in memory: no temporary files to clean up, no permissions to worry about, nothing left behind if the job fails halfway.

Step 2: Anchor the image with openpyxl

Python
from openpyxl import Workbook
from openpyxl.drawing.image import Image as XLImage

wb = Workbook()
data_ws = wb.active
data_ws.title = "Data"
data_ws.append(list(df.columns))
for row in df.itertuples(index=False):
    data_ws.append(list(row))

chart_ws = wb.create_sheet("Chart", 0)          # first tab
buffer = render_chart(df)

img = XLImage(buffer)
img.anchor = "B3"
chart_ws.add_image(img, "B3")
chart_ws["B1"] = "Actual against budget, 2026"
chart_ws.column_dimensions["A"].width = 3

wb.save("with-chart.xlsx")

openpyxl accepts a file-like object, so the BytesIO buffer goes straight in. One caveat with in-memory images: openpyxl reads the buffer when the workbook is saved, not when add_image is called, so the buffer must still be open at save time — do not close it in between, and do not reuse one buffer for two images.

Writing the numbers to a Data sheet alongside is not optional in practice. An image is a picture; the first question anyone asks about a chart is "what was the number for June", and a workbook that cannot answer it sends them back to you.

Step 3: Size it so it stays sharp

An image inserted into Excel is placed at its pixel dimensions. Those come from figsize × dpi, so a figure at 8×4 inches and 150 DPI is 1200×600 pixels:

Python
buffer = render_chart(df, width_in=8.0, height_in=4.0, dpi=150)   # 1200 x 600 px
img = XLImage(buffer)
img.width = 800          # displayed size in pixels
img.height = 400
chart_ws.add_image(img, "B3")

Rendering larger than the display size and scaling down keeps the chart crisp on a high-DPI screen and when printed; scaling up is what produces the soft, blurry chart people complain about. A 1.5× ratio between rendered and displayed pixels is a good default.

Set the figure's font sizes explicitly if the chart will be shown small — matplotlib's defaults are sized for a full-screen figure and become unreadable at 400 pixels wide:

Python
plt.rcParams.update({"font.size": 11, "axes.titlesize": 13,
                     "axes.labelsize": 11, "legend.fontsize": 10})
Rendering above the display size versus below it A figure rendered at 1200 by 600 pixels and displayed at 800 by 400 has more pixels than it needs, so it stays sharp on a high-resolution screen and in print. A figure rendered at 600 by 300 and displayed at 800 by 400 is scaled up, and the text and lines soften. render 1200×600, display 800×400 rendered pixels displayed area detail to spare — sharp on screen and in print costs a slightly larger file, and nothing else render 600×300, display 800×400 rendered pixels displayed area, larger than what was drawn scaled up — text and thin lines soften this is what "the chart looks blurry" means

Step 4: The same thing with xlsxwriter

If the report is being generated with xlsxwriter, the in-memory route uses image_data:

Python
import xlsxwriter

wb = xlsxwriter.Workbook("with-chart.xlsx")
ws = wb.add_worksheet("Chart")
buffer = render_chart(df)

ws.insert_image("B3", "trend.png", {
    "image_data": buffer,           # the buffer, not a file on disk
    "x_scale": 0.67, "y_scale": 0.67,
    "object_position": 1,           # move and size with cells
})
wb.close()

The filename argument is still required even with image_data — xlsxwriter uses it only to name the part inside the archive and to infer the format, so it can be any sensible name.

object_position controls what happens when rows and columns are resized: 1 moves and sizes with cells, 2 moves but does not size, 3 does neither. For a chart beside a table whose columns a reader might widen, 2 is usually the least surprising.

Step 4b: One figure, many sheets

Where the same chart belongs on several sheets — a trend line repeated at the top of each regional tab — render it once and insert the same bytes repeatedly. Rendering per sheet costs a tenth of a second each and produces a larger file, because every insertion carries its own copy of the image:

Rendering once against rendering per sheet Rendering the figure once and reusing the PNG bytes across six sheets costs one render and one stored image. Calling the render function inside the loop costs six renders and six copies of the same picture in the workbook. render once, insert six times one render · one PNG 0.1 s and one stored image render inside the loop six identical renders six copies of the same picture in the file 0.6 s, and a workbook several times larger

With openpyxl, give each insertion its own XLImage wrapping a fresh BytesIO built from the same bytes — one buffer cannot be read twice — while xlsxwriter deduplicates identical images itself, so passing the same image_data to several insert_image calls stores it once.

Step 5: Choose the image only when it earns it

A rendered image cannot be restyled, does not recalculate, and does not tell the reader which cells it came from. Those are real costs, so use it where matplotlib does something Excel cannot:

PlotNative Excel chart?Verdict
Column, bar, line, pie, scatterYesUse a native chart — it recalculates
Heatmap of a matrixNoImage, or conditional formatting on the cells
Distribution, histogram with a fitted curvePartlyImage
Small multiples, a grid of facetsNoImage
Regression fit with a confidence bandNoImage
Anything the reader will editYesNative chart, always

The middle ground worth knowing about: for a matrix heatmap, applying a colour scale to the actual cells gives you a heatmap made of real numbers, which is better than a picture of one on every count.

Common pitfalls and gotchas

SymptomCauseFix
Crash or hang on a serverInteractive backend, no displaymatplotlib.use("Agg") before importing pyplot
Memory grows across a batchFigures never closedplt.close(fig) after each save
Image missing from the saved fileBuffer closed before wb.save()Keep the buffer open until after the save
The same image appears twiceOne buffer reused for two insertionsRender into a fresh buffer per image
Blurry chartRendered smaller than displayedRender at 1.5× the display size
Labels unreadableDefault font sizes on a small figureSet rcParams sizes explicitly
Image covers the tableAnchored inside the data rangeAnchor to the right of the last column
Workbook is hugeSeveral high-DPI PNGsLower the DPI, or reuse one chart per sheet

Performance and scale notes

Rendering is the slow part: a simple matplotlib figure takes on the order of a hundred milliseconds, so a report with a chart per region across forty regions spends several seconds purely in rendering. If the same chart appears on several sheets, render it once and insert the same PNG bytes repeatedly rather than re-plotting.

File size grows with DPI squared, so a 300-DPI chart is four times the bytes of a 150-DPI one for a difference nobody sees on screen. 150 is a good default; go to 200 only when the workbook is genuinely going to be printed.

Conclusion

Render with the Agg backend, save into a BytesIO buffer, keep that buffer open until the workbook is saved, and close every figure so a batch does not leak. Render at about 1.5 times the display size so the result is sharp rather than soft, set the font sizes for the size it will actually appear at, and write the underlying numbers to a data sheet so the picture is not the only record. Then check the plot is one Excel could not have drawn — if it is a column chart, a native chart serves the reader better.

Frequently asked questions

Should I embed an image or build a native Excel chart? A native chart when the reader may edit the numbers or wants to restyle it — it recalculates. An image when the plot is something Excel cannot draw, such as a heatmap, a regression fit or a small-multiples grid.

Do I need to write the PNG to disk first? No. Save the figure into an io.BytesIO buffer and pass that to openpyxl's Image or xlsxwriter's insert_image with the image_data argument. Nothing touches the filesystem.

Why does the image look blurry in Excel? It was rendered at screen DPI and then scaled up. Render at dpi=150 or 200 and set the figure size in inches so the pixel dimensions match the space it will occupy.

Does the image resize when someone changes a column width? With openpyxl the anchor is a cell reference and the image keeps its size. Excel's own move-and-size behaviour is not something openpyxl exposes, so treat the placement as fixed.

Up to the parent guide:

Related guides: