Excel Charts and Pivots skill for Codex

Done well means: charts are native Excel charts bound to cell ranges (so they update and stay editable), summaries reconcile to the raw data, and the user knows whether a pivot is a live PivotTable or a static computed table.

The honest constraint

No mainstream open-source library creates a real PivotTable from scratch: openpyxl keeps existing ones but cannot author them, XlsxWriter and ExcelJS do not support them, and ExcelJS has no chart support either. Choose deliberately:

User needs	Approach
A summary table + chart, refreshed by rerunning the script	pandas pivot_table written as a range, native chart on it (default)
A true PivotTable users can slice and re-pivot	Template workbook with a PivotTable on an Excel Table; code replaces the data and sets refresh-on-open
Live summary that updates as users type, Microsoft 365 users	PIVOTBY / GROUPBY or SUMIFS grid typed in Excel, chart on the result
Windows machine with Excel installed, full automation	COM via pywin32 or xlwings (not available in a Linux/macOS sandbox)

State which approach you used in the hand-over; users get confused when a “pivot” will not re-pivot.

Workflow

- [ ] 1. Read AGENTS.md; check which of openpyxl / XlsxWriter / pandas the project uses
- [ ] 2. Aggregate in code; print the summary and reconcile its grand total to the raw data
- [ ] 3. Write data and summary as plain ranges or an Excel Table
- [ ] 4. Bind charts to those ranges (never to pasted values or images)
- [ ] 5. Format: titles that state the takeaway, axis number formats, legend placement
- [ ] 6. Save, render or open, check the chart reads correctly

Static pivot with pandas + native chart (openpyxl)

import pandas as pd
from openpyxl import load_workbook
from openpyxl.chart import BarChart, LineChart, Reference

sales = pd.read_csv("sales.csv", parse_dates=["date"])
sales["month"] = sales["date"].dt.to_period("M").astype(str)
pivot = sales.pivot_table(index="month", columns="region", values="net",
                          aggfunc="sum", fill_value=0)
assert round(pivot.to_numpy().sum(), 2) == round(sales["net"].sum(), 2)   # reconcile

with pd.ExcelWriter("sales-summary.xlsx", engine="openpyxl") as xw:
    sales.to_excel(xw, sheet_name="Data", index=False)
    pivot.to_excel(xw, sheet_name="Summary")          # index -> column A, regions -> B..

wb = load_workbook("sales-summary.xlsx")
ws = wb["Summary"]
n_rows, n_cols = pivot.shape

chart = BarChart()
chart.type, chart.grouping = "col", "clustered"
chart.title = "Net sales by month and region"
chart.y_axis.title = "Net sales"
chart.y_axis.number_format = "#,##0"
chart.y_axis.majorGridlines = None
data = Reference(ws, min_col=2, max_col=1 + n_cols, min_row=1, max_row=1 + n_rows)
cats = Reference(ws, min_col=1, min_row=2, max_row=1 + n_rows)
chart.add_data(data, titles_from_data=True)
chart.set_categories(cats)
chart.x_axis.delete = False      # openpyxl 3.1 otherwise hides axes in recent Excel
chart.y_axis.delete = False
chart.legend.position = "b"
chart.width, chart.height = 24, 12                     # centimetres
ws.add_chart(chart, f"{chr(ord('A') + n_cols + 2)}2")
wb.save("sales-summary.xlsx")

Combo chart (columns plus a line on a secondary axis), assuming a margin column was written right after the region columns:

line = LineChart()
line.add_data(Reference(ws, min_col=2 + n_cols, min_row=1, max_row=1 + n_rows), titles_from_data=True)
line.y_axis.axId = 200
line.y_axis.title = "Margin %"
line.y_axis.number_format = "0%"
line.y_axis.crosses = "max"                            # put the secondary axis on the right
line.y_axis.delete = False
chart += line

XlsxWriter charts (new files, richer formatting)

import xlsxwriter

wb = xlsxwriter.Workbook("kpi.xlsx")
ws = wb.add_worksheet("Summary")
ws.write_row("A1", ["Month", "Orders", "Margin"])
rows = [("Jul", 1180, 0.21), ("Aug", 1204, 0.23), ("Sep", 1310, 0.24)]
for r, row in enumerate(rows, start=1):
    ws.write_row(r, 0, row)
