Fixing Python xlsxwriter That Ignores freeze_panes When Workbook Has Multiple Sheets

You write a clean loop that adds freeze_panes(1, 0) to every worksheet, save the file, and open it in Excel — only to find that the header row is frozen on one sheet but not the others. Sometimes no sheets are frozen at all. The call didn't error out, so the bug is invisible until a user complains.

This is one of the most quietly frustrating xlsxwriter quirks because the fix is simple once you understand what's actually happening under the hood. This article walks through the root cause and gives you three concrete solutions you can apply right now.

What You'll Learn

• Why freeze_panes appears to be silently ignored on multi-sheet workbooks
• How xlsxwriter's active-sheet state interacts with pane settings
• Three reliable patterns for freezing panes across all worksheets
• How to verify the fix is actually written into the file
• Edge cases involving split_panes and set_selection that can undo your work

Prerequisites

This article assumes you are using xlsxwriter 3.x with Python 3.8 or later. The behavior described here also applies to earlier 1.x and 2.x releases. You should be comfortable creating workbooks and adding worksheets programmatically. No external dependencies beyond xlsxwriter are required for the examples.

How xlsxwriter Applies freeze_panes Internally

When you call worksheet.freeze_panes(row, col), xlsxwriter records the freeze instruction inside the worksheet object's internal state. It writes a <sheetView> XML element that includes a <pane> child element specifying the frozen row and column positions. That element also carries a topLeftCell attribute and an activePane attribute that tell Excel which pane gets keyboard focus when the sheet opens.

The critical detail is that xlsxwriter also writes a tabSelected attribute on the <sheetView> element. Only the sheet that is currently marked as the active sheet gets tabSelected="1". Excel uses this to decide which sheet to display on open. If the workbook's internal active-sheet pointer is misaligned, the pane settings on the other sheets can be read incorrectly by Excel — or in some versions, partially ignored at render time.

Why Multiple Sheets Break the Freeze

xlsxwriter tracks a single active sheet index at the workbook level. Every time you call workbook.add_worksheet(), xlsxwriter advances that pointer to the newly created sheet. By the time you've finished adding all your sheets, the active-sheet index is pointing at the last sheet you created.

Here's a minimal reproduction:

import xlsxwriter

workbook = xlsxwriter.Workbook("broken.xlsx")

for name in ["Jan", "Feb", "Mar"]:
    ws = workbook.add_worksheet(name)
    ws.freeze_panes(1, 0)          # Freeze row 1 on every sheet
    for col in range(5):
        ws.write(0, col, f"Header {col}")
    for row in range(1, 20):
        for col in range(5):
            ws.write(row, col, row * col)

workbook.close()

Open broken.xlsx and you'll notice that "Mar" is frozen correctly because it was the last sheet added (and therefore the active sheet), but "Jan" and "Feb" may not be frozen or may behave inconsistently depending on your Excel version. The freeze instruction is present in the XML for those sheets, but the sheetView state is not fully initialised because xlsxwriter only finalises cursor and pane state for the active sheet during the close sequence.

The Active Sheet Trap

The workbook's active-sheet pointer interacts with two other per-sheet settings: the cursor position (which cell is selected) and the split position (used by split_panes). When these settings are left at their defaults on non-active sheets, Excel can end up in an inconsistent state where the freeze instruction exists in the XML but the active pane reference points to the wrong place.

The result varies by Excel version. Excel for Microsoft 365 tends to respect the XML strictly and shows the freeze correctly on all sheets. Older Excel releases (2013, 2016) and LibreOffice Calc are more likely to drop the freeze on sheets that are not the active sheet at open time. Google Sheets, when importing xlsx files, is the most sensitive and will often ignore pane settings on non-active sheets entirely.

If you've run into similar issues with other worksheet-level settings, the same active-sheet mechanism is responsible for named ranges disappearing after workbook close — it's worth reading that article alongside this one.

Fix 1: Set freeze_panes Before Switching Sheets

