Create Groups and Outlines in Excel Using Python

A project plan usually carries two different levels of information at once: the phases, and the tasks inside each phase along with their owners and durations. Flattened into one list, the reader has to work out the hierarchy while scanning; in a status meeting, it is far more useful to show the phases first and expand a phase only when its tasks matter. Excel's outline feature exists for exactly this: grouped rows get a plus/minus control in the margin, level buttons labeled 1, 2, 3 appear above the row headers, and one click collapses or expands a block.

Doing it by hand means selecting rows and clicking Data → Group for every level, which is easy to get wrong once the nesting deepens and has to be redone for every new file. Python lets the structure live in code so it can be applied to any report with the same shape. This article covers creating worksheet groups, controlling their collapsed state, and removing groups with Spire.XLS for Python.

Why Group Rows in a Script

• Levels defined in code: which rows belong to which level is a parameter, not a mouse drag.
• Consistent across files: one rule set applies to every plan or monthly report with the same layout.
• Controlled initial state: the script decides which groups start collapsed, so the file opens in its final view.

Setting Up the Environment

Install the Spire.XLS library:

pip install Spire.XLS

Then import the required modules in your script:

from spire.xls import *
from spire.xls.common import *

How Grouping and Outline Levels Work

Grouping is done through the worksheet's GroupByRows() and GroupByColumns() methods:

GroupByRows(startRow, endRow, isCollapsed)
GroupByColumns(startColumn, endColumn, isCollapsed)

The first two arguments are an inclusive, 1-based range of rows or columns, and the third sets whether the group starts collapsed. Each call places the selected rows or columns into a new outline level. Calling GroupByRows() again on rows that are already inside a group creates the next level within it, which is how nested outlines are built.

Grouping Rows

The example below puts rows 1 through 5 into a single group:

from spire.xls import *
from spire.xls.common import *

workbook = Workbook()
workbook.LoadFromFile("GroupRowsAndColumns.xls")
sheet = workbook.Worksheets[0]

# Group rows 1 to 5 and leave the group expanded
sheet.GroupByRows(1, 5, False)

