Python Excel AutomationPractical guides for developers
How-to
Getting Started With Python Excel AutomationDeep dive

Insert a matplotlib Plot into a Live Workbook with xlwings

Put a matplotlib chart into a workbook that is open in Excel and keep it up to date: read the data with xlwings, build the figure, add it with sheet.pictures.add, place it next to a cell with anchor, replace it in place on every run with name and update=True, choose PNG resolution or SVG, and size it so it looks right on screen and in print.
xlwingsCharts & images

Native Excel charts are great until you need something they do not offer — a box plot with outliers, a small-multiples grid, a regression line with a confidence band, annotations placed exactly where you want them. matplotlib draws all of that, and xlwings can place the figure into a workbook that is open in Excel, as a picture beside the data. The useful trick is not inserting the picture once but refreshing it: give it a name, and every run replaces the previous image at the same position, so a button or a scheduled refresh keeps the chart current. This page covers reading the data, building the figure, inserting and positioning it, keeping it sharp, and refreshing it in place. It belongs to Automating Excel With xlwings: The Basics.

Prerequisites

Bash
pip install xlwings matplotlib pandas

xlwings needs Excel on Windows or macOS. The xlwings calls on this page are marked as not run by our Linux build; the matplotlib code is run, using the same data read with pandas instead of from Excel. The workbook weekly_sales.xlsx has 26 weeks of sales for two regions in columns A to C of its first sheet.

Step 1 — Read the data from the open workbook

Python
import xlwings as xw
import pandas as pd

wb = xw.Book("weekly_sales.xlsx")
sheet = wb.sheets[0]
df = sheet.range("A1").expand().options(pd.DataFrame, index=False, header=True).value

options(pd.DataFrame, ...) converts the expanded range into a DataFrame in one bulk read, which is much faster than reading cells one by one; with index=False the first column stays a column instead of becoming the index. If the data lives in an Excel table, sheet.tables["Sales"].range gives its range regardless of where it sits. For the rest of this page the same frame is read with pandas, so the plotting code can run anywhere:

Python
import pandas as pd

df = pd.read_excel("weekly_sales.xlsx")
print(df.tail(3))

Step 2 — Build the figure

Python
import matplotlib
matplotlib.use("Agg")
import matplotlib.pyplot as plt

fig, ax = plt.subplots(figsize=(7, 3.2), dpi=100)
ax.plot(df["Week"], df["North"], color="#5B5CF0", label="North", linewidth=2)
ax.plot(df["Week"], df["South"], color="#0F9488", label="South", linewidth=2)
ax.fill_between(df["Week"], df["North"].rolling(4).min(), df["North"].rolling(4).max(),
                color="#5B5CF0", alpha=0.12, label="North 4-week range")
best = df.loc[df["North"].idxmax()]
ax.annotate(f"peak {best['North']:,.0f}", (best["Week"], best["North"]),
            xytext=(10, 10), textcoords="offset points", fontsize=8)
ax.set_title("Weekly sales by region", loc="left", fontsize=11)
ax.spines[["top", "right"]].set_visible(False)
ax.legend(frameon=False, fontsize=8, loc="lower right")
fig.tight_layout()
fig.savefig("weekly_sales_chart.png")
print("figure size in pixels:", fig.get_size_inches() * fig.dpi)

The rolling band and the peak annotation are exactly the kind of detail native Excel charts make hard. figsize is in inches; at 100 dots per inch the figure is 700 × 320 pixels, and that ratio decides the picture's shape in Excel. tight_layout keeps labels from being cut off at the edges — a common reason inserted figures look sloppy. The figure object itself, fig, is what xlwings needs; saving a PNG here is only so the build can check it renders.

From cells to a picture in the sheet The data range is read into a DataFrame with xlwings options, a matplotlib figure is drawn, and sheet.pictures.add inserts it with a name, placing it at an anchor cell. On later runs the same name with update=True replaces the image in place. Data range A1.expand() DataFrame options(pd.DataFrame) matplotlib figure fig, ax = plt.subplots() pictures.add(fig, …) named, anchored

Step 3 — Insert it next to the data

Python
pic = sheet.pictures.add(
    fig,
    name="WeeklySalesChart",
    update=True,
    anchor=sheet.range("E2"),
)
print(pic.name, pic.left, pic.top, pic.width, pic.height)

pictures.add accepts a matplotlib figure directly, as well as image file paths. anchor puts the picture's top-left corner on cell E2, two columns to the right of the data, so the position follows the layout rather than fixed points. name identifies the picture for later runs, and update=True says: if a picture with this name already exists, replace its image and keep its position and size. The returned Picture has left, top, width and height in points, which you can set to move or resize it later.

Step 4 — Keep it sharp: resolution, SVG and size

A figure inserted at screen resolution looks soft on high-density displays and in print. Two options:

Python
pic = sheet.pictures.add(fig, name="WeeklySalesChart", update=True, anchor=sheet.range("E2"),
                         export_options={"bbox_inches": "tight", "dpi": 200})
# or, for crisp vector output in recent Excel versions:
pic_svg = sheet.pictures.add(fig, name="WeeklySalesChartSVG", update=True,
                             anchor=sheet.range("E20"), format="svg")
pic.width = 520                       # points; height follows when the aspect ratio is locked

