Put a Chart on Its Own Chartsheet with openpyxl

A chart placed on a worksheet shares the grid with data: it covers cells, moves when rows are resized, and prints across page breaks unless someone lays it out carefully. Excel has a second kind of sheet for exactly this — the chartsheet, a tab that holds one chart and nothing else. The chart fills the window at any screen size, cannot be dragged off its data, and prints as a single full page. It is the natural home for the headline chart of a report: put it first, and the workbook opens on the picture, with the numbers a tab away. This page, part of Creating Charts in Excel with openpyxl, creates chartsheets with openpyxl and fixes the one default that makes them look empty outside Excel.

Prerequisites

pip install openpyxl

The examples chart twelve months of revenue for three regions; the data goes on a worksheet named Data.

Step 1 — Create a chartsheet and give it a chart

wb.create_chartsheet(title, index) adds a chartsheet at the given position. Build the chart exactly as for a worksheet — its references point at the Data sheet — and attach it with add_chart. Unlike a worksheet, a chartsheet takes no cell anchor, because the chart occupies the whole sheet:

from openpyxl import Workbook
from openpyxl.chart import LineChart, Reference
from openpyxl.drawing.xdr import XDRPositiveSize2D
from openpyxl.utils.units import cm_to_EMU

months = ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"]
regions = {"North": [42, 45, 48, 51, 55, 58, 54, 52, 60, 63, 66, 72],
           "South": [30, 31, 35, 34, 38, 41, 44, 47, 45, 49, 52, 55],
           "East": [12, 14, 15, 18, 20, 22, 25, 27, 30, 33, 35, 40]}

wb = Workbook(); data = wb.active; data.title = "Data"
data.append(["Month", *regions])
for i, m in enumerate(months):
    data.append([m, *(v[i] for v in regions.values())])

def revenue_chart(title, first_col, last_col):
    chart = LineChart()
    chart.title = title
    chart.add_data(Reference(data, min_col=first_col, max_col=last_col, min_row=1, max_row=13), titles_from_data=True)
    chart.set_categories(Reference(data, min_col=1, min_row=2, max_row=13))
    chart.y_axis.title = "Revenue (k)"
    chart.x_axis.delete = False
    chart.y_axis.delete = False
    return chart

overview = wb.create_chartsheet("Overview", 0)
chart = revenue_chart("Revenue by region, 2026", 2, 4)
overview.add_chart(chart)
chart.anchor.ext = XDRPositiveSize2D(cm_to_EMU(25), cm_to_EMU(17))
print(wb.sheetnames, type(chart.anchor).__name__)

Index 0 puts Overview first, so the workbook's first tab is the chart. The last line matters outside Excel: openpyxl records the chart's size on a chartsheet as zero. Excel ignores it and stretches the chart to fill the sheet, but LibreOffice and other applications draw the chart at its stored size — a dot in the corner of an empty page. Setting the anchor's extent to a real size, in EMUs (English Metric Units, 360,000 per centimetre), makes the sheet look right everywhere; Excel still scales it to the window. The delete = False lines keep the axes visible in Excel, as explained in Format Chart Axes with openpyxl.

Step 2 — One chartsheet per region

Chartsheets suit a set of parallel charts — one per region, product or department — each a tab of its own that readers flip through. Create them in a loop, after the overview:

for position, (name, col) in enumerate(zip(regions, range(2, 5)), start=1):
    sheet = wb.create_chartsheet(f"{name} chart", position)
    region_chart = revenue_chart(f"{name} revenue, 2026", col, col)
    region_chart.legend = None
    sheet.add_chart(region_chart)
    region_chart.anchor.ext = XDRPositiveSize2D(cm_to_EMU(25), cm_to_EMU(17))
print(wb.sheetnames)
print("chartsheets:", [s.title for s in wb.chartsheets], "| worksheets:", [s.title for s in wb.worksheets])

The four chart tabs come first and Data last. wb.chartsheets and wb.worksheets list the two kinds separately, which matters when a later step loops over "all sheets" to format cells — a chartsheet has no cells, and chartsheet["A1"] raises a TypeError. A chartsheet is meant for exactly one chart. openpyxl will accept a second add_chart call, but both charts are anchored at the same corner and drawn on top of each other, so make a new sheet per chart.

Step 3 — Colour the tabs, set printing and protect

Chartsheets have their own property, protection and page-setup objects, which start empty in openpyxl and are created rather than edited:

from openpyxl.chartsheet.properties import ChartsheetProperties
from openpyxl.chartsheet.protection import ChartsheetProtection
from openpyxl.styles.colors import Color
from openpyxl.worksheet.page import PrintPageSetup

for sheet in wb.chartsheets:
    sheet.sheetPr = ChartsheetProperties(tabColor=Color("0B6157" if sheet.title == "Overview" else "5B6780"))
    sheet.pageSetup = PrintPageSetup(orientation="landscape", paperSize=9)
    sheet.sheetProtection = ChartsheetProtection(content=True)
