Python Excel AutomationPractical guides for developers
Start learning
Guide
Formatting And Charting Excel Reports With PythonDeep dive

Group and Outline Rows in Excel with openpyxl

Add collapsible groups to a generated report: row and column outlines, nested levels, collapsed-by-default state, summary rows below or above, and subtotal formulas.

A report that shows every row is honest but overwhelming; one that shows only totals hides the workings. Excel's outlining gives you both — totals visible, detail one click away — and openpyxl writes it in a single call per group. It suits exactly the reports that generated workbooks tend to be: a region with its months, a month with its transactions, a total with the rows behind it. This guide groups rows and columns, nests levels, sets the collapsed state, and pairs the outline with SUBTOTAL formulas that respond to it. It belongs to Creating Excel Tables and Autofilters with Python.

An outlined sheet, collapsed and expanded Collapsed, the sheet shows one total row per region; expanded, the same sheet reveals the detail rows beneath each total, controlled from the outline margin. collapsed — what opens first expanded — one click later + North total 128,400 + South total 96,220 + East total 51,130 | Jan 41,100 | Feb 43,200 | Mar 44,100 - North total 128,400 The detail is present in both — only its visibility differs

Prerequisites

Bash
pip install openpyxl pandas

Group a block of rows

group_rows takes the first and last row of the detail — not the summary row — and an outline level:

Python
from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.title = "Sales"

ws.append(["Region", "Month", "Revenue"])
ws.append(["North", "Jan", 41100])
ws.append(["North", "Feb", 43200])
ws.append(["North", "Mar", 44100])
ws.append(["North total", "", "=SUBTOTAL(109,C2:C4)"])

ws.group_rows(2, 4, outline_level=1, hidden=True)
wb.save("outline.xlsx")

hidden=True makes the group start collapsed, which is usually what a summary report wants — a reader sees the totals and opens the detail they care about. Leave it out and the outline controls appear with everything expanded.

Build the outline from grouped data

In practice the groups come from the data, so write each block and record where it started:

Python
import pandas as pd
from openpyxl import Workbook

df = pd.DataFrame({
    "region": ["North"] * 3 + ["South"] * 2 + ["East"] * 3,
    "month": ["Jan", "Feb", "Mar", "Jan", "Feb", "Jan", "Feb", "Mar"],
    "revenue": [41100, 43200, 44100, 48000, 48220, 16100, 17300, 17730],
})

wb = Workbook()
ws = wb.active
ws.title = "Sales"
ws.append(["Region", "Month", "Revenue"])

for region, group in df.groupby("region", sort=True):
    first = ws.max_row + 1
    for row in group.itertuples():
        ws.append([region, row.month, float(row.revenue)])
    last = ws.max_row
    ws.append([f"{region} total", "", f"=SUBTOTAL(109,C{first}:C{last})"])
    ws.group_rows(first, last, outline_level=1, hidden=True)

ws.append(["Grand total", "", f"=SUBTOTAL(109,C2:C{ws.max_row})"])
wb.save("outline.xlsx")

SUBTOTAL(109, …) rather than SUM is deliberate: function 109 ignores rows hidden by a filter, and — crucially — nested SUBTOTAL results, so the grand total does not double-count the per-region totals sitting inside its range.

Nest a second level

Levels are just integers, and a deeper group inside a shallower one nests automatically:

Python
ws.group_rows(3, 6, outline_level=2, hidden=True)     # weeks inside a month
ws.group_rows(2, 12, outline_level=1, hidden=True)    # months inside a region

Excel then shows numbered buttons 1 2 3 in the margin, so a reader can jump the whole sheet to a level rather than opening groups one at a time. Two levels is usually the practical limit for comprehension; the eight Excel allows is a specification, not a recommendation.

How outline levels map onto the rows Level one groups the months within a region, level two groups the weeks within a month, and the summary row for each group sits below its detail by default. Two nested levels, one summary row each row 2 week 1 detail — level 2 row 3 week 2 detail — level 2 row 4 January total — closes level 2 row 5 February detail — level 2 row 6 North total — closes level 1

Put the summary above the detail

Some report conventions put the total first. Excel assumes the opposite, so tell it:

Python
ws.sheet_properties.outlinePr.summaryBelow = False
ws.sheet_properties.outlinePr.summaryRight = False

Set this once per sheet, before or after the grouping — it changes where Excel draws the outline bracket and which row the collapse control attaches to. Getting it wrong produces the confusing result where clicking the control hides the total instead of the detail.

Group columns

The same idea horizontally, using column letters, is how you hide twelve monthly columns behind a total:

Python
ws.group_columns("B", "M", outline_level=1, hidden=True)   # Jan–Dec

The column holding the annual total sits outside the group, so it stays visible when the months are collapsed. This pairs well with a sparkline column — see Add sparklines to an Excel report with xlsxwriter.

Apply grouping after writing with pandas

pandas has no concept of outlines, so the sequence is: write with pandas, reopen with openpyxl, group:

