Add Subtotals to Excel Reports with pandas
The sales report lists products grouped by region, and the finance team wants what Excel's Data → Subtotal command gives them: a subtotal row after each region, a grand total at the bottom, and the plus/minus outline buttons that collapse each region to its subtotal. pivot_table(margins=True) only produces a grand total. Appending rows with concat puts all the subtotals at the end. Writing formulas by hand breaks the moment a region has one more product than last month.
Root Cause
pandas aggregates or keeps detail rows; it has no single operation that returns both interleaved. margins=True computes totals across the whole table, not per group, and groupby().sum() replaces the detail with one row per group. Excel's Subtotal feature combines three separate things: inserted summary rows positioned directly after each group, styling that distinguishes them, and outline levels stored per row in the worksheet that drive the collapse buttons. To reproduce it, build the interleaved frame in pandas with explicit sort keys, then apply styling and row outline levels with openpyxl after writing.
Minimal Diagnostic
Check that groups are contiguous and that group labels are clean. Subtotals inserted into unsorted data, or into groups split by case or whitespace variants, land in the wrong places.
# pip install "pandas>=2.2"
from pathlib import Path
import pandas as pd
SOURCE = Path("in/sales-by-product-2026-09.csv")
def diagnose(path: Path, group: str = "region") -> None:
try:
frame = pd.read_csv(path)
except (OSError, pd.errors.ParserError) as exc:
raise SystemExit(f"cannot read {path}: {exc}")
runs = (frame[group] != frame[group].shift()).cumsum()
run_count = frame.groupby(runs)[group].first().value_counts()
split = run_count[run_count > 1]
print(f"{frame[group].nunique()} groups, {runs.max()} contiguous runs")
if not split.empty:
print(f"groups split into several runs (data not sorted): {split.to_dict()}")
variants = frame[group].astype(str).str.strip().str.casefold()
if variants.nunique() < frame[group].nunique():
print("case or whitespace variants would create extra subtotal groups")
if __name__ == "__main__":
diagnose(SOURCE)
5 groups, 9 contiguous runs
groups split into several runs (data not sorted): {'EMEA': 3, 'APAC': 2}
Nine runs for five groups means inserting a subtotal "after each group" would produce nine subtotals. Sort — and clean labels — first.
Fix: Build Interleaved Rows, Then Style and Outline
Sort, compute group sums, tag every row with a sort key that places each subtotal after its group, and concatenate. Then write with openpyxl and set outline levels. Changed lines are commented.
# pip install "pandas>=2.2" "openpyxl>=3.1"
from pathlib import Path
import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import Border, Font, PatternFill, Side
SOURCE = Path("in/sales-by-product-2026-09.csv")
DEST = Path("out/sales-with-subtotals.xlsx")
GROUP, LABEL, MEASURES = "region", "product", ["units", "revenue"]
def with_subtotals(frame: pd.DataFrame) -> pd.DataFrame:
detail = frame.copy()
detail[GROUP] = detail[GROUP].astype(str).str.strip() # changed: clean group labels
detail = detail.sort_values([GROUP, LABEL], kind="stable") # changed: contiguous groups
detail["row_type"] = "detail"
detail["_order"] = 0
subs = detail.groupby(GROUP, sort=True)[MEASURES].sum().reset_index() # changed: one row per group
subs[LABEL] = subs[GROUP] + " total"
subs["row_type"] = "subtotal"
subs["_order"] = 1 # changed: after its group
out = pd.concat([detail, subs], ignore_index=True)
out = out.sort_values([GROUP, "_order", LABEL], kind="stable") # changed: interleave by key
grand = pd.DataFrame([{GROUP: "", LABEL: "Grand total", # changed: appended last
**detail[MEASURES].sum().to_dict(), # changed: detail rows only
"row_type": "grand"}])
out = pd.concat([out.drop(columns="_order"), grand], ignore_index=True)
out.loc[out["row_type"] == "subtotal", GROUP] = "" # blank group on total rows
return out.reset_index(drop=True)
def write_with_outline(table: pd.DataFrame, dest: Path) -> None:
dest.parent.mkdir(parents=True, exist_ok=True)
visible = table.drop(columns="row_type")
visible.to_excel(dest, sheet_name="Sales", index=False)
wb = load_workbook(dest)
try:
ws = wb["Sales"]
bold = Font(bold=True)
rule = Border(top=Side(style="thin"))
shade = PatternFill(start_color="F2F2F2", end_color="F2F2F2", fill_type="solid")
ws.sheet_properties.outlinePr.summaryBelow = True # changed: buttons below groups
for i, kind in enumerate(table["row_type"], start=2): # row 1 is the header
if kind == "detail":
ws.row_dimensions[i].outline_level = 2 # changed: collapsible detail
else:
ws.row_dimensions[i].outline_level = 1 if kind == "subtotal" else 0
for cell in ws[i]:
cell.font = bold
cell.border = rule
if kind == "grand":
cell.fill = shade
wb.save(dest)
finally:
wb.close()
if __name__ == "__main__":
try:
sales = pd.read_csv(SOURCE)
except (OSError, pd.errors.ParserError) as exc:
raise SystemExit(f"cannot read {SOURCE}: {exc}")
table = with_subtotals(sales)
write_with_outline(table, DEST)
print(table.tail(4).to_string(index=False))
The _order column is what places each subtotal after its products: within one region, detail rows carry 0 and the subtotal carries 1, so a stable sort on region then order interleaves them without any row-position arithmetic. The grand total is appended after sorting and computed from the detail rows only — computing it from the combined frame would double-count every value, because the subtotals are in there too. Blanking the region on subtotal rows is cosmetic but matters to readers, since APAC next to APAC total looks like one more product line.
summaryBelow = True tells Excel the subtotal sits below its detail rows, so the collapse button appears next to the subtotal. Outline levels are per row: level 2 for products, 1 for subtotals, 0 for the grand total, which leaves the grand total visible when everything is collapsed.
Variant Fix 1: Subtotals as Live Excel Formulas
Static subtotal values go stale if someone edits a product row. When the workbook is meant to be edited, write SUBTOTAL formulas instead. SUBTOTAL(9, ...) sums a range while ignoring other SUBTOTAL cells inside it, so the grand total can simply cover the whole column without double-counting.
# pip install "pandas>=2.2" "openpyxl>=3.1"
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter
def subtotal_formulas(dest, table, measure_cols: list[str]) -> None:
wb = load_workbook(dest)
try:
ws = wb["Sales"]
headers = [c.value for c in ws[1]]
group_start = 2
for i, kind in enumerate(table["row_type"], start=2):
if kind == "subtotal":
for name in measure_cols:
col = get_column_letter(headers.index(name) + 1)
ws[f"{col}{i}"] = f"=SUBTOTAL(9,{col}{group_start}:{col}{i - 1})"
group_start = i + 1
elif kind == "grand":
for name in measure_cols:
col = get_column_letter(headers.index(name) + 1)
ws[f"{col}{i}"] = f"=SUBTOTAL(9,{col}2:{col}{i - 1})" # ignores nested SUBTOTALs
wb.save(dest)
finally:
wb.close()
Formula results are not stored by openpyxl, so tools that read cached values — including pd.read_excel — see empty cells until the file has been opened and saved in Excel or recalculated by LibreOffice. The trade-off and the recalculation options are in fix openpyxl formulas not calculating.
Variant Fix 2: Two Grouping Levels
Region subtotals with channel subtotals inside them follow the same pattern with a second key. Generalise the sort key to a tuple per level:
# pip install "pandas>=2.2"
import pandas as pd
def nested_subtotals(frame: pd.DataFrame, levels: list[str], measures: list[str]) -> pd.DataFrame:
detail = frame.sort_values(levels, kind="stable").assign(row_type="detail", depth=len(levels))
parts = [detail]
for depth in range(len(levels), 0, -1):
keys = levels[:depth]
sub = detail.groupby(keys, sort=True)[measures].sum().reset_index()
sub["row_type"], sub["depth"] = f"subtotal_{depth}", depth - 1
for lower in levels[depth:]:
sub[lower] = "~total" # '~' sorts after letters and digits
parts.append(sub)
out = pd.concat(parts, ignore_index=True).sort_values(levels, kind="stable")
out[levels] = out[levels].replace("~total", "")
return out.reset_index(drop=True)
Map depth to outline levels when writing: detail rows at len(levels) + 1, each subtotal at its depth plus one. The ~total placeholder sorts after ordinary labels in ASCII order; if your labels themselves can start with ~ or non-ASCII letters, sort with an explicit order column instead.
Making the Report Print Well
Subtotal reports are the ones people print for meetings. Three page-setup settings stop the printout from falling apart: repeat the header row on every page, fit all columns to one page width, and avoid a page break splitting a region from its subtotal where possible.
# pip install "openpyxl>=3.1"
from pathlib import Path
from openpyxl import load_workbook
from openpyxl.worksheet.pagebreak import Break
def print_setup(dest: Path, table, max_rows_per_page: int = 45) -> None:
wb = load_workbook(dest)
try:
ws = wb["Sales"]
ws.print_title_rows = "1:1" # header on every printed page
ws.page_setup.orientation = "landscape"
ws.page_setup.fitToWidth = 1
ws.page_setup.fitToHeight = 0 # as many pages tall as needed
ws.sheet_properties.pageSetUpPr.fitToPage = True
rows_on_page = 0
for i, kind in enumerate(table["row_type"], start=2):
rows_on_page += 1
if kind == "subtotal" and rows_on_page > max_rows_per_page * 0.7:
ws.row_breaks.append(Break(id=i)) # break after a subtotal, not mid-group
rows_on_page = 0
ws.oddFooter.center.text = "Page &P of &N"
wb.save(dest)
finally:
wb.close()
Breaking after a subtotal once a page is roughly two-thirds full keeps most regions on one page without leaving pages nearly empty. Groups longer than a page will still split; the repeated header row keeps those continuation pages readable. &P and &N are Excel's page-number codes for the footer — the same placeholders appear in PDF generation, as in add page numbers and headers to PDF reports, if the report is later exported.
Verification
Check that each subtotal equals the sum of its group's detail rows, that the grand total equals the sum of detail rows only (not detail plus subtotals), and that outline levels are set as intended.
# pip install "pandas>=2.2" "openpyxl>=3.1"
from pathlib import Path
import pandas as pd
from openpyxl import load_workbook
def verify_subtotals(table: pd.DataFrame, dest: Path, group: str = "region",
measure: str = "revenue") -> None:
detail = table[table["row_type"] == "detail"]
subs = table[table["row_type"] == "subtotal"]
for _, sub in subs.iterrows():
name = sub["product"].removesuffix(" total")
expected = detail.loc[detail[group] == name, measure].sum()
assert abs(sub[measure] - expected) < 0.005, f"{name}: {sub[measure]} != {expected}"
grand = table.loc[table["row_type"] == "grand", measure].iloc[0]
assert abs(grand - detail[measure].sum()) < 0.005, "grand total includes subtotal rows"
wb = load_workbook(dest)
try:
ws = wb["Sales"]
levels = [ws.row_dimensions[i].outline_level for i in range(2, len(table) + 2)]
expected_levels = [{"detail": 2, "subtotal": 1, "grand": 0}[k] for k in table["row_type"]]
assert levels == expected_levels, "outline levels do not match row types"
finally:
wb.close()
print(f"{len(subs)} subtotals, grand total and outline levels verified")
The "grand total includes subtotal rows" assertion is the one that catches the most common bug: summing the whole measure column after subtotals were inserted doubles every value.
FAQ
Can I use Excel's own Subtotal command from Python?
Only by automating Excel itself (for example with pywin32 on Windows). The pandas-plus-openpyxl approach runs anywhere and produces the same visible result.
Why are my collapse buttons above the groups?summaryBelow defaults to what Excel last used; set ws.sheet_properties.outlinePr.summaryBelow = True explicitly.
How do I start with groups collapsed?
Set ws.row_dimensions[i].hidden = True on detail rows in addition to their outline level. Readers expand what they need.
Do subtotals work with pd.read_excel later?
Static values do. Filter them out with the label suffix, or keep a row_type column in the sheet if the file is meant to be read back by code.
Related
- Building Pivot Tables and Summaries for Excel — summaries, totals and reconciliation
- Fix Pivot Table MultiIndex Columns in Excel — clean headers for grouped columns
- Highlight Rows with Conditional Formatting — styling rows by rule instead of by position
- Automating Excel Report Generation — producing these reports on a schedule