Python Excel AutomationPractical guides for developers
Start learning
Guide
Automating Reporting WorkflowsDeep dive

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.

Detail rows aggregate into KPIs on a front Summary sheet Raw detail rows are reduced with pandas groupby and sum into a few headline KPIs, which are written onto a Summary sheet placed as the leftmost first tab. Detail rows groupby .sum() Summary (1st tab) $2.4M Total revenue 312 Orders $7.7K Avg order

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 a grouped aggregation. 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

All three tables go through one pd.ExcelWriter, the same mechanism behind writing several DataFrames to one file. 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. Because that offset shifts every table down, the styling step below has to target the right rows — with startrow=1, the KPI header lands on row 2 and its data on rows 3 onward.

Row-by-row layout of the Summary sheet A spreadsheet grid with row numbers down the left edge. Row 1 is reserved for the title by startrow=1, row 2 is the styled KPI header, rows 3 and 4 hold the KPI values, rows 5 and 6 are a blank gap, and rows 7 onward hold the by-region breakdown table lower down the sheet. A B 1 2 3 4 5 6 7 8 9 10 Sales Summary Metric Total Revenue Total Units region North South West Value 44,100 441 revenue 26,700 9,800 7,600 blank gap between the two tables Row 1 — title kept free by startrow=1 Row 2 — KPI header styled bold + fill Rows 3–4 — KPI values number-formatted Rows 5–6 — blank gap Rows 7+ — by-region breakdown, offset lower

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 — swap #,##0 for a currency format on revenue rows if you want the $ sign baked in. 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")

# Number 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

SymptomCauseFix
Summary ends up as the rightmost tabIt was written after the detail sheetsMake the Summary the first to_excel() call, or reorder wb._sheets afterward.
KPIs disagree with the detailThe Summary was hard-coded or computed from stale dataAlways derive KPIs from the same DataFrame the detail sheet is written from, in the same run.
Number formats lost after reopeningStyling applied before pandas overwrote the cellsStyle after all to_excel() writes — reopen with load_workbook() and style last.
Report opens on the wrong tabwb.active never setSet wb.active = wb.sheetnames.index("Summary") before saving.
Styles target the wrong rowstartrow shifted the table down and the style code didn't account for itTrack 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.

Put the summary first, and make it open there

Where the summary sheet belongs A summary written last sits at the end of the tab strip and the workbook opens on the detail. Inserting it at position zero and setting it active means the reader sees the headline first. appended last last tab in the strip workbook opens on detail reader has to hunt inserted first, active first tab opens on the summary headline is unavoidable
Python
from openpyxl import load_workbook

wb = load_workbook("report.xlsx")
summary = wb.create_sheet("Summary", 0)          # index 0 = first tab
summary["A1"] = "Monthly summary"
summary["A3"], summary["B3"] = "Total revenue", 1_284_600.00
summary["B3"].number_format = "#,##0.00"

wb.active = 0
summary.sheet_view.showGridLines = False
wb.save("report_summary_first.xlsx")

Turning gridlines off on the summary alone is a small change with a disproportionate effect: it reads as a designed page rather than as a spreadsheet, which is exactly the distinction between a front page and a working sheet.

Keeping the KPI values as real numbers with a number format — rather than pre-formatted strings — means they remain usable in any formula a reader adds, and that a later job can read them back for the output validation step.

Compute the KPIs once

The figures on a summary sheet should come from the same computation that produced the detail, not from a second pass over the written workbook. Calculating once in pandas and writing both the detail and the KPIs from that single result removes any possibility of the two disagreeing — which is the failure readers notice first and trust least.

Label the period

A summary sheet should say which period it covers, in a cell rather than only in the filename. Reports get renamed, forwarded and printed, and the one that carries its own date is the one nobody has to ask about.

Ranges come from the data

The habit that keeps a monthly report correct is deriving every range, row count and column position from what was just written rather than from a number typed once. ws.max_row after the write, a header map built from row one, a table reference rebuilt on each run — each of those removes a class of failure that is silent rather than loud. A hardcoded range does not raise when the data outgrows it; it simply stops covering the rows nobody looks at, and the report is wrong in a way that takes months to notice. Deriving costs a line and removes the whole category.

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