Fixing Python openpyxl Chart Data That Disappears After Saving the Workbook

You write a script that builds a bar chart from worksheet data, call wb.save(), open the file in Excel, and find a blank chart placeholder — or no chart at all. The data is there, the chart object is there, but nothing renders. This is one of the most frustrating openpyxl bugs to diagnose because the script raises no errors.

The root cause is almost always a malformed data reference or an anchoring mistake that openpyxl silently accepts but Excel rejects at render time. This guide walks through every layer of the problem and shows you the exact patterns that fix it.

What You'll Learn

• How openpyxl builds chart data references internally and what Excel expects
• The specific Reference constructor arguments that cause blank charts
• How to verify your sheet name, data range, and series titles are wired correctly
• How to anchor a chart so it appears in the right place after saving
• A working end-to-end example you can copy and adapt

Prerequisites

This guide assumes you have openpyxl 3.x installed (pip install openpyxl) and a basic familiarity with reading and writing Excel files in Python. You do not need any knowledge of the underlying OOXML format — but it helps to know that openpyxl generates XML files that Excel then parses, which is why silent mismatches are possible.

How openpyxl Charts Actually Work

When you create a chart in openpyxl, you are building an XML object that gets embedded inside the workbook's ZIP structure at save time. That XML contains references to worksheet data using a string formula like Sheet1!$A$1:$A$10. Excel reads those references when it opens the file and renders the chart from whatever data it finds at those coordinates.

This matters because openpyxl does not validate those references against the actual sheet content. You can point a chart at a sheet that doesn't exist, a range with zero rows, or a mismatched series title, and openpyxl will happily write the file. Excel will open it, fail to resolve the reference silently, and render a blank chart.

The Most Common Cause: Broken Data References

The Reference class is where most bugs live. Its constructor looks simple, but the arguments are 1-indexed (not 0-indexed like Python lists), and getting min_row, max_row, min_col, and max_col wrong by even one cell can produce an empty series.

Here is a typical broken pattern:

from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference

wb = Workbook()
ws = wb.active

# Write headers + data
rows = [("Month", "Sales"), ("Jan", 1200), ("Feb", 1500), ("Mar", 980)]
for row in rows:
    ws.append(row)

chart = BarChart()
# BUG: min_col and max_col are 0 — openpyxl uses 1-based indexing
data = Reference(ws, min_col=0, min_row=1, max_row=4, max_col=0)
chart.add_data(data)

ws.add_chart(chart, "D2")
wb.save("broken_chart.xlsx")

Column index 0 is outside the valid range. openpyxl generates a reference string that Excel cannot resolve, so the chart renders empty. No exception is raised.

Fixing the Reference: Using Reference Objects Correctly

Switch to 1-based column and row indices and be explicit about whether your first row is a header. The titles_from_data=True flag on add_data() tells openpyxl that row 1 of your reference contains series labels, not numbers.

from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference

wb = Workbook()
ws = wb.active
ws.title = "Sales"

rows = [("Month", "Sales"), ("Jan", 1200), ("Feb", 1500), ("Mar", 980)]
for row in rows:
    ws.append(row)

chart = BarChart()
chart.type = "col"
chart.title = "Monthly Sales"
chart.y_axis.title = "Amount"
chart.x_axis.title = "Month"

# Data reference: column 2 (Sales), rows 1-4 (header + 3 data rows)
data = Reference(ws, min_col=2, min_row=1, max_row=4, max_col=2)
chart.add_data(data, titles_from_data=True)

# Category reference: column 1 (Month labels), rows 2-4 (data only, no header)
categories = Reference(ws, min_col=1, min_row=2, max_row=4)
chart.set_categories(categories)

# Anchor the chart starting at cell D2
ws.add_chart(chart, "D2")

wb.save("fixed_chart.xlsx")

