Make an Excel Chart Grow with Its Data

A monthly sales sheet has a line chart beside it. A script appends October's row — and the chart still stops at September. Charts in Excel do not point at "the data"; each series stores fixed cell ranges such as Data!$B$2:$B$10, and appending a row below those ranges does not change them. Inside Excel, people are used to charts that expand on their own, because a chart built on an Excel table is adjusted by Excel when rows are added to the table through the interface. A file edited by a script gets none of that help. This page shows how to see the problem, how to extend an existing chart's ranges from Python, why rebuilding the chart is often simpler, and what to do for workbooks that people edit live. It belongs to Creating Charts in Excel with openpyxl.

Prerequisites

pip install openpyxl xlsxwriter pandas

sales_chart.xlsx has six months of sales and targets and a line chart covering rows 2–7. The task: append July to September and have the chart include them.

Step 1 — See the problem

from openpyxl import load_workbook

wb = load_workbook("sales_chart.xlsx")
ws = wb["Data"]
for row in [("Jul", 172, 140), ("Aug", 169, 145), ("Sep", 181, 150)]:
    ws.append(row)
wb.save("sales_appended.xlsx")

check = load_workbook("sales_appended.xlsx")["Data"]
chart = check._charts[0]
print("data now ends at row", check.max_row)
for s in chart.series:
    print("series range:", s.val.numRef.f)

The data ends at row 10, but both series still read rows 2–7. openpyxl 3.1 keeps the chart when it loads and saves the workbook — it reads charts back and rewrites them — but it does not, and cannot know to, change their ranges. Anyone opening the file sees a chart that stops in June.

Step 2 — Extend the existing chart's ranges

Each series of a loaded openpyxl chart exposes its range formulas — val.numRef.f for values and cat.numRef.f or cat.strRef.f for categories — and they can be rewritten:

import re

def extend_series(chart, last_row):
    """Point every series' values and categories at rows up to last_row."""
    def grow(ref):
        return re.sub(r"\$(\d+)$", f"${last_row}", ref)
    for s in chart.series:
        s.val.numRef.f = grow(s.val.numRef.f)
        if s.cat is not None:
            target = s.cat.numRef or s.cat.strRef
            target.f = grow(target.f)

wb = load_workbook("sales_appended.xlsx")
ws = wb["Data"]
for chart in ws._charts:
    extend_series(chart, ws.max_row)
wb.save("sales_extended.xlsx")
print([s.val.numRef.f for s in load_workbook("sales_extended.xlsx")["Data"]._charts[0].series])

The helper replaces the end row of each range — the digits after the last $ — with the sheet's new last row, so 'Data'!$B$2:$B$7 becomes 'Data'!$B$2:$B$10 for both series and the category range follows. _charts is openpyxl's list of charts on a worksheet; it carries a leading underscore, so treat it as an implementation detail and pin your openpyxl version. One more caution: because openpyxl rebuilds charts from what it understands, a chart designed in Excel with formatting openpyxl does not model can lose some of it in the round trip — try it on a copy of the real workbook first, as described in What openpyxl Loses When Saving an Existing Workbook.

Step 3 — Or rebuild the chart from the data's size

For reports a script generates from scratch, the more robust habit is to create the chart after writing the data, with ranges computed from the data itself:

import pandas as pd

df = pd.read_excel("sales_appended.xlsx", sheet_name="Data")
with pd.ExcelWriter("sales_rebuilt.xlsx", engine="xlsxwriter") as xw:
    df.to_excel(xw, sheet_name="Data", index=False)
    book = xw.book
    chart = book.add_chart({"type": "line"})
    last = len(df)                                   # last data row, zero-based (header is row 0)
    for col, colour in ((1, "#4338CA"), (2, "#0F9488")):
        chart.add_series({"name": ["Data", 0, col], "categories": ["Data", 1, 0, last, 0],
                          "values": ["Data", 1, col, last, col], "line": {"color": colour}})
    chart.set_title({"name": "Sales vs target"})
    xw.sheets["Data"].insert_chart("E2", chart)
print("chart covers", len(df), "months:", df["Month"].tolist()[0], "to", df["Month"].tolist()[-1])

The ranges come from len(df), so the chart always covers exactly the rows written — nine months now, twelve in December, and never a stale range. There is no round trip through a reader, so nothing is lost. This is the approach to prefer whenever the workbook is a generated report rather than a file people maintain by hand. Building line charts is covered in Add a Line Chart to an Excel Report with Python.

