Plot Charts with matplotlib in Python in Excel

Excel's chart gallery covers the everyday cases well and runs out quickly after that: no small multiples, no regression bands, no distribution plots, and a lot of clicking to reproduce the same chart for twelve regions. Python in Excel brings matplotlib and seaborn into the workbook — both are imported by default as plt and sns — so a =PY cell can draw any chart those libraries can. The result arrives as an image, which changes how you size, place and refresh it. This guide from Using Python in Excel covers the whole path from table to finished chart.

Prerequisites

• Excel with Python in Excel available and enabled.
• A table named Sales with Date, Region, Rep and Amount columns.
• Familiarity with matplotlib's figure-and-axes interface helps; everything here uses it explicitly rather than the implicit plt.plot state machine, which makes the figure that is returned unambiguous.

Step 1 — Draw a first chart and return the figure

Create a figure and axes, plot on the axes, and make the figure the last expression of the formula. A monthly revenue line is a good first chart because it needs a resample, which Excel's own charts cannot do.

sales = xl("Sales[#All]", headers=True)
monthly = sales.set_index("Date")["Amount"].resample("MS").sum()

fig, ax = plt.subplots(figsize=(6, 3.2))
ax.plot(monthly.index, monthly.values, marker="o", linewidth=2)
ax.set_title("Revenue by month")
ax.set_ylabel("Amount")
ax.grid(axis="y", alpha=0.3)
fig.tight_layout()
fig

Returning fig explicitly is the habit worth forming. Plotting calls also leave a "current figure" behind that Excel can pick up, but a formula that builds two figures, or that ends with a line that is not the plot, can return something other than the chart you meant. fig.tight_layout() stops axis labels from being clipped at the image edges, which is otherwise the most common cosmetic defect.

Step 2 — Show the image over the grid or inside the cell

By default the formula returns an image object: the cell shows a small image icon and the plot is not yet visible at a useful size. There are two ways to display it.

• Display Plot over Cells — right-click the cell and choose it from the menu, or press Ctrl+Alt+Shift+C. Excel places a copy of the image floating over the sheet, where you can drag it and resize it from its corners.
• Excel Value — switch the output type from the Python output menu next to the formula bar (or with Ctrl+Alt+Shift+M). The image is then drawn inside the cell itself and scales with the cell, so widen the column, increase the row height or merge cells to make it bigger.

In-cell images suit a layout where each row has its own small chart — a sparkline-style trend next to each region, for example — because the image stays attached to its cell. A floating copy suits a dashboard where one large chart should sit in a specific position.

Step 3 — Size and style for the grid

A cell-sized image is small, so charts need to be designed for that. Set figsize to the aspect ratio of the space the chart will occupy, raise the DPI modestly so text stays crisp when Excel scales the image, and strip anything that does not carry information.

sales = xl("Sales[#All]", headers=True)
by_region = sales.groupby("Region")["Amount"].sum().sort_values()

fig, ax = plt.subplots(figsize=(4.5, 2.4), dpi=150)
bars = ax.barh(by_region.index, by_region.values, color="#5b5cf0")
ax.bar_label(bars, labels=[f"{v:,.0f}" for v in by_region.values], padding=3, fontsize=8)
ax.spines[["top", "right"]].set_visible(False)
ax.tick_params(labelsize=8)
ax.set_xlim(0, by_region.max() * 1.18)
ax.set_title("Revenue by region", fontsize=10, loc="left")
fig.tight_layout()
fig

Direct value labels with bar_label replace the axis ticks a reader would otherwise squint at, and set_xlim leaves room for the longest label. A consistent figsize and DPI across every chart in a workbook makes the images line up when they are placed side by side, in the same way a shared style does in Apply a Reusable Style Theme Across an Excel Report.

Step 4 — Use seaborn for statistical charts and small multiples

The charts Excel cannot draw are where Python in Excel is most worth its cost. seaborn builds a grid of panels — one per region — or a distribution with a fitted density in a single call, and its figure-level functions return an object whose .figure attribute is the matplotlib figure to return.

sales = xl("Sales[#All]", headers=True)
sales["Month"] = sales["Date"].dt.to_period("M").dt.to_timestamp()
monthly = sales.groupby(["Region", "Month"], as_index=False)["Amount"].sum()

grid = sns.relplot(
    data=monthly, x="Month", y="Amount", col="Region",
    kind="line", marker="o", height=2.4, aspect=1.2,
)
grid.set_titles("{col_name}")
grid.set_axis_labels("", "Amount")
for ax in grid.axes.flat:
    ax.tick_params(axis="x", labelrotation=45, labelsize=7)
grid.figure.tight_layout()
grid.figure