workbook.SaveToFile("GroupRows.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

Open the saved file and the outline area appears to the left of the row headers, with a minus sign beside rows 1 to 5 to collapse them. Passing False as the third argument keeps the group expanded after creation; passing True stores it collapsed, so the file opens with the group already folded away.

Building Nested Outline Levels

Real reports rarely stop at one level. The following example creates a project plan, puts every task row into an outer group, and then groups the tasks of each phase separately:

from spire.xls import *
from spire.xls.common import *

workbook = Workbook()
sheet = workbook.Worksheets[0]

# Define a heading style
style = workbook.Styles.Add("style")
style.Font.Color = Color.get_CadetBlue()
style.Font.IsBold = True

# Show summary rows above the detail rows
sheet.PageSetup.IsSummaryRowBelow = False

# Write phases and tasks
sheet.Range["A1"].Value = "Project plan for project X"
sheet.Range["A1"].CellStyleName = style.Name

sheet.Range["A3"].Value = "Set up"
sheet.Range["A3"].CellStyleName = style.Name
sheet.Range["A4"].Value = "Task 1"
sheet.Range["A5"].Value = "Task 2"
sheet.Range["A4:A5"].BorderAround(LineStyleType.Thin)
sheet.Range["A4:A5"].BorderInside(LineStyleType.Thin)

sheet.Range["A7"].Value = "Launch"
sheet.Range["A7"].CellStyleName = style.Name
sheet.Range["A8"].Value = "Task 1"
sheet.Range["A9"].Value = "Task 2"
sheet.Range["A8:A9"].BorderAround(LineStyleType.Thin)
sheet.Range["A8:A9"].BorderInside(LineStyleType.Thin)

# Create the outer group first, then the inner ones
sheet.GroupByRows(2, 9, False)   # Level 1: rows 2 to 9
sheet.GroupByRows(4, 5, False)   # Level 2: tasks of "Set up"
sheet.GroupByRows(8, 9, False)   # Level 2: tasks of "Launch"

workbook.SaveToFile("CreateNestedGroup.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

The order matters: build the outer group (rows 2–9) before the inner ones (rows 4–5 and 8–9). Reversing the order still leaves the inner groups inside the outer one, but working from the widest range inward keeps the hierarchy unambiguous. After saving, the level buttons 1 2 appear above the row headers: level 1 leaves the two phase names visible, level 2 expands down to the tasks.

sheet.PageSetup.IsSummaryRowBelow = False places the summary row above its detail rows. Excel assumes summaries sit below details by default, while a phase name normally precedes its tasks, so this property has to be flipped explicitly.

Grouping Columns

Column grouping mirrors row grouping and is useful for folding away detail columns in a wide sheet:

from spire.xls import *
from spire.xls.common import *

workbook = Workbook()
workbook.LoadFromFile("GroupRowsAndColumns.xls")
sheet = workbook.Worksheets[0]

# Group columns 1 to 3
sheet.GroupByColumns(1, 3, False)

workbook.SaveToFile("GroupColumns.xlsx", ExcelVersion.Version2010)
workbook.Dispose()

The outline controls appear above the column headers, independent of any row groups, so a single sheet can carry both kinds of grouping.

Expanding and Collapsing Existing Groups

When the groups already exist in a file, their state can be changed through a cell range instead of recreating them:

from spire.xls import *
from spire.xls.common import *

workbook = Workbook()
workbook.LoadFromFile("Template_Xls_3.xlsx")
sheet = workbook.Worksheets[0]

# Expand rows 16 to 19, including any parent group
sheet.Range["A16:G19"].ExpandGroup(GroupByType.ByRows, ExpandCollapseFlags.ExpandParent)

# Collapse rows 10 to 12
sheet.Range["A10:G12"].CollapseGroup(GroupByType.ByRows)

workbook.SaveToFile("ExpandAndCollapseGroups.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

CollapseGroup() and ExpandGroup() both operate on a cell range, and the second argument GroupByType.ByRows specifies row-based groups (use GroupByType.ByColumns for columns). ExpandGroup() also accepts a third argument, ExpandCollapseFlags.ExpandParent: when the target group sits inside a deeper level, expanding it alone leaves it hidden behind a collapsed parent, and this flag expands the parents as well.

Removing Groups

Groups that are no longer needed can be removed with UngroupByRows(), which takes a row range:

from spire.xls import *
from spire.xls.common import *

workbook = Workbook()
workbook.LoadFromFile("Template_Xls_3.xlsx")
sheet = workbook.Worksheets[0]

# Remove the group on rows 10 to 12
sheet.UngroupByRows(10, 12)
# Remove the group on rows 16 to 19
sheet.UngroupByRows(16, 19)

workbook.SaveToFile("UngroupExcelCells.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

Ungrouping removes only the outline structure; cell values, styles, and formulas are unaffected.

Practical Tips

• Work from the outside in: call nested grouping from the widest row range down to the narrowest so the hierarchy stays readable.
• Row numbers start at 1: the range passed to GroupByRows() uses Excel row numbers and includes both endpoints, unlike Python's zero-based indexing.
• The collapsed state is saved: the third argument decides the initial view of the file, so pass True when readers should see the folded structure first.
• Set the summary position explicitly: when phase names sit above their tasks, set PageSetup.IsSummaryRowBelow to False.
• Release resources: call workbook.Dispose() after each workbook, and in batch jobs load, process, and save one file at a time.

Conclusion

This article covered creating row and column groups and outline levels in Excel with Python: grouping and nesting with GroupByRows() and GroupByColumns(), controlling the state of existing groups with CollapseGroup() and ExpandGroup() (optionally with ExpandCollapseFlags.ExpandParent), positioning summary rows through PageSetup.IsSummaryRowBelow, and removing groups with UngroupByRows(). Because grouping changes only the view and never the underlying data, the same code can turn a flat detail sheet into a collapsible structure without touching its contents.