export_options is passed to matplotlib's savefig, so dpi=200 doubles the pixel density and bbox_inches="tight" trims white margins. format="svg" inserts a vector image, which stays sharp at any zoom in Excel versions that support SVG pictures; for older versions, stay with PNG at a higher dpi. Setting width (in points) resizes the picture, and the height follows when its aspect ratio is locked, as it is by default. The relationship between figure size and on-sheet size is the same as in Embed a matplotlib Chart in an Excel Report, which does it for files with openpyxl.

PNG at higher dpi or SVG A PNG at 200 dpi works in every Excel version and looks sharp on most screens but gets soft when zoomed far in. An SVG picture stays sharp at any zoom and in print but needs a recent Excel version. PNG, dpi=200 works in every Excel version sharp on most screens soft when zoomed far in larger file for big figures format="svg" sharp at any zoom and in print small for simple charts needs a recent Excel some effects rasterised

Step 5 — Refresh the chart on every run

Because the picture has a name, a refresh is the same call again. A small function makes that the only thing a button or scheduled job has to do:

Python
def refresh_chart(book_path="weekly_sales.xlsx"):
    wb = xw.Book(book_path)
    sheet = wb.sheets[0]
    df = sheet.range("A1").expand().options(pd.DataFrame, index=False).value
    fig = build_figure(df)                          # the plotting code from Step 2, as a function
    sheet.pictures.add(fig, name="WeeklySalesChart", update=True, anchor=sheet.range("E2"))
    plt.close(fig)                                  # free the figure's memory

plt.close(fig) matters in anything that runs repeatedly: matplotlib keeps every figure alive until it is closed, and a workbook button pressed fifty times would otherwise hold fifty figures in memory. Wiring the function to a button is covered in Run Python from an Excel Button with xlwings.

Python
def build_figure(df):
    fig, ax = plt.subplots(figsize=(7, 3.2), dpi=100)
    for col, colour in (("North", "#5B5CF0"), ("South", "#0F9488")):
        ax.plot(df["Week"], df[col], color=colour, label=col, linewidth=2)
    ax.legend(frameon=False, fontsize=8)
    fig.tight_layout()
    return fig

figs_before = len(plt.get_fignums())
for _ in range(5):
    plt.close(build_figure(df))
print("open figures after five refreshes:", len(plt.get_fignums()) - figs_before)

The local check confirms that building and closing the figure five times leaves no extra figures open.

Data and picture side by side The worksheet with Week, North and South in columns A to C and the WeeklySalesChart picture anchored at cell E2, occupying roughly columns E to L and rows 2 to 18. A B C D E F 1 Week North South 2 06 Apr 2026 1,176 958 WeeklySalesChart 3 13 Apr 2026 1,180 921 (picture anchored 4 20 Apr 2026 1,295 968 at E2) 5 … … … The picture keeps its name and position across refreshes.

Step 6 — Picture or native chart?

A picture is a snapshot: it does not change when someone edits a cell until the script runs again, and readers cannot hover over points to see values. A native Excel chart, created with openpyxl or XlsxWriter in the file or through .api in a live workbook, updates instantly and stays interactive. Use matplotlib pictures for the analyses Excel cannot draw — distributions, annotations, statistical bands, small multiples — and native charts for standard lines and bars that people will explore. Native charts from Python are covered in Creating Charts in Excel with openpyxl.

Performance notes

Inserting a picture is quick — the figure is rendered to a temporary file and Excel loads it — so the time goes into reading the data and drawing the figure. Bulk-reading the range with options(pd.DataFrame) is one round trip; reading cell by cell would be hundreds. For dashboards with several charts, turn off app.screen_updating while all pictures are replaced and switch it back on in a finally block, so Excel redraws once instead of after every picture. Keep figure sizes modest: a simple 7 × 3.2 inch line chart at 200 dpi came to about 40 kB in testing, but photographic content, dense scatter plots and dozens of large high-resolution pictures make the workbook slow to open and to share, the problem described in Reduce the File Size of an Excel Workbook with Python.

Common pitfalls

  • Inserting without a name. Every run adds another picture on top of the last; always pass name and update=True.
  • Blurry images. Raise dpi through export_options or use format="svg".
  • Labels cut off. Call fig.tight_layout() or pass bbox_inches="tight".
  • Memory growth in repeated runs. Close figures with plt.close(fig).
  • Expecting interactivity. A picture does not update with the cells; rerun the script or use a native chart.

Conclusion

Read the data with xlwings into a DataFrame, draw the figure with matplotlib, and add it with sheet.pictures.add(fig, name=..., update=True, anchor=...) so it sits next to the data and is replaced in place on every run. Raise the dpi or insert SVG for sharpness, size the picture in points, close figures after use, and keep native Excel charts for standard visuals that readers explore interactively.

Frequently asked questions

How do I insert a matplotlib figure into Excel with xlwings? Pass the figure to sheet.pictures.add(fig, name="MyChart", update=True, anchor=sheet.range("E2")). The picture is placed at the anchor cell and replaced in place the next time you call it with the same name.

How do I update an existing chart picture instead of adding a new one? Give the picture a name and call pictures.add with the same name and update=True. xlwings replaces the image and keeps the picture's position and size.

Why does my inserted matplotlib chart look blurry in Excel? It was rendered at a low resolution. Pass export_options={"dpi": 200} to pictures.add, or insert it as SVG with format="svg" in Excel versions that support SVG pictures.

Should I use a matplotlib picture or a native Excel chart? Use matplotlib for visuals Excel cannot draw, such as statistical bands, annotations and small multiples. Use native charts when readers need to interact with the chart or when it must update immediately as cells change.

More on xlwings

More on xlwings →