Python Guide to Freezing and Splitting Excel Panes

When working with Excel spreadsheets that contain large amounts of data, users often need to keep certain rows or columns visible while scrolling through the rest of the worksheet. Freeze panes is the core feature designed for this purpose — it locks specified rows or columns in place so they remain on screen as the user scrolls. While this is straightforward to do manually, it becomes inefficient when you need to apply the same settings across multiple workbooks.

Automating freeze pane operations with Python can significantly streamline data processing workflows. Whether you're auto-freezing header rows when generating reports or applying consistent freeze settings in batch processing scenarios, a programmatic approach ensures consistency and repeatability. This article walks through how to freeze panes, retrieve freeze information, and unfreeze panes in Excel worksheets using Python.

Environment Setup

This article uses the Spire.XLS for Python library to work with Excel files. Install it via pip:

pip install Spire.XLS

Once installed, import the required modules in your Python script:

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

How Freeze Panes Works

Before diving into code, it helps to understand the mechanics behind freeze panes. Excel's freeze pane feature works by establishing a "split point": everything above and to the left of a given cell is locked in place, while everything below and to the right remains scrollable.

In the Spire.XLS API, the FreezePanes() method takes two parameters:

• row: The index of the first row below the frozen area (1-based)
• column: The index of the first column to the right of the frozen area (1-based)

For example, FreezePanes(2, 1) freezes row 1 (the header row) and allows scrolling starting from row 2.

Freeze the Top Row

This is the most common use case — freezing the first row of a data table as a header so that column names remain visible as the user scrolls down through the data.

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

# Create a workbook and load the file
workbook = Workbook()
workbook.LoadFromFile("input.xlsx")

# Get the first worksheet
sheet = workbook.Worksheets[0]

# Freeze the top row: scrolling starts from row 2, no columns frozen
sheet.FreezePanes(2, 1)

# Save the document
workbook.SaveToFile("FreezeTopRow.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

The parameters (2, 1) mean that row 2 and all rows above it are frozen, and column 1 and all columns to its left are frozen. Since column 1 is the leftmost column, only row 1 ends up being fixed in place.

Freeze the First Column

In some data tables, the first column contains row identifiers (such as product names or dates) that need to remain visible when scrolling horizontally:

# Get the worksheet
sheet = workbook.Worksheets[0]

# Freeze the first column: no rows frozen, scrolling starts from column 2
sheet.FreezePanes(1, 2)

# Save the document
workbook.SaveToFile("FreezeFirstColumn.xlsx", ExcelVersion.Version2013)

Setting the row parameter to 1 means no rows are frozen (there is no area above row 1 to freeze), while the column parameter of 2 freezes the first column.

Freeze Rows and Columns Simultaneously

In practice, it's common to freeze both header rows and identifier columns at the same time. For example, in a sales data table where row 1 contains column headers and column 1 contains product IDs, both need to stay visible while scrolling:

# Freeze the first row and first column simultaneously
# Parameters (3, 2) freeze the first two rows and the first column
sheet.FreezePanes(3, 2)

workbook.SaveToFile("FreezeRowsAndColumns.xlsx", ExcelVersion.Version2013)

FreezePanes(3, 2) freezes rows 1–2 and column A. The area starting from row 3 and column B becomes freely scrollable. This approach is well suited for complex tables with multi-row headers or when both row and column identifiers need to be pinned.

Retrieve Freeze Pane Information

In certain automation scenarios, you may need to check whether a worksheet already has frozen panes before deciding on the next step. The GetFreezePanes() method returns the row and column indices of the current freeze position:

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

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

# Retrieve the freeze pane position
indexs = sheet.GetFreezePanes()
rowIndex = indexs[0]
colIndex = indexs[1]

# Print the freeze position
print(f"Freeze row index: {rowIndex}, freeze column index: {colIndex}")

# A return value of 0 means that direction is not frozen
if rowIndex == 0 and colIndex == 0:
    print("No freeze panes are set on the current worksheet")

workbook.Dispose()

An index value of 0 indicates that the corresponding direction (row or column) is not frozen. This is useful when batch-processing multiple files, as it allows you to quickly identify which worksheets already have freeze panes configured and which do not.

Unfreeze Panes

If you need to modify a frozen worksheet or reconfigure the freeze area, the first step is to remove the existing freeze:

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

workbook = Workbook()
workbook.LoadFromFile("input.xlsx")

# Get the worksheet
sheet = workbook.Worksheets[0]

# Remove freeze panes
sheet.RemovePanes()

# Save the document
workbook.SaveToFile("UnfreezePanes.xlsx", ExcelVersion.Version2013)
workbook.Dispose()

The RemovePanes() method removes all freeze settings in both directions, restoring the worksheet to a fully scrollable state.

Practical Tips

Freeze position vs. data start position: Freeze pane parameters are based on absolute positions within the worksheet, not relative to the data range. If your data starts on row 3 and you want to freeze the first two rows as headers, use FreezePanes(3, 1) rather than FreezePanes(2, 1).

Combining freeze with filtering: Freezing the header row on a worksheet with auto-filter applied lets users see column headers at all times while filtering data, significantly improving the browsing experience. Apply the freeze first, then set up the filter.

Batch processing multiple worksheets: When a workbook contains multiple worksheets, you can iterate through all of them and apply a uniform freeze setting:

for i in range(workbook.Worksheets.Count):
    sheet = workbook.Worksheets[i]
    sheet.FreezePanes(2, 1)  # Freeze the top row on every sheet

Important note: Each worksheet can only have one freeze position at a time. Calling FreezePanes() again will overwrite the previous setting. To change the freeze position, there is no need to call RemovePanes() first — simply set the new position directly.

Conclusion

This article covered the complete workflow for managing freeze panes in Excel worksheets using Python, including freezing the top row, first column, both rows and columns simultaneously, retrieving freeze information, and removing freeze settings. These capabilities have practical value in batch report generation, automated data organization, and document template processing.

Combined with other Spire.XLS features such as conditional formatting, data validation, and cell merging, you can build more comprehensive Excel automation solutions.