Indent Cells to Show a Hierarchy with openpyxl

Hierarchies are everywhere in finance and operations reports: a chart of accounts that rolls up into cost groups, an organisation chart, a bill of materials, a product catalogue with categories and subcategories. Printed as a flat table they are hard to read; the convention readers expect is an outline — children indented under their parent, parents in bold with totals, and branches that can be folded away. Excel supports all of it natively, and openpyxl can set each piece. This page, part of Styling Excel Cells with openpyxl, turns a parent–child table of expense accounts into an indented, rolled-up, collapsible report.

Prerequisites

pip install openpyxl pandas

accounts.xlsx lists thirteen rows: groups such as People and Technology, a subgroup (Software, under Technology), and the accounts that carry amounts. Each row names its parent; the top row, Operating expenses, has none.

Step 1 — Work out each row's depth and order

The depth of a row is the number of steps to the top of the tree. Follow parents recursively, caching results so each row is computed once. The display order is a depth-first walk: each parent followed immediately by its children, in the order the table lists them:

from functools import lru_cache
import pandas as pd

acc = pd.read_excel("accounts.xlsx", dtype={"Code": str, "Parent": str})
parent = dict(zip(acc["Code"], acc["Parent"]))
children = {}
for code, par in zip(acc["Code"], acc["Parent"]):
    children.setdefault(par if pd.notna(par) else None, []).append(code)

@lru_cache(maxsize=None)
def depth(code):
    p = parent[code]
    return 0 if pd.isna(p) else depth(p) + 1

def walk(code=None):
    for child in children.get(code, []):
        yield child
        yield from walk(child)

order = list(walk())
acc = acc.set_index("Code").loc[order].reset_index()
acc["Depth"] = acc["Code"].map(depth)
print(acc[["Code", "Name", "Depth"]].to_string(index=False))

Reading the codes as text matters: account numbers such as 6000 and group codes such as OPEX share a column, and a mix of types would break the parent lookup. Hardware, listed after Software in the table, still appears under Technology after Software's two accounts — the walk keeps each branch together.

Hand-maintained hierarchies go wrong in two ways: a row names a parent that does not exist, or a chain of parents loops back on itself. Either one breaks the depth calculation, so check the table before building anything from it:

def tree_problems(parent):
    problems = []
    for code, p in parent.items():
        if pd.notna(p) and p not in parent:
            problems.append(f"{code}: parent {p} does not exist")
    for code in parent:
        seen, p = {code}, parent[code]
        while pd.notna(p) and p in parent:
            if p in seen:
                problems.append(f"{code}: cycle through {p}")
                break
            seen.add(p)
            p = parent[p]
    return problems

print(tree_problems(parent) or "tree is valid")
print(tree_problems({**parent, "6400": "MARKETING", "OPEX": "SAAS"})[:3])

The real table is valid. The second call adds a row whose parent is missing and makes Operating expenses a child of Software, which creates a loop; both are reported instead of sending the depth function into endless recursion. Run the check whenever the hierarchy comes from a sheet people edit.

Step 2 — Roll the amounts up the tree

Parent rows show the total of everything beneath them. Add each account's amount to every ancestor on its path:

totals = dict(zip(acc["Code"], acc["Amount"].fillna(0)))
for code, amount in zip(acc["Code"], acc["Amount"]):
    if pd.isna(amount):
        continue
    p = parent[code]
    while pd.notna(p):
        totals[p] += amount
        p = parent[p]
acc["Total"] = acc["Code"].map(totals)
acc["IsParent"] = acc["Code"].isin(children)
print(acc.loc[acc["IsParent"], ["Name", "Total"]].to_string(index=False))

People totals 133,300, Premises 41,150, Technology 16,120 — including its Software subgroup's 12,920 — and Operating expenses 190,570. Computing the totals in Python keeps them as values; to make them live, write SUM formulas over each parent's child rows instead, the way Build a Budget vs Actual Variance Report writes its subtotals.

Step 3 — Indent, bold and write

Now write the rows, setting Alignment(indent=depth) on the name cell. Each indent level shifts the text right by about three characters' width, and the value itself stays clean — "Rent", not " Rent". Parents get bold text and a top border to read as headings:

from openpyxl import Workbook
from openpyxl.styles import Alignment, Border, Font, Side

wb = Workbook(); ws = wb.active; ws.title = "Expenses"
ws.append(["Account", "Code", "Amount"])
for c in ws[1]:
    c.font = Font(bold=True)