Python
import pandas as pd
from openpyxl import load_workbook

df.to_excel("sales.xlsx", index=False, sheet_name="Sales")

wb = load_workbook("sales.xlsx")
ws = wb["Sales"]

start = 2
for region, group in df.groupby("region", sort=False):
    end = start + len(group) - 1
    ws.group_rows(start, end, outline_level=1, hidden=True)
    start = end + 1
wb.save("sales.xlsx")

Because the frame was written in its existing order, the row arithmetic is a simple running offset — but only if the DataFrame is sorted by the grouping column first. Sort it before writing, or the groups will interleave and the outline will be nonsense.

Style the summary rows so the structure reads

An outline tells Excel which rows belong together; formatting tells the reader. Give each summary row a fill and a bold font, and indent the detail so the hierarchy is visible even when everything is expanded:

Python
from openpyxl.styles import Alignment, Font, PatternFill

total_fill = PatternFill("solid", start_color="DDEBF7")
bold = Font(bold=True)

for row in ws.iter_rows(min_row=2, max_row=ws.max_row):
    label = row[0].value or ""
    is_total = str(label).endswith("total")
    for cell in row:
        if is_total:
            cell.fill = total_fill
            cell.font = bold
    if not is_total:
        row[0].alignment = Alignment(indent=1)

Alignment(indent=1) shifts the detail labels one step right, which does more for legibility than any amount of colour. Keep the indent small — two steps already looks like a mistake on a printed page.

Formatting that makes the outline legible on paper Indenting detail labels and filling summary rows keeps the hierarchy visible when the sheet is fully expanded or printed, where the outline controls do not appear. Outline controls do not print — formatting does Jan 41,100 Feb 43,200 North total 128,400 Indented detail, filled and bold totals — readable with no outline at all

Read an existing outline back

When a workbook arrives already outlined — from a colleague, or from a previous run — you can inspect and rebuild the structure rather than guessing at it:

Python
from openpyxl import load_workbook

wb = load_workbook("sales.xlsx")
ws = wb["Sales"]

for row_index, dim in sorted(ws.row_dimensions.items()):
    if dim.outlineLevel:
        state = "collapsed" if dim.hidden else "expanded"
        print(f"row {row_index}: level {dim.outlineLevel}, {state}")

row_dimensions only holds entries for rows that carry some property, so the loop is cheap even on a large sheet. Two practical uses: flipping every group to expanded before a PDF export, since collapsed rows do not print, and clearing an inherited outline before applying your own:

Python
for dim in ws.row_dimensions.values():
    dim.outlineLevel = 0
    dim.hidden = False

The PDF point is worth emphasising — a report delivered collapsed prints as totals only, which is either exactly what you wanted or a missing appendix nobody notices until the meeting.

Common pitfalls and gotchas

  • Including the summary row in the group. Group the detail only, or collapsing hides the total too.
  • Unsorted data. Groups must be contiguous rows; sort by the grouping column before writing.
  • SUM instead of SUBTOTAL. A grand total using SUM over a range containing per-group totals double-counts everything.
  • Forgetting summaryBelow. If your totals are above the detail, the outline controls attach to the wrong rows.
  • Expecting the outline to survive pandas. Reading the file back with pandas gives values only; re-writing it drops the outline.

Performance and scale notes

Grouping is metadata — a level number per row — so it costs nothing at write time and nothing in file size. What it costs is Excel's rendering when a sheet has thousands of groups: opening such a file is noticeably slower, and the outline margin becomes a wall of brackets. Keep groups meaningful and few, typically one per business grouping rather than one per row. If a sheet needs more than a hundred or so, the report probably wants a summary sheet with links into detail sheets instead — the structure in Add a table of contents sheet with hyperlinks in Excel.

Conclusion

group_rows turns a long report into a summary a reader can drill into, without removing anything. Group the detail rows and leave the summary row outside the group, start collapsed with hidden=True, use SUBTOTAL(109, …) so nested totals do not double-count, and set summaryBelow to match your layout. Apply the grouping as a final openpyxl pass after pandas has written the data, and sort by the grouping column first so every group is contiguous.

Frequently asked questions

What is the difference between grouping and hiding rows? Hiding removes rows from view with no way back except unhiding them. Grouping adds an outline control in the margin, so a reader collapses and expands the detail themselves — the report stays complete but starts summarised.

How many levels can I nest? Excel supports up to eight outline levels. In practice two or three — region, then month, then detail — is as deep as a reader will follow.

Why do my collapse controls appear on the wrong side? The outline's summary row is assumed to be below the group by default. If your totals sit above the detail, set ws.sheet_properties.outlinePr.summaryBelow = False.

Can I group columns too? Yes, with group_columns, using column letters. It is the usual way to hide a block of monthly columns behind a total.

Do outlines survive a pandas round trip? No. Reading with pandas gives you values only, so apply grouping after writing, with openpyxl, as the final step.