A small-multiples chart like this replaces a dozen separate Excel charts, and it redraws automatically when the Sales table changes. For a distribution, sns.histplot(data=sales, x="Amount", hue="Region", kde=True) returns axes; take ax.figure from it in the same way.

Step 5 — Know when a native Excel chart is better

A Python chart is an image. That makes it the wrong tool in a few common situations: when readers need to hover for values or filter series, when the workbook must work offline or for people without Python in Excel, and when a chart should update instantly while someone types. In those cases, let Python compute and spill the numbers as Excel values, and build a native chart on the spilled range.

sales = xl("Sales[#All]", headers=True)
(
    sales.pivot_table(index=sales["Date"].dt.to_period("M").astype(str),
                      columns="Region", values="Amount", aggfunc="sum", fill_value=0)
         .rename_axis("Month")
         .reset_index()
)

Set this formula's output to Excel value, select the spilled block, and insert a line chart. Python does the reshaping that would otherwise need a helper pivot table; Excel draws a chart that is interactive and needs no cloud round trip to repaint. The same "compute in Python, chart natively" split is how scripts produce charts in files, as in Add a Line Chart to an Excel Report with Python.

Step 6 — Build a row of trend images with a helper function

In-cell images become powerful when each row of a summary table carries its own small trend: region in column A, total in column B, and a tiny line chart in column C. Writing the same plotting code twelve times would be fragile, so define a helper once in an early cell and call it from one short formula per row.

# Cell A1 (top of the first sheet) — shared data and a plotting helper
sales = xl("Sales[#All]", headers=True)
monthly_by_region = (
    sales.assign(Month=sales["Date"].dt.to_period("M").dt.to_timestamp())
         .groupby(["Region", "Month"])["Amount"].sum()
)

def trend(region: str):
    series = monthly_by_region.loc[region]
    fig, ax = plt.subplots(figsize=(2.4, 0.7), dpi=150)
    ax.plot(series.index, series.values, linewidth=1.6, color="#0f9488")
    ax.fill_between(series.index, series.values, alpha=0.15, color="#0f9488")
    ax.scatter(series.index[-1], series.values[-1], s=12, color="#0f9488", zorder=3)
    ax.axis("off")
    fig.tight_layout(pad=0.1)
    return fig

"helpers ready"

# Cell C5 — one formula per row; A5 holds the region name as ordinary text
trend(xl("A5"))

Set each trend cell's output to Excel Value so the image sits inside it, then give the column a fixed width and the rows a fixed height so every chart has the same proportions. ax.axis("off") removes ticks and frames, which are unreadable at this size anyway, and the dot on the last point draws the eye to the current value. Because the helper reads from monthly_by_region, the data is aggregated once rather than once per row — and because the calculation order runs across each row and down, the helper in row 1 always exists before row 5 needs it.

Common pitfalls

Performance and scale notes

Every recalculation redraws the chart in the cloud and sends a new image back, so a sheet with many chart cells recalculates noticeably more slowly than one with a few. Aggregate before plotting — a line of twelve monthly points draws faster and reads better than 50,000 raw points — and switch to Partial calculation while editing a chart-heavy workbook. Remember too that the data behind any single calculation is capped at 100 MB, which a row-level scatter of a very large table can approach.

Conclusion

Build charts with explicit figures and axes, finish the formula with the figure, and choose the display that suits the layout: a floating copy for a dashboard, an in-cell image for a row-aligned table of charts. Design for the small space with deliberate size, DPI and direct labels. Use seaborn where Excel's gallery runs out — small multiples, distributions and statistical overlays — and when a chart must be interactive or work without Python, spill the numbers as Excel values and draw a native chart on them.

Frequently asked questions

Do I need to import matplotlib or seaborn? No. Python in Excel imports matplotlib.pyplot as plt and seaborn as sns by default, and lists those imports in the Initialization pane on the Formulas tab.

Can I call plt.show()? There is no window to show a plot in. Return the figure object as the last expression instead; that is what Excel turns into the image.

Can I copy a Python chart into PowerPoint? Yes. Display the plot over cells, then copy the floating image like any other picture. It is a static image, so it does not update in the presentation.

Can I use plotly or other interactive libraries? Interactive output does not carry into the grid, because Excel receives an image. Stick to matplotlib-based libraries for charts in cells.

Related

• Up one level: Using Python in Excel — where =PY charts fit among Excel automation options.
• Return DataFrames and Values from Python in Excel Cells — output types and spilling in detail.
• Embed a matplotlib Chart in an Excel Report — the same charts, inserted into a file by a script.
• Creating Charts in Excel with openpyxl — native, interactive charts written from Python.
• Python in Excel vs openpyxl and xlwings — choosing between in-cell Python and scripts.