wb.active = 0
wb.save("revenue_chartsheets.xlsx")

from openpyxl import load_workbook
check = load_workbook("revenue_chartsheets.xlsx")
print([(s.title, s.sheetProtection.content) for s in check.chartsheets], "| opens on:", check.active.title)

paperSize=9 is A4 (use 1 for US Letter); landscape suits most charts. Protecting the content stops readers changing the chart's formatting by accident while leaving the data sheet editable — add password=... to ChartsheetProtection for a light deterrent, remembering that sheet passwords are not security. wb.active = 0 makes the workbook open on the Overview tab.

Step 4 — Read and update chartsheets later

openpyxl reads chartsheets and the charts on them back, so a template workbook with chart tabs can be refilled: write new numbers to the Data sheet and save, and every chart updates because it references cells, not copies of values:

wb2 = load_workbook("revenue_chartsheets.xlsx")
ws = wb2["Data"]
for row in ws.iter_rows(min_row=2, max_row=13, min_col=2, max_col=4):
    for cell in row:
        cell.value = round(cell.value * 1.1)
wb2.save("revenue_chartsheets_updated.xlsx")
print([len(s._charts) for s in load_workbook("revenue_chartsheets_updated.xlsx").chartsheets], "chart per chartsheet after saving")

Each chartsheet still carries its chart after the round trip, now showing the updated figures. Changing the range a chart covers — adding a thirteenth month — means rebuilding its references, which is easiest done by recreating the chart. For the same result with xlsxwriter, which writes new files only, use workbook.add_chartsheet() and chartsheet.set_chart(chart); see Add a Chart to an Excel File with xlsxwriter.

When a chartsheet is the right choice

Chartsheets work best for a small number of headline charts that people look at rather than work with: the chart a manager opens the workbook to see, a set of parallel region charts to flip through, a page to print for a meeting. They are the wrong choice when readers need the chart and its numbers side by side, or several small charts compared on one screen — a dashboard sheet with embedded charts does that better, as Building Multi-Sheet Excel Dashboards shows. Many reports use both: a chartsheet first for the headline, then a dashboard worksheet, then the data. Name chart tabs so their purpose is obvious from the tab bar — "Overview" and "North chart" rather than "Chart1" — because a chartsheet has no cells to hold a heading.

Common pitfalls

• Empty-looking chartsheets outside Excel. Set chart.anchor.ext after add_chart.
• Missing axes in Excel. Set delete = False on both axes, as for any openpyxl chart.
• Editing sheet properties that are None. Chartsheet properties, protection and page setup must be created, not modified.
• Looping over every sheet to format cells. Use wb.worksheets; chartsheets have no cells.
• Several charts on one chartsheet. They overlap at the same corner; create another sheet for the next chart.
• Data on the same tab. A chartsheet cannot hold data; reference a worksheet.

Performance notes

Chartsheets add a sheet, a drawing and a chart part each — a few kilobytes. Dozens of chart tabs are fine; hundreds make the tab bar unusable before they make the file slow.

Conclusion

Create a chartsheet with wb.create_chartsheet(title, index), build its chart from references to a data worksheet, attach it with add_chart, and set chart.anchor.ext so applications other than Excel draw it at full size. Loop to make one chartsheet per group, set tab colours, landscape printing and protection by assigning new property objects, and make the headline chart the active first tab. Refill the data sheet later and the chartsheets follow.

Frequently asked questions

What is a chartsheet in Excel? A sheet that holds a single chart and no cells. The chart fills the window, stays in place, and prints on a full page. Its data lives on an ordinary worksheet elsewhere in the workbook.

How do I create a chartsheet with openpyxl? Call wb.create_chartsheet("Title", index) to add the sheet, build the chart from a Reference to data on a worksheet, and attach it with chartsheet.add_chart(chart). A chartsheet holds exactly one chart.

Why does my openpyxl chartsheet look empty in LibreOffice? openpyxl writes the chart's size on a chartsheet as zero. Excel stretches the chart to fit the sheet anyway, but other applications draw it at its stored size. Set chart.anchor.ext to a real size after add_chart so every application shows it.

Can I read or write cells on a chartsheet? No. A chartsheet has no cells, so chartsheet"A1" raises a TypeError. Keep the data on a worksheet, and use wb.chartsheets to tell the two kinds of sheet apart when looping over a workbook.

Related

• Up one level: Creating Charts in Excel with openpyxl — every chart type.
• Add a Chart to an Excel File with xlsxwriter — chartsheets with xlsxwriter.
• Set the Active Sheet and Tab Colour with openpyxl — tabs on worksheets.
• Building Multi-Sheet Excel Dashboards — combining chart and data tabs.