The simplest fix is to ensure you configure all settings on a worksheet before you create the next one. xlsxwriter finalises each sheet's XML during workbook.close(), but some internal state is written incrementally as you add content. Keeping all per-sheet configuration together avoids the active-sheet pointer drifting out of sync.

import xlsxwriter

workbook = xlsxwriter.Workbook("fixed_v1.xlsx")
sheet_names = ["Jan", "Feb", "Mar"]

for name in sheet_names:
    ws = workbook.add_worksheet(name)

    # Configure panes immediately after creation, before any writes
    ws.freeze_panes(1, 0)
    ws.set_column(0, 4, 15)

    for col in range(5):
        ws.write(0, col, f"Header {col}")
    for row in range(1, 20):
        for col in range(5):
            ws.write(row, col, row * col)

workbook.close()

This pattern works reliably in most cases. The key discipline is: call freeze_panes immediately after add_worksheet, before any data writes or format applications. In practice this is enough to fix the problem for Excel 365 and most modern readers.

Fix 2: Use set_first_sheet() and activate() Together

For maximum compatibility — especially when targeting LibreOffice, Google Sheets, or older Excel releases — you need to explicitly tell xlsxwriter which sheet should be active when the file opens, and you need to mark the others as non-active. Use worksheet.activate() to set the active sheet and worksheet.set_first_sheet() to control which tab appears first in the tab bar.

import xlsxwriter

workbook = xlsxwriter.Workbook("fixed_v2.xlsx")
sheet_names = ["Jan", "Feb", "Mar"]
worksheets = []

for name in sheet_names:
    ws = workbook.add_worksheet(name)
    ws.freeze_panes(1, 0)
    for col in range(5):
        ws.write(0, col, f"Header {col}")
    for row in range(1, 20):
        for col in range(5):
            ws.write(row, col, row * col)
    worksheets.append(ws)

# Explicitly activate the first sheet so Excel opens there
worksheets[0].set_first_sheet()
worksheets[0].activate()

workbook.close()

Calling activate() on the first sheet forces xlsxwriter to write tabSelected="1" on sheet 0 instead of the last sheet. This causes Excel to compute the pane state for all sheets from a clean starting point, which means the freeze instructions on the non-active sheets are honoured correctly.

A similar technique is needed when you're dealing with row height mismatches after autofit — per-sheet state has to be finalised in the right order.

Fix 3: Reset the Cursor Position with set_selection()

When you freeze panes, Excel needs to know where the cursor sits inside the frozen pane. xlsxwriter defaults this to cell A1, but if you've written data in a way that moves the internal cursor, or if you're combining freeze_panes with other navigation calls, the active cell reference can become inconsistent.

The safest pattern for any sheet that uses freeze_panes(row, col) is to immediately follow it with a set_selection call that places the cursor in the first unfrozen cell:

import xlsxwriter

FREEZE_ROW = 1
FREEZE_COL = 0

workbook = xlsxwriter.Workbook("fixed_v3.xlsx")

for name in ["Jan", "Feb", "Mar"]:
    ws = workbook.add_worksheet(name)

    # Freeze and explicitly set the cursor to the first unfrozen cell
    ws.freeze_panes(FREEZE_ROW, FREEZE_COL)
    ws.set_selection(FREEZE_ROW, FREEZE_COL, FREEZE_ROW, FREEZE_COL)

    for col in range(5):
        ws.write(0, col, f"Header {col}")
    for row in range(1, 20):
        for col in range(5):
            ws.write(row, col, row * col)

worksheets_list = workbook.worksheets()
worksheets_list[0].activate()

workbook.close()

set_selection(row, col, row, col) with all four arguments set to the same cell creates a single-cell selection with no range highlight. When freeze_panes(1, 0) is in effect, the cursor should land on A2 (row index 1, col index 0), which is the top-left cell of the scrollable area. This removes any ambiguity in the XML about which pane owns the active cell.

Verifying the Fix in Excel

