Python Excel AutomationPractical guides for developers
How-to
Formatting And Charting Excel Reports With PythonDeep dive

Create One Chart per Group with openpyxl

Turn a long table — sales by region and month — into a grid of small charts, one per region, generated in a loop: write each group as its own block, build a chart that references only that block, lay the charts out in a grid with computed anchors, give every chart the same axis scale so they can be compared, or put each group on its own sheet with its chart.
Charts & imagesMany sheets & many files

One chart with twelve lines on it — one per region — is unreadable; twelve small charts with one line each, arranged in a grid, are easy to scan. That arrangement, often called small multiples, is a standard way to compare groups, and building it by hand in Excel is exactly the kind of repetitive work a script should do. openpyxl can create any number of native charts in a loop. The work is in three details: each chart must reference only its own group's rows, the charts must be positioned so they do not overlap, and — the one most often missed — every chart must use the same axis scale, or the eye compares shapes that are not comparable. This page belongs to Creating Charts in Excel with openpyxl.

Prerequisites

Bash
pip install openpyxl pandas

regional_sales.xlsx is a long table with one row per region and month: six regions, nine months. LibreOffice (soffice) is used at the end to render the sheet for a quick visual check; it is optional.

Step 1 — Write each group as its own block

A chart references a rectangular range, so each region's data needs to sit in a contiguous block. Pivoting to one column per region is the compact way:

Python
import pandas as pd
from openpyxl import Workbook

sales = pd.read_excel("regional_sales.xlsx")
wide = sales.pivot(index="Month", columns="Region", values="Sales")
regions = list(sales["Region"].unique())
wide = wide[regions]

wb = Workbook()
ws = wb.active
ws.title = "Data"
ws.append(["Month", *regions])
for month, row in wide.iterrows():
    ws.append([month, *row.tolist()])
print(ws.dimensions, "|", [c.value for c in ws[1]])

The data sheet now has months down column A and one column per region. Each chart will use column A for its categories and one region's column for its values. Keeping the data on its own sheet and the charts on another keeps the dashboard tidy and lets people inspect the numbers. Reshaping long data to wide is covered in Create a Pivot Table from Excel with pandas.

A long table becomes one column per group On the left the long table with Region, Month and Sales rows. On the right a wide block with months down the side and one column per region, so each region's values form a contiguous range a chart can reference. A B C 1 Region Month Sales 2 North 2026-01 1276 3 North 2026-02 1376 4 South 2026-01 930 5 South 2026-02 946 A B C D 1 Month North South East 2 2026-01 1276 930 1498 3 2026-02 1376 946 1685 4 … … … … long one block per region pivot

Step 2 — One chart per column, in a loop

Python
from openpyxl.chart import LineChart, Reference

n_months = len(wide)
y_max = float(wide.max().max()) * 1.1             # one scale for every chart

def region_chart(col_idx, title):
    chart = LineChart()
    chart.title = title
    chart.add_data(Reference(ws, min_col=col_idx, min_row=1, max_row=n_months + 1), titles_from_data=True)
    chart.set_categories(Reference(ws, min_col=1, min_row=2, max_row=n_months + 1))
    chart.legend = None
    chart.y_axis.scaling.min = 0
    chart.y_axis.scaling.max = round(y_max, -2)
    chart.x_axis.delete = False
    chart.y_axis.delete = False
    chart.width, chart.height = 9, 5.5
    for s in chart.series:
        s.smooth = False
    return chart

charts = [region_chart(i, name) for i, name in enumerate(regions, start=2)]
print(len(charts), "charts; shared y-axis max:", round(y_max, -2))

Each chart takes one region's column (from row 1, so the header becomes the series name) and the shared month column as categories. The legend is dropped because the title already names the single series. The two lines that matter most are the shared axis limits: every chart runs from 0 to the same maximum, rounded to the nearest hundred. Without them, Excel scales each chart to its own data, and West's flat line at 600 would look as dramatic as East's line at 1,500 — the most common way small multiples mislead. smooth = False keeps openpyxl's line series from being drawn as curves, a default noted in Add a Line Chart to an Excel Report with Python.

Step 3 — Lay the charts out in a grid

Charts are anchored to a cell. Compute the anchor for each position in a grid of, say, three columns:

Python
from openpyxl.utils import get_column_letter

dash = wb.create_sheet("Charts", 0)
dash["A1"] = "Sales by region, January–September 2026 (same scale on every chart)"
COLS, CELL_W, CELL_H = 3, 6, 12                   # charts per row; columns and rows per chart slot
for i, chart in enumerate(charts):
    row_slot, col_slot = divmod(i, COLS)
    anchor = f"{get_column_letter(1 + col_slot * CELL_W)}{3 + row_slot * CELL_H}"
    dash.add_chart(chart, anchor)
    print(f"{regions[i]:8} -> {anchor}")
wb.active = 0
wb.save("regional_small_multiples.xlsx")

divmod(i, 3) turns the chart number into a grid position — row 0, columns 0–2, then row 1 — and each slot is six columns wide and twelve rows tall, a little more than the chart's own size, so neighbouring charts never overlap. The title row above the grid states that the scales are shared, which tells readers they may compare heights directly. Making the chart sheet the active one means the file opens on the dashboard.

