Add a Summary Sheet to an Excel Report with Python

Compute KPIs with pandas groupby, write a styled Summary sheet first, then detail sheets, and make the Summary the active leftmost tab — all with openpyxl.

A report that opens on raw rows makes the reader do the work. A Summary sheet does it for them: the totals, the key ratios, and a small breakdown table on the front page, with the detail behind it. This page builds that Summary tab step by step — compute the KPIs with pandas, write the Summary first so it lands leftmost, style the block, and set it as the active tab. It is part of Building Multi-Sheet Excel Dashboards.

Prerequisites

You need pandas for the aggregation and openpyxl as the engine and styling layer:

Bash

pip install pandas openpyxl

The rest of the page uses a single sample sales DataFrame, built inline in each snippet so every block runs on its own.

Step 1: compute the KPIs with pandas

Derive the headline numbers from your data, not by hand. Totals come from sum(); a breakdown comes from groupby. Keep the breakdown as a flat DataFrame with as_index=False so it writes cleanly:

Python

import pandas as pd

sales = pd.DataFrame({
    "region": ["North", "South", "North", "West", "South", "West"],
    "product": ["Widget", "Gadget", "Widget", "Gizmo", "Gadget", "Widget"],
    "revenue": [12500, 9800, 14200, 7600, 11100, 8300],
    "units":   [125, 98, 142, 76, 111, 83],
})

total_rev = sales["revenue"].sum()
total_units = sales["units"].sum()
avg_price = round(total_rev / total_units, 2)
top_region = sales.groupby("region")["revenue"].sum().idxmax()

kpis = pd.DataFrame({
    "Metric": ["Total Revenue", "Total Units", "Avg Unit Price", "Top Region"],
    "Value":  [total_rev, total_units, avg_price, top_region],
})
by_region = sales.groupby("region", as_index=False)["revenue"].sum()

print(kpis)
print(by_region)

You now have two things for the front page: a KPI table and a small per-region total table.

Step 2: write the Summary first, then the detail

Sheet order follows the order of your to_excel() calls, so write the Summary sheet first to make it the leftmost tab. Put the KPI block at the top and the breakdown table a few rows below it, on the same sheet, using startrow. Then write the detail sheet:

Python

import pandas as pd

sales = pd.DataFrame({
    "region": ["North", "South", "North", "West"],
    "revenue": [12500, 9800, 14200, 7600],
    "units":   [125, 98, 142, 76],
})
kpis = pd.DataFrame({
    "Metric": ["Total Revenue", "Total Units"],
    "Value":  [sales["revenue"].sum(), sales["units"].sum()],
})
by_region = sales.groupby("region", as_index=False)["revenue"].sum()

with pd.ExcelWriter("report.xlsx", engine="openpyxl") as writer:
    kpis.to_excel(writer, sheet_name="Summary", index=False, startrow=1)
    # place the breakdown table below the KPI block
    start = len(kpis) + 4
    by_region.to_excel(writer, sheet_name="Summary",
                       index=False, startrow=start)
    sales.to_excel(writer, sheet_name="Detail", index=False)

print("Summary tab written first, Detail behind it")

startrow=1 leaves row 1 free for a title. The breakdown starts a few rows under the KPI block so the two tables read as distinct sections.

Step 3: style the KPI block

A Summary earns its place by being readable at a glance. Reopen the workbook and apply a title, bold headers, a fill behind the KPI labels, and a number format on the values. openpyxl styles are set cell by cell on the loaded worksheet:

Python

from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill, Alignment

wb = load_workbook("report.xlsx")
ws = wb["Summary"]

# Title in the reserved top row
ws["A1"] = "Sales Summary"
ws["A1"].font = Font(size=14, bold=True)

# Bold the KPI header row (row 2, since startrow=1 shifted it down)
header_fill = PatternFill("solid", fgColor="DDEBF7")
for cell in ws[2]:
    if cell.value is not None:
        cell.font = Font(bold=True)
        cell.fill = header_fill
        cell.alignment = Alignment(horizontal="left")

# Currency format on the Value column for the KPI rows
for row in ws.iter_rows(min_row=3, max_row=4, min_col=2, max_col=2):
    for cell in row:
        if isinstance(cell.value, (int, float)):
            cell.number_format = "#,##0"

ws.column_dimensions["A"].width = 18
ws.column_dimensions["B"].width = 16
wb.save("report.xlsx")
print("Styled the Summary KPI block")

For the full styling vocabulary — borders, conditional fills, themed colors — see Styling Excel Cells with openpyxl.

Step 4: make the Summary the active, frozen front page

Even though you wrote it first, set it explicitly as the active tab so Excel opens on it, and freeze the top rows so the title and KPIs stay put while scrolling:

Python

from openpyxl import load_workbook

wb = load_workbook("report.xlsx")
wb.active = wb.sheetnames.index("Summary")  # open here on launch
wb["Summary"].freeze_panes = "A3"           # pin title + KPI header
wb.save("report.xlsx")
print("Active tab:", wb.active.title)

Setting wb.active by index is the reliable way to control which tab the reader sees first, independent of sheet order.

Common pitfalls

Symptom	Cause	Fix
Summary ends up as the rightmost tab	It was written after the detail sheets	Make the Summary the first `to_excel()` call, or reorder `wb._sheets` afterward.
KPIs disagree with the detail	The Summary was hard-coded or computed from stale data	Always derive KPIs from the same DataFrame the detail sheet is written from, in the same run.
Number formats lost after reopening	Styling applied before pandas overwrote the cells	Style after all `to_excel()` writes — reopen with `load_workbook()` and style last.
Report opens on the wrong tab	`wb.active` never set	Set `wb.active = wb.sheetnames.index("Summary")` before saving.
Styles target the wrong row	`startrow` shifted the table down and the style code didn't account for it	Track the offset: with `startrow=1`, the header is row 2 and data starts at row 3.

Scale note

Recompute KPIs from the in-memory DataFrame rather than reading the detail sheet back from disk — reading back is slower and risks parsing formatted strings as text. For very large detail sets, aggregate once into the small tables the Summary needs and write the heavy detail separately; the Summary stays cheap to build no matter how big the underlying data grows.

Frequently asked questions

Should I compute KPIs from the DataFrame or read them back from the Excel detail sheet? Compute from the DataFrame in the same run. Reading the sheet back is slower and can misread formatted cells. Keeping a single source DataFrame guarantees the Summary and detail agree.

How do I make the Summary the first tab? Write it with the first to_excel() call so it lands leftmost. If it was written later, sort wb._sheets so "Summary" comes first before saving.

Why did my cell styles vanish? You styled before pandas wrote the data, so the to_excel() call overwrote the styled cells. Always reopen the finished file with load_workbook() and apply styles as the last step.

The Summary opens on the wrong tab — why? Sheet order and the active tab are separate. Set wb.active to the Summary's index explicitly; Excel opens on whatever was active when the file was saved.

Conclusion

A good Summary sheet is computed, not typed: pandas sum() and groupby produce the KPIs and the breakdown, you write the Summary first so it sits leftmost, you style the block after the data is written, and you set it as the active, frozen front page. Drive everything from one source DataFrame in a single run and the front page can never drift out of sync with the detail behind it.

Where to go next

Building Multi-Sheet Excel Dashboards — add a chart and cross-sheet navigation to this Summary.
Write Multiple DataFrames to One Excel File — the writer mechanics behind the multi-sheet write above.
Styling Excel Cells with openpyxl — fonts, fills, borders, and number formats in depth.