last = len(rows)

col = wb.add_chart({"type": "column"})
col.add_series({
    "name": "=Summary!$B$1",
    "categories": ["Summary", 1, 0, last, 0],          # [sheet, first_row, first_col, last_row, last_col]
    "values": ["Summary", 1, 1, last, 1],
    "data_labels": {"value": True, "num_format": "#,##0"},
})
line = wb.add_chart({"type": "line"})
line.add_series({
    "name": "=Summary!$C$1",
    "categories": ["Summary", 1, 0, last, 0],
    "values": ["Summary", 1, 2, last, 2],
    "y2_axis": True,
    "marker": {"type": "circle"},
})
col.combine(line)
col.set_title({"name": "Orders rising, margin up 3 points"})
col.set_y_axis({"num_format": "#,##0", "major_gridlines": {"visible": False}})
line.set_y2_axis({"num_format": "0%"})
col.set_legend({"position": "bottom"})
ws.insert_chart("E2", col, {"x_scale": 1.6, "y_scale": 1.3})
wb.close()

XlsxWriter only writes new files. To add a chart to an existing workbook, use openpyxl (and check that the workbook’s other charts survive the round trip, see Gotchas).

Refreshable PivotTable via a template

1. In Excel, build the source as an Excel Table (e.g. SalesData on sheet Data), create the PivotTable and any PivotChart from it, save as pivot-template.xlsx.
2. In code, replace the Table’s rows and resize it; mark the cache to refresh on open:

from openpyxl import load_workbook

wb = load_workbook("pivot-template.xlsx")
ws = wb["Data"]
table = ws.tables["SalesData"]
ws.delete_rows(2, ws.max_row)                      # keep the header row
for row in new_rows:                               # same column order as the header
    ws.append(row)
table.ref = f"A1:{ws.cell(row=1, column=ws.max_column).column_letter}{ws.max_row}"

for sheet in wb.worksheets:
    for pivot in sheet._pivots:                    # openpyxl keeps pivots it loaded
        pivot.cache.refreshOnLoad = True
wb.save("pivot-2025-10.xlsx")

Excel rebuilds the pivot when the file opens. Until then the pivot shows the template’s old numbers, and tools that read cached values (pandas, previewers) will see stale data; say so in the hand-over. _pivots is a private attribute: pin the openpyxl version and cover this step with a test that reopens the file and checks refreshOnLoad.

Chart choices

Data	Chart
Category comparison (regions, products)	Clustered bar/column, sorted by value
Trend over time	Line; columns only for few periods
Two measures with different units	Combo with secondary axis, both axes labelled
Part-to-whole with 2-5 parts	Stacked column or 100% stacked; pie only for one series with few slices
Relationship between two numbers	Scatter (ScatterChart / {"type": "scatter"})

Titles state the finding (“North leads on volume”), axes have units, and numbers use the same format as the cells.

Gotchas

• References are 1-based in openpyxl (min_row=1 is the header) and 0-based in XlsxWriter list notation. Off-by-one shows up as a series named after a number or a missing month.
• titles_from_data=True expects the header cell in min_row; without it the first data row becomes the series name.
• Charts bound to fixed ranges do not grow. For monthly appends, bind to a range sized for the year, or regenerate the chart each run.
• openpyxl re-serialises charts it loads, which can lose formatting it does not model, and it drops shapes, form controls and slicers (images too if Pillow is missing). Never round-trip the user’s hand-made dashboard through openpyxl without comparing unzip -l part lists and the rendered charts before and after.
• Sheet names with spaces need quotes in XlsxWriter string references: ="'Q3 Data'!$B$2:$B$13".

Verification

• Reconcile: the summary’s grand total equals the raw data total (assert in code).
• Open the file (or convert with soffice --headless --convert-to pdf) and look at each chart: correct series names, all categories present, axis formats right.
• unzip -l out.xlsx | grep -E "charts/|pivotTables/" confirms the expected chart and pivot parts exist.
• For template pivots, reopen with openpyxl and assert refreshOnLoad is set on every cache.