Don't rely solely on opening the file and scrolling. Here's a structured way to confirm that all sheets have their freeze correctly applied:

1. Open the generated file in Excel (or LibreOffice if that's your target).
2. Click on each sheet tab in turn. On each sheet, try scrolling down — the header row should stay visible.
3. Go to View > Freeze Panes on each sheet. If the freeze is active, the menu will show "Unfreeze Panes" as the available option. If it shows "Freeze Top Row", the freeze was not applied to that sheet.
4. For a programmatic check, open the .xlsx file as a zip archive and inspect the xl/worksheets/sheet1.xml, sheet2.xml, etc. files. Each should contain a <pane> element inside <sheetViews>.

unzip -p fixed_v2.xlsx xl/worksheets/sheet1.xml | grep -o '<pane[^/]*/>'

You should see output like <pane ySplit="1" topLeftCell="A2" activePane="bottomLeft" state="frozen"/> for every sheet file. If a sheet file is missing the <pane> element, the freeze was never written and you need to revisit where freeze_panes is being called.

Common Pitfalls and Edge Cases

Combining freeze_panes with split_panes

You cannot use freeze_panes and split_panes on the same worksheet. If you call both, the last one wins and the earlier call is silently discarded. Always pick one. If your sheet needs both frozen headers and resizable split regions, consider using only freeze_panes and instructing users to drag the freeze line manually.

Freezing both rows and columns

When you freeze both a row and a column — for example freeze_panes(1, 1) — the active pane becomes bottomRight instead of bottomLeft. The set_selection call in Fix 3 must also target the correct quadrant. Use ws.set_selection(1, 1, 1, 1) to place the cursor in the first unfrozen cell of the bottom-right pane.

Using freeze_panes with a cell string argument

xlsxwriter supports both freeze_panes(1, 0) and freeze_panes("A2") (cell string notation). The two forms are equivalent. If you use the string form, make sure your set_selection call uses the same target cell — ws.set_selection(1, 0, 1, 0) for "A2".

Sheets added after workbook operations

If you add a sheet, do some workbook-level operations (like defining named ranges), and then add more sheets, the active-sheet pointer resets on each add_worksheet call. Always call activate() on your intended first sheet as the very last step before workbook.close(), not during the creation loop.

This ordering issue is also behind the bug described in the article on cell formats being dropped on merged cells — the workbook finalisation sequence is sensitive to the order you make these calls.

Large workbooks and memory mode

If you're using xlsxwriter's constant_memory=True mode for large files, the worksheet is written to a temp file incrementally. In this mode, freeze_panes must be called before any write calls on that sheet, because the <sheetViews> element is written at the start of the sheet XML. Calling it after any data write will silently have no effect in constant-memory mode.

workbook = xlsxwriter.Workbook("large_file.xlsx", {"constant_memory": True})
ws = workbook.add_worksheet("Data")

# MUST come before any ws.write() calls in constant_memory mode
ws.freeze_panes(1, 0)

for row in range(100_000):
    ws.write_row(row, 0, [row, row * 2, row * 3])

workbook.close()

If you're generating reports with many thousands of rows, also check the related article on text truncation in wrapped cells, which becomes more common at scale.

Wrapping Up

The freeze_panes bug on multi-sheet workbooks comes down to xlsxwriter's active-sheet pointer and the order in which per-sheet XML state is finalised. Here are the concrete steps to take away:

• Call freeze_panes immediately after add_worksheet, before writing any data to that sheet.
• After building all sheets, call worksheets[0].activate() to ensure Excel opens on the correct sheet with a clean pane state.
• Add ws.set_selection(FREEZE_ROW, FREEZE_COL, FREEZE_ROW, FREEZE_COL) after each freeze_panes call for maximum cross-application compatibility.
• If you are using constant_memory=True, treat freeze_panes as the first call you make on a worksheet, with no exceptions.
• Validate the output by inspecting the raw XML inside the .xlsx zip archive — don't trust visual inspection alone if you're targeting multiple Excel versions.