Three things changed here compared to the broken version. First, min_col and max_col are both 2 — the actual column index of the Sales data. Second, titles_from_data=True is passed so openpyxl emits the correct XML attribute. Third, the categories reference starts at min_row=2 to skip the header row, which prevents Excel from treating "Month" as a data point.

Checking Your Sheet Name and Data Range

A less obvious cause of vanishing chart data is a mismatch between the sheet name used in the reference and the actual worksheet title. If you rename a sheet after constructing the reference, the embedded XML still points to the old name.

# Dangerous: rename after building the reference
ws = wb.active
data = Reference(ws, min_col=2, min_row=1, max_row=10)
ws.title = "Report"  # Reference was built when title was "Sheet"

Always set ws.title before constructing any Reference objects. If you need to verify what sheet name openpyxl will embed, print ws.title immediately before building the reference and compare it to the saved file's sheet tab in Excel.

Empty data ranges are another common trap. If your script appends rows conditionally and the sheet ends up with only a header row and no data, the chart will be blank. Add a guard before saving:

if ws.max_row < 2:
    raise ValueError("No data rows found — chart would be empty.")

This also helps when debugging scripts that pull from a database or API and silently return zero rows. For similar silent-failure bugs in Python data pipelines, the patterns described in fixing openpyxl that reads Excel formulas instead of values are worth reviewing as well.

Anchoring the Chart to the Worksheet

Charts that seem to disappear are sometimes actually present but positioned outside the visible area, or stacked on top of each other. The anchor string you pass to add_chart() is a cell address like "E5" that marks the chart's top-left corner.

If you add multiple charts in a loop without adjusting the anchor, each one lands on top of the previous. Only the last one is visible, and you might mistake the missing charts for data bugs.

chart_positions = ["B2", "B20", "B38"]  # offset vertically by ~18 rows each

for i, (series_col, position) in enumerate(zip(range(2, 5), chart_positions)):
    chart = BarChart()
    data = Reference(ws, min_col=series_col, min_row=1, max_row=ws.max_row)
    chart.add_data(data, titles_from_data=True)
    chart.width = 15
    chart.height = 10
    ws.add_chart(chart, position)

Set chart.width and chart.height explicitly (in centimetres) so you know exactly how much vertical space each chart consumes and can calculate safe offsets.

When the Chart Object Exists But Excel Shows Nothing

If you have confirmed that your references are correct and the chart is anchored, but Excel still shows a blank frame, the issue is usually one of three things.

The workbook was opened in read-only mode

If you load an existing workbook with load_workbook(path, read_only=True) and then try to save it, some chart relationships in the OOXML package are dropped. Always load with read_only=False (the default) when you intend to modify and save charts.

The chart type attribute is missing or wrong

For BarChart, the type attribute controls orientation. Valid values are "col" (vertical bars) and "bar" (horizontal). Leaving it unset defaults to "col", which is fine, but setting it to an invalid string causes Excel to silently discard the chart series. Always use the documented constants.

You are overwriting an existing chart relationship

If you load a workbook that already contains charts and then call ws.add_chart(), openpyxl appends a new chart. It does not replace the existing one. Depending on the Excel version, the file can end up with duplicate or conflicting chart relationships. If you need to update an existing chart, it is safer to remove all charts from the sheet and rebuild them programmatically. You can clear charts with ws._charts.clear() before adding new ones — note the underscore indicates an internal attribute, so treat this as a workaround rather than a stable API guarantee.

If you are also experiencing problems with cell formatting disappearing when you copy data between workbooks, the approach described in fixing openpyxl losing cell styles when copying rows between workbooks covers the same class of OOXML relationship issues.

A Complete Working Example

Here is a self-contained script that writes data, builds two charts on the same sheet, anchors them without overlap, and saves correctly. Use this as a reference baseline when debugging your own implementation.

from openpyxl import Workbook
from openpyxl.chart import BarChart, LineChart, Reference

wb = Workbook()
ws = wb.active
ws.title = "Revenue"

# Write data
headers = ["Quarter", "Product A", "Product B"]
data_rows = [
    ("Q1", 4200, 3100),
    ("Q2", 5300, 4800),
    ("Q3", 4700, 5200),
    ("Q4", 6100, 5900),
]
ws.append(headers)
for row in data_rows:
    ws.append(row)

max_row = ws.max_row  # 5 (1 header + 4 data rows)

# --- Bar chart: Product A ---
bar_chart = BarChart()
bar_chart.type = "col"
bar_chart.title = "Product A Revenue"
bar_chart.y_axis.title = "Revenue ($)"
bar_chart.x_axis.title = "Quarter"
bar_chart.width = 15
bar_chart.height = 10

bar_data = Reference(ws, min_col=2, max_col=2, min_row=1, max_row=max_row)
bar_chart.add_data(bar_data, titles_from_data=True)
categories = Reference(ws, min_col=1, min_row=2, max_row=max_row)
bar_chart.set_categories(categories)

ws.add_chart(bar_chart, "E2")

# --- Line chart: Product B ---
line_chart = LineChart()
line_chart.title = "Product B Revenue"
line_chart.y_axis.title = "Revenue ($)"
line_chart.x_axis.title = "Quarter"
line_chart.width = 15
line_chart.height = 10

line_data = Reference(ws, min_col=3, max_col=3, min_row=1, max_row=max_row)
line_chart.add_data(line_data, titles_from_data=True)
line_chart.set_categories(categories)

# Offset by approximately 20 rows to avoid overlap
ws.add_chart(line_chart, "E22")

wb.save("revenue_charts.xlsx")
print("Saved successfully.")

Open revenue_charts.xlsx in Excel and both charts should render with correct labels and data points. If yours still does not, add a quick diagnostic print before saving:

print(f"Sheet title: {ws.title}")
print(f"Max row: {ws.max_row}, Max col: {ws.max_column}")
print(f"Charts on sheet: {len(ws._charts)}")

This confirms that the sheet name, data bounds, and chart count all look right before the file is written.

Common Pitfalls to Avoid

• 0-indexed columns: openpyxl always uses 1-based row and column indices in Reference. Column 1 is A, column 2 is B, and so on.
• Forgetting titles_from_data=True: Without this flag, your header label is treated as a numeric data point, which corrupts the series and can produce a blank or incorrect chart.
• Not setting categories: Skipping set_categories() means the x-axis will show generic numbers instead of your labels. In some Excel versions, missing category information causes the entire series to disappear.
• Renaming the sheet after building references: The reference captures the sheet title at construction time. Rename first, build references second.
• Saving to a path that is already open in Excel: Excel locks the file. openpyxl will raise a PermissionError, but if you catch that error silently, you may end up debugging a stale file.
• Using data_only=True when loading for chart editing: This mode strips chart objects from the loaded workbook. Never combine it with chart manipulation.

Many of these silent failure modes parallel problems you see with hidden rows and cell reads — if you have run into that, fixing openpyxl skipping hidden rows when reading cell data explains how openpyxl's read modes affect what data is actually accessible.

Next Steps

You now have the tools to diagnose and fix blank or missing openpyxl chart data. Here are concrete actions to take from here:

1. Audit existing scripts for 0-indexed Reference arguments and missing titles_from_data=True calls — these are the two most common bugs by a wide margin.
2. Set the sheet title before building any references as a standard practice, not an afterthought.
3. Add a data-row guard (if ws.max_row < 2: raise ValueError(...)) at the top of any chart-generating function so you catch empty inputs before the file is saved.
4. Use explicit chart.width and chart.height on every chart, and calculate anchor offsets based on those dimensions when placing multiple charts on one sheet.
5. Test with the number formatting on your chart axes — a related source of confusion covered in fixing openpyxl number formatting not applied to cells that can make correct data look wrong in the rendered chart.