Fix openpyxl Chart Not Showing in Excel
The script runs without error, the workbook saves, and the sheet has no chart on it. Or there is a chart frame with axes and no data:
from openpyxl.chart import BarChart, Reference
chart = BarChart()
chart.add_data(Reference(sheet, min_col=2, min_row=1, max_row=10))
sheet.add_chart(chart, "E5")
book.save("out/report.xlsx")
# Excel opens: no chart, or an empty plot area
openpyxl does not render anything, so it cannot warn you that a chart is empty or misplaced. It writes chart XML exactly as instructed; Excel then renders that XML, and an invalid reference produces an empty frame while a lost chart usually means the workbook object that was saved is not the one the chart was added to.
Root Cause
Four failures account for nearly all of these, and they are distinguishable by what appears on screen.
An empty plot area means the Reference range points at cells with no numeric values — commonly because min_row included the header while titles_from_data was left off, or the range is on a different worksheet than the one being referenced.
No chart at all means the chart was added to a worksheet object that is not part of the saved workbook: a sheet fetched before pandas rewrote it, or a second load_workbook call producing a separate object graph.
A chart that appears in some readers and not others usually means it was written into a workbook opened with keep_vba or in a format that cannot carry charts, such as .csv or a .xlsx written by a library that strips drawing parts.
And a chart that vanishes on the second run is the classic openpyxl round-trip loss: load_workbook does not preserve existing charts, so re-saving a workbook silently discards every chart it already had.
Minimal Diagnostic
Reopen the saved file and ask it what charts it contains, and what their references point at.
# pip install "openpyxl>=3.1,<4"
from pathlib import Path
from openpyxl import load_workbook
XLSX = Path("out/report.xlsx")
def chart_report(xlsx_path: Path) -> None:
"""List the charts in a saved workbook and the ranges they reference."""
if not xlsx_path.exists():
raise FileNotFoundError(f"No such workbook: {xlsx_path}")
try:
book = load_workbook(xlsx_path)
except Exception as exc:
raise RuntimeError(f"Could not open {xlsx_path}: {exc}") from exc
for sheet in book.worksheets:
charts = getattr(sheet, "_charts", [])
print(f"{sheet.title}: {len(charts)} chart(s), data rows to {sheet.max_row}")
for index, chart in enumerate(charts, start=1):
refs = [str(series.val.numRef.f) for series in chart.series if series.val.numRef]
print(f" chart {index}: {type(chart).__name__} anchored at {chart.anchor}")
print(f" series ranges: {refs}")
book.close()
if __name__ == "__main__":
chart_report(XLSX)
Data: 1 chart(s), data rows to 10
chart 1: BarChart anchored at E5
series ranges: ["'Data'!$B$1:$B$10"]
Zero charts means the chart never reached the saved file — a workbook-object problem. A chart with an empty series ranges list, or a range that does not overlap the populated cells, is a reference problem.
The Fix: Correct References, Correct Sheet, Correct Order
# pip install "openpyxl>=3.1,<4"
from pathlib import Path
from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference
OUT = Path("out/report.xlsx")
ROWS = [
["Region", "Units"],
["EMEA", 1204],
["AMER", 980],
["APAC", 655],
]
def build_chart_workbook(dest: Path) -> Path:
"""Write data and a bar chart that Excel will actually render."""
book = Workbook()
sheet = book.active
sheet.title = "Data"
for row in ROWS:
sheet.append(row)
chart = BarChart()
chart.title = "Units by region"
chart.y_axis.title = "Units"
chart.x_axis.title = "Region"
# Values: include the header row and set titles_from_data so row 1 becomes the series name
values = Reference(sheet, min_col=2, min_row=1, max_row=sheet.max_row)
# Categories: the label column WITHOUT its header
categories = Reference(sheet, min_col=1, min_row=2, max_row=sheet.max_row)
chart.add_data(values, titles_from_data=True)
chart.set_categories(categories)
chart.height, chart.width = 8, 16 # centimetres; the default is small
sheet.add_chart(chart, "D2") # anchor must be a free cell
dest.parent.mkdir(parents=True, exist_ok=True)
book.save(dest)
return dest
if __name__ == "__main__":
print(build_chart_workbook(OUT))
Three rules make the difference. Values ranges include the header row when titles_from_data=True, and exclude it otherwise — mixing the two produces either an unnamed series or a first bar of zero. Category ranges never include the header. And the anchor is a cell reference where the chart's top-left corner lands; anchoring onto the data itself hides the numbers behind the plot but does not break the chart.
Variant Fix 1: Charts Disappear When the Workbook Is Re-Saved
openpyxl reads charts as opaque parts and does not round-trip them. Loading a workbook that already contains charts and saving it again writes a file with none.
# pip install "openpyxl>=3.1,<4"
from openpyxl import load_workbook
book = load_workbook("out/report.xlsx")
print(len(book["Data"]._charts)) # 1 — visible on load
book.save("out/report.xlsx")
book2 = load_workbook("out/report.xlsx")
print(len(book2["Data"]._charts)) # 0 — lost on save
The only reliable answer is to rebuild the chart every time the file is written. Structure the job so charts are created by code at the end of the write, not carried over from a template:
# pip install "openpyxl>=3.1,<4" "pandas>=2.2,<3"
from pathlib import Path
import pandas as pd
from openpyxl.chart import LineChart, Reference
def write_report(frame: pd.DataFrame, dest: Path) -> Path:
"""Write data with pandas, then add the chart to the same writer's worksheet."""
dest.parent.mkdir(parents=True, exist_ok=True)
with pd.ExcelWriter(dest, engine="openpyxl") as writer:
frame.to_excel(writer, sheet_name="Data", index=False)
sheet = writer.sheets["Data"] # fetched AFTER to_excel
chart = LineChart()
chart.title = "Monthly value"
values = Reference(sheet, min_col=2, min_row=1, max_row=sheet.max_row)
chart.add_data(values, titles_from_data=True)
chart.set_categories(Reference(sheet, min_col=1, min_row=2, max_row=sheet.max_row))
sheet.add_chart(chart, "E2")
return dest
Fetching the worksheet from writer.sheets after to_excel is the same ordering rule that governs fix openpyxl number format not applied — a worksheet captured earlier belongs to an object pandas has since replaced.
Variant Fix 2: The Series Points at Text
A chart plots numbers. If the value column arrived as text — a common outcome when a CSV column was read as object — the plot area is empty even though the range is right.
# pip install "openpyxl>=3.1,<4"
from openpyxl import load_workbook
book = load_workbook("out/report.xlsx")
sheet = book["Data"]
non_numeric = [
cell.coordinate
for row in sheet.iter_rows(min_col=2, max_col=2, min_row=2)
for cell in row
if cell.value is not None and not isinstance(cell.value, (int, float))
]
print("non-numeric value cells:", non_numeric[:10])
Convert before writing rather than in the sheet — pd.to_numeric(frame["units"], errors="coerce") at the source, with the sentinel handling described in cleaning messy CSV data with pandas.
Variant Fix 3: Formula Results Are Not Values Yet
A chart over a column of formulas shows nothing until Excel has calculated them, because openpyxl writes the formula string and no cached result.
# pip install "openpyxl>=3.1,<4"
sheet["C2"] = "=B2*1.2" # openpyxl writes the formula, not a value
Excel evaluates on open and the chart then fills in, but any consumer reading the file with data_only=True sees None. When the chart must be correct without Excel ever opening the file, compute the column in Python and write literal values — the same constraint behind fix openpyxl formulas not calculating.
Verification
Assert on the saved file: a chart exists, it has series, and each series range overlaps populated numeric cells.
# pip install "openpyxl>=3.1,<4"
from pathlib import Path
from openpyxl import load_workbook
def assert_chart_valid(xlsx_path: Path, sheet_name: str = "Data",
expected_charts: int = 1) -> None:
"""Fail when a chart is missing, has no series, or references empty cells."""
book = load_workbook(xlsx_path)
try:
sheet = book[sheet_name]
charts = getattr(sheet, "_charts", [])
assert len(charts) == expected_charts, (
f"{len(charts)} chart(s) found, expected {expected_charts}"
)
for index, chart in enumerate(charts, start=1):
assert chart.series, f"chart {index}: no data series attached"
for series in chart.series:
assert series.val.numRef is not None, f"chart {index}: series has no numeric ref"
assert "!" in str(series.val.numRef.f), (
f"chart {index}: reference is not sheet-qualified"
)
numeric_cells = sum(
1
for row in sheet.iter_rows(min_row=2)
for cell in row
if isinstance(cell.value, (int, float))
)
assert numeric_cells > 0, "no numeric cells on the sheet — the chart will render empty"
print(f"{len(charts)} chart(s) verified over {numeric_cells} numeric cell(s)")
finally:
book.close()
if __name__ == "__main__":
assert_chart_valid(Path("out/report.xlsx"))
The sheet-qualified reference check catches the subtle case where a Reference was built against the wrong worksheet object: the range looks plausible but names a sheet that holds no data. In a scheduled report this assertion is what stops an empty chart being emailed to a distribution list — wire it into the job described in automating Excel report generation.
FAQ
Does openpyxl preserve charts that already exist in a template? No. Charts are lost on load-and-save, so any workbook written by openpyxl must have its charts recreated in code each time. Keep the template for styling and generate charts programmatically.
Why is my chart tiny?
The default is roughly 7.5 by 15 centimetres. Set chart.height and chart.width in centimetres before add_chart; values around 8 by 16 suit a full-width report block.
Can I put the chart on its own sheet?
Yes — create a sheet and anchor there, referencing the data sheet: Reference(data_sheet, ...) while calling chart_sheet.add_chart(chart, "A1"). The reference carries the sheet name, so cross-sheet charts work.
Are pivot charts supported? No. openpyxl writes standard charts only; pivot caches and pivot charts are not generated. Pre-aggregate in pandas and plot the aggregate as an ordinary chart.
Why does LibreOffice show the chart but Excel does not, or vice versa? The two differ in how strictly they validate chart XML. A reference that names a non-existent range is tolerated by one and dropped by the other — run the diagnostic and confirm every series range resolves to populated cells.
Related
- Writing Excel Formulas and Charts with openpyxl — the full charting and styling workflow
- Fix openpyxl Formulas Not Calculating — why a chart over formulas can start empty
- Fix openpyxl Number Format Not Applied — the same worksheet-object ordering rule
- Automating Excel Report Generation — scheduled reports that must not ship empty charts
- Fix openpyxl Read-Only Mode Error — modes that silently discard drawing parts