money = "#,##0"
bold = Font(bold=True)
top = Border(top=Side(style="thin"))
indents = {}
for r in acc.itertuples(index=False):
    ws.append([r.Name, r.Code, r.Total])
    row = ws.max_row
    name = ws.cell(row=row, column=1)
    name.alignment = indents.setdefault(r.Depth, Alignment(indent=r.Depth))
    ws.cell(row=row, column=3).number_format = money
    if r.IsParent:
        for c in ws[row]:
            c.font = bold
            c.border = top
ws.column_dimensions["A"].width = 28
ws.column_dimensions["C"].width = 12
print([(ws.cell(row=i, column=1).value, ws.cell(row=i, column=1).alignment.indent) for i in range(2, 6)])

One Alignment object per depth is reused for every row at that depth. The maximum indent Excel accepts is 250, far more than any readable hierarchy; beyond four or five levels, consider splitting the report instead.

Step 4 — Group rows so branches can collapse

Excel's outline feature adds buttons in the left margin that fold a group of rows away. Give each row an outline_level equal to its depth, and tell Excel that summary rows sit above their detail — the default assumes totals below:

for i, d in enumerate(acc["Depth"], start=2):
    if d > 0:
        ws.row_dimensions[i].outline_level = min(d, 7)
ws.sheet_properties.outlinePr.summaryBelow = False
ws.freeze_panes = "B2"
wb.save("expenses_outline.xlsx")

from openpyxl import load_workbook
book = load_workbook("expenses_outline.xlsx")
check = book.active
print({check.cell(row=i, column=1).value: check.row_dimensions[i].outline_level for i in range(2, 9)})

Excel supports up to seven outline levels, hence the min. With summaryBelow = False, the collapse button for People sits on the People row itself, and clicking the level buttons at the top of the margin shows the report at one, two or three levels of detail. Group and Outline Rows in Excel with openpyxl covers outlines in more depth.

Step 5 — Start with branches collapsed

For a summary-first report, open it with the detail folded away. Hide the rows below the level you want to show; Excel treats hidden rows inside an outline as collapsed, and the buttons expand them:

for i, d in enumerate(acc["Depth"], start=2):
    if d >= 2:
        check.row_dimensions[i].hidden = True
book.save("expenses_collapsed.xlsx")
print("hidden rows:", [i for i in range(2, check.max_row + 1) if check.row_dimensions[i].hidden])

Opened in Excel, the report shows Operating expenses and its three groups; clicking a group's button expands its accounts. Leave the rows visible when the file is mainly printed, since collapsed rows do not print, and when readers are likely to filter or sort, since hidden rows are easy to forget about.

Common pitfalls

• Leading spaces instead of indent. They change the value and break lookups and sorting.
• Codes read as numbers. Mixed numeric and text codes break parent lookups; read them as text.
• Totals below by default. Set summaryBelow = False when parents are above their children.
• Too many levels. Excel outlines stop at seven levels; very deep trees need a different layout.
• Cycles in the parent column. A row that is its own ancestor makes the depth function recurse forever; validate the tree first.

Performance notes

The recursive depth function with a cache and the depth-first walk are linear in the number of rows; ten thousand accounts take milliseconds. Styling per row is the slowest step, which is why the example reuses one Alignment per depth and one Font.

Conclusion

Compute each row's depth by following its parents, order rows with a depth-first walk so branches stay together, and roll amounts up to every ancestor. Write the names with Alignment(indent=depth) rather than leading spaces, bold the parent rows, and set outline_level on each row with summaryBelow = False so branches collapse under their headings — optionally hiding the deeper levels for a summary-first view.

Frequently asked questions

How do I indent a cell with openpyxl? Set the alignment's indent property: cell.alignment = Alignment(indent=2). Each indent level moves the text right by roughly the width of three characters. Use it instead of typing leading spaces, which break sorting, lookups and exports.

How do I work out how deep each row is in a hierarchy? From a parent–child table, follow each row's parent until you reach a row with no parent, counting the steps. A small recursive function with a cache does this for thousands of rows instantly.

How do I make rows collapsible in Excel with Python? Give each detail row an outline level by setting outline_level on its entry in ws.row_dimensions. Excel then shows grouping buttons in the margin. Set summaryBelow to False in the sheet's outline properties when parent rows sit above their children.

Why not just put spaces in front of the names? Leading spaces become part of the value. " Rent" no longer matches "Rent" in a lookup, sorts before every letter, and appears with spaces in any export. The indent property changes only how the cell is displayed.

Related

• Up one level: Styling Excel Cells with openpyxl — alignment, fonts and borders.
• Group and Outline Rows in Excel with openpyxl — outlines in depth.
• Building Finance and Operations Reports — where charts of accounts come from.
• Standardise Category Values with a Mapping Table — mapping accounts to groups.