Chart anchors in a three-column grid The Charts sheet with six chart slots. North is anchored at A3, South at G3 and East at M3 in the first row; West at A15, Central at G15 and Coast at M15 in the second row. Each slot is six columns wide and twelve rows tall. col slot 0 col slot 1 col slot 2 row slot 0 North · A3 South · G3 East · M3 row slot 1 West · A15 Central · G15 Coast · M15

Step 4 — Verify what was written

Reload the file and confirm each chart points at its own column and shares the scale:

Python
from openpyxl import load_workbook

check = load_workbook("regional_small_multiples.xlsx")
for chart in check["Charts"]._charts:
    series = chart.series[0]
    print(series.tx.strRef.f, series.val.numRef.f, "max", chart.y_axis.scaling.max)

Every chart reads a different column — $B, $C, $D… — with the same nine rows, and every axis maximum is the same. A test asserting these two properties protects the dashboard against an off-by-one in the loop, which would otherwise show one region's numbers under another region's title. For a visual check, render the chart sheet to PDF with LibreOffice as in Convert Excel to PDF on Linux with LibreOffice.

Step 5 — Variant: one sheet per group

When each group's owner should get "their" sheet — a tab per region with its table and its chart — write the group's rows to a new sheet and put the chart beside them:

Python
wb2 = Workbook()
wb2.remove(wb2.active)
for region, group in sales.groupby("Region", sort=False):
    sh = wb2.create_sheet(region)
    sh.append(["Month", "Sales"])
    for row in group[["Month", "Sales"]].itertuples(index=False):
        sh.append(list(row))
    chart = LineChart()
    chart.title = f"{region} sales"
    chart.add_data(Reference(sh, min_col=2, min_row=1, max_row=len(group) + 1), titles_from_data=True)
    chart.set_categories(Reference(sh, min_col=1, min_row=2, max_row=len(group) + 1))
    chart.y_axis.scaling.min, chart.y_axis.scaling.max = 0, round(y_max, -2)
    chart.x_axis.delete = chart.y_axis.delete = False
    chart.legend = None
    sh.add_chart(chart, "D2")
wb2.save("regional_sheets.xlsx")
print(load_workbook("regional_sheets.xlsx").sheetnames)

Each sheet holds one region's rows and a chart that references them — with the same shared maximum, so a manager flipping between tabs sees comparable charts. Splitting the sheets into separate files for distribution is covered in Save Each Sheet of a Workbook as a Separate File.

A grid on one sheet or a sheet per group A grid of small charts on one sheet is best for comparing groups at a glance. One sheet per group with its own table and chart is best when each group's owner should see only their data, and it splits easily into separate files. Grid on one sheet compare all groups at a glance one page to print data lives on a separate sheet One sheet per group owner sees table and chart together easy to split into files comparing groups means flipping tabs

Performance notes

Each chart is a small XML part, so dozens of charts add little to the file size or to the time it takes to write it — six charts here write in a fraction of a second. The practical limits are human and visual: Excel redraws every chart on a sheet when it opens or recalculates, so a sheet with a hundred charts feels sluggish, and nobody reads a hundred charts anyway. Keep the grid to the groups that matter — the top twelve by revenue, say, plus an "all other" chart — and move the rest to a detail sheet or a separate file. If the data has thousands of groups, a heatmap table often communicates better than any number of charts; see Turn a Pivot Table into a Heatmap in Excel.

Common pitfalls

  • Independent axis scales. Each chart auto-scales to its own data; set the same min and max everywhere.
  • Overlapping charts. Compute anchors from a grid larger than the chart size.
  • Off-by-one references. Header row 1, data from row 2; check each chart's ranges after writing.
  • Smoothed lines. openpyxl line series are smoothed unless smooth = False.
  • Too many charts on one sheet. Beyond a dozen or so, split by sheet or filter to the groups that matter.

Conclusion

Pivot the long data so each group is a contiguous column, create one chart per column in a loop with its own value range and the shared category range, and give every chart the same axis limits so they can be compared. Place the charts in a grid with anchors computed from divmod, make the chart sheet active, verify each chart's ranges in a test, and use one sheet per group when the charts are meant for different owners.

Frequently asked questions

How do I create a chart for each group in a DataFrame with openpyxl? Pivot the data so each group has its own column, then loop over the columns and create a chart per column with Reference objects for that column's values and the shared category column. Add each chart at its own anchor cell.

How do I place many charts in a grid without overlapping? Compute each chart's anchor from its position: row and column slot from divmod(index, charts_per_row), multiplied by a slot size in columns and rows that is larger than the chart.

How do I give all small charts the same y-axis scale? Set chart.y_axis.scaling.min and chart.y_axis.scaling.max to the same values on every chart, typically zero and the overall maximum plus a margin.

How do I put each group on its own sheet with its chart? Group the DataFrame, create a sheet per group, write the group's rows, and add a chart that references that sheet's range. Use the same axis limits so the sheets are comparable.

More on charts & images

More on charts & images →