Step 4 — Workbooks people edit live: put the data in a table

When people add rows in Excel, the most reliable mechanism is Excel's own: make the data an Excel table and build the chart on it. When a row is added to the table in Excel, Excel extends the table and adjusts charts based on it. A script that adds rows through Excel — with xlwings, for example — gets the same behaviour:

import xlwings as xw

wb = xw.Book("sales_live.xlsx")
table = wb.sheets["Data"].tables["Sales"]
next_row = table.range.last_cell.offset(1, 0)
next_row.resize(1, 3).value = ["Oct", 176, 155]      # typed just below the table: Excel extends it
print(table.range.address)

Writing directly below a table in a live Excel session triggers Excel's automatic table expansion, and the chart follows. A script that edits the file with openpyxl does not get this: it has to resize the table itself, as in Append Rows to an Excel Table and Resize It, and then still update the chart's ranges as in Step 2, because the chart stores plain cell ranges in the file.

Step 5 — Why dynamic named ranges are a poor fit for generated files

Classic Excel advice is a named range defined with OFFSET and COUNTA — =OFFSET(Data!$B$2,0,0,COUNTA(Data!$A:$A)-1,1) — used as a series' values. Created in Excel, it works there. Generated from Python it is fragile: chart series that reference workbook-level names are displayed by Excel as ='Workbook.xlsx'!Name, tying them to the file name, XlsxWriter warns that it cannot validate such references, and OFFSET is volatile, recalculating on every edit. Updating plain ranges from Python, or rebuilding the chart, gives the same result without these side effects.

Step 6 — Test that the chart covers the data

Whichever approach you use, a two-line test prevents a stale chart from shipping:

def chart_covers_data(path, sheet="Data"):
    ws = load_workbook(path)[sheet]
    last = ws.max_row
    ends = {int(re.search(r"\$(\d+)$", s.val.numRef.f).group(1)) for c in ws._charts for s in c.series}
    return ends == {last}, last, ends

for f in ("sales_appended.xlsx", "sales_extended.xlsx", "sales_rebuilt.xlsx"):
    print(f"{f:22}", chart_covers_data(f))

The appended-only file fails — its series end at row 7 while the data ends at row 10 — and both fixed files pass. Run a check like this after every update; it costs milliseconds and catches the chart that quietly stopped growing three months ago.

Common pitfalls

• Assuming charts follow appended rows. Ranges are fixed in the file; update or rebuild them.
• Updating values but not categories. Extend the category range too, or new points get no labels.
• Relying on table auto-expansion from a script. That is an Excel interface feature; scripts must resize tables and charts themselves.
• Round-tripping hand-made charts. openpyxl may drop formatting it does not model; test on a copy.
• Hard-coding row numbers. Derive the last row from the data each run.
• Trailing blank or total rows. If the sheet ends with a total row, stop the series one row above it, or the total becomes a spike at the end of the line.

Conclusion

Excel charts store fixed ranges, so rows appended by a script fall outside them. For generated reports, rebuild the chart every run with ranges computed from the data's size; for workbooks people maintain, extend each series' value and category ranges with openpyxl after checking the round trip keeps the chart's formatting; for live editing, keep the data in an Excel table and let Excel extend it. Avoid OFFSET named ranges in generated files, and test that the chart's last row matches the data's.

Frequently asked questions

Why doesn't my Excel chart include rows added by Python? Each chart series stores a fixed cell range such as Data!$B$2:$B$7. Appending rows below it does not change the range, so the chart has to be updated or rebuilt by the script.

How do I extend an existing chart's range with openpyxl? Load the workbook, go through the worksheet's charts and their series, and rewrite val.numRef.f and the category reference so their end row is the sheet's new last row, then save.

Do charts based on Excel tables update automatically? When rows are added to the table inside Excel, Excel extends the table and adjusts charts that use it. Files edited by scripts do not get this automatically; resize the table and update the chart ranges in the script.

Should I use OFFSET named ranges for dynamic charts in generated files? They work when built in Excel, but from Python they are brittle: name references in chart series are tied to the file name, libraries cannot validate them, and OFFSET is volatile. Updating ranges or rebuilding the chart is more reliable.

Related

• Up one level: Creating Charts in Excel with openpyxl — native charts from Python.
• Create One Chart per Group with openpyxl — generating many charts from data.
• openpyxl: Append Data to an Existing Excel Sheet — appending rows safely.
• Add a Chart to an Excel Online Workbook with Microsoft Graph — setData for cloud workbooks.