Freeze Panes and Set Zoom with XlsxWriter

The first few seconds with a report decide whether people find it easy to use. If they open it to a sheet scrolled halfway down, at a zoom where the numbers are tiny, and the column headings disappear as soon as they scroll, they will struggle with it however good the figures are. All of that is controlled by a sheet's view settings, which are saved in the file: which panes are frozen, the zoom level, whether gridlines show, which sheet is active and which cell is selected. XlsxWriter sets each one with a single method call. This page goes through them on a three-sheet report — a summary, a wide detail table and a notes sheet — and reads the file back to confirm what Excel will show. It belongs to Building Excel Reports with XlsxWriter.

Prerequisites

pip install xlsxwriter pandas openpyxl

The view settings are written into each sheet's XML, so they work the same in Excel, LibreOffice and most online viewers. openpyxl is used only to inspect the result.

Step 1 — Freeze the header row and the first column

import numpy as np
import pandas as pd

rng = np.random.default_rng(8)
months = pd.period_range("2025-10", periods=12, freq="M").strftime("%b %y")
detail = pd.DataFrame(rng.integers(20_000, 90_000, (40, 12)), columns=months)
detail.insert(0, "Product", [f"Product {i:02d}" for i in range(1, 41)])

xw = pd.ExcelWriter("sales_views.xlsx", engine="xlsxwriter")
wb = xw.book
summary = wb.add_worksheet("Summary")        # added first, so it is the first tab
detail.to_excel(xw, sheet_name="Detail", index=False, startrow=2)
ws = xw.sheets["Detail"]
ws.write("A1", "Monthly sales by product", wb.add_format({"bold": True, "font_size": 14}))
ws.freeze_panes(3, 1)                        # rows above row 4 and the column left of B stay put
ws.set_column("A:A", 14)
ws.set_column("B:M", 10, wb.add_format({"num_format": "#,##0"}))

freeze_panes(row, col) takes the zero-based position of the first cell that scrolls: everything above that row and left of that column stays on screen. Here the table starts at row 3 (zero-based 2) after a title and a blank line, so freeze_panes(3, 1) keeps the title, the blank row and the column headings fixed, and the product name column pinned on the left, while twelve months of numbers scroll freely beneath and beside them. The arguments also accept an A1 reference — freeze_panes("B4") means the same thing — which is easier to read when the position is fixed. The most common mistake is counting from one: freeze_panes(1, 0) freezes only the first row, which is right for a table whose headings are in row 1 and wrong for this one. A wide table needs both directions frozen; a long narrow one needs only the header row.

Step 2 — Zoom, gridlines and where the view starts

ws.set_zoom(90)                              # fit more of a wide table on screen
ws.set_top_left_cell("A1")
ws.set_selection("B4")                       # the cursor starts on the first number

summary.write("A1", "Sales summary", wb.add_format({"bold": True, "font_size": 18}))
summary.write_row("A3", ["Total", "Best month", "Products"])
summary.write_row("A4", [int(detail[months].to_numpy().sum()), months[int(detail[months].sum().argmax())], len(detail)])
summary.set_zoom(130)                        # a sparse sheet reads better larger
summary.hide_gridlines(2)                    # 2 = hide on screen and in print
summary.set_tab_color("#1F3864")
summary.activate()                           # opens on this sheet

Zoom is set per sheet, between 10 and 400 percent. A wide detail table benefits from slightly less than 100% so a full year of columns fits on a normal screen; a summary sheet with a few large numbers reads better at 120–130%. hide_gridlines(2) removes the grid on screen and in print, which makes a summary look like a page rather than a spreadsheet; hide_gridlines(1) hides them in print only, which is XlsxWriter's default.

Three methods decide where the reader starts. activate() chooses the sheet that is open when the file is opened — by default it is the first. set_selection() puts the cursor on a cell, here the first number of the table rather than the title. set_top_left_cell() scrolls the view so a given cell is in the top-left corner, useful for a sheet whose interesting part is not at the top. Tabs appear in the order the sheets are created, which is why the summary sheet was added to the workbook before pandas wrote the detail table — activate() chooses the open sheet but does not move it. Tab colours help readers find the sheet they need in a workbook with many tabs, and work best when they carry meaning — one colour for summaries, another for detail.

Step 3 — Split panes, the alternative to freezing

notes = wb.add_worksheet("Notes")
for r in range(60):
    notes.write(r, 0, f"Note {r + 1}: assumptions and definitions for the report")
notes.split_panes(15 * 10, 0)                # split 10 default rows down (height in points)
notes.set_tab_color("#808080")
xw.close()

Split panes divide the window into two or four independently scrollable areas, without locking anything in place. They suit a long sheet where the reader wants to compare two distant parts — the definitions at the top with a note sixty rows down. Unlike freeze_panes, split_panes takes the position of the split in points and character widths, not rows and columns, because the user can drag the split bar afterwards; 15 points is the default row height, so 150 points places the split about ten rows down. Freeze and split are alternatives: a sheet can have one or the other, not both.

Step 4 — Check the view settings

from openpyxl import load_workbook

wb2 = load_workbook("sales_views.xlsx")
print("tab order:", wb2.sheetnames, "| active:", wb2.active.title)
for ws in wb2.worksheets:
    v = ws.sheet_view
    pane = v.pane
    print(f"{ws.title:8} zoom={v.zoomScale} gridlines={v.showGridLines} tab={ws.sheet_properties.tabColor.rgb if ws.sheet_properties.tabColor else None} "
          f"pane={(pane.state, pane.topLeftCell, pane.xSplit, pane.ySplit) if pane else None} selection={v.selection[-1].activeCell}")   # last entry = the active pane

Reading the file back shows every setting as Excel will apply it: the summary first in the tab order and active, at 130% with gridlines off and a navy tab; the detail sheet at 90% with a frozen pane whose top-left scrolling cell is B4; the notes sheet with a split pane measured in twips (twentieths of a point) and a grey tab. With frozen panes a sheet stores one selection per pane — the top-right, bottom-left and bottom-right areas — and the one that matters is the active, scrolling pane, which is why the code reads the last entry: B4, as set. Gridlines show as None where they were left at the default, which means visible, and the notes sheet's zoom is None for the default of 100%. A check like this is a cheap test for a report that people rely on, because nothing else would notice if a refactor dropped a frozen header.

A sensible default for reports

When pandas writes the sheets, as here, the view settings go on the XlsxWriter worksheet objects that ExcelWriter exposes through xw.book and xw.sheets, and they must be set before the writer is closed — after close() the file is finished and nothing more can be added. The same applies to any formatting done through the writer.

Most reports can use the same few rules. Freeze the heading row on every table, and the first column too if the table is wider than one screen. Open on the summary, with the cursor in a sensible place and the view scrolled to the top. Use 100% for tables, a little less for very wide ones, and a little more for sparse summary sheets. Hide gridlines on presentation sheets and keep them on data sheets, where they help the eye follow a row. Putting these rules in one helper function that every report calls keeps a family of reports consistent, and a reader who learns one of them knows how to use the rest.

Common pitfalls

• Counting rows from one. freeze_panes takes zero-based positions; (1, 0) freezes row 1 only.
• Freezing below a title block. Freeze at the first data row, not after row 1.
• Using split units as rows. split_panes is in points and character widths.
• Opening on the last sheet written. Call activate() on the sheet readers should see first.
• Zoom set once for all sheets. Zoom is per sheet; set it where it matters.

Conclusion

XlsxWriter sets each sheet's view with one call: freeze_panes(row, col) to keep headings and the first column on screen, split_panes for independent scrolling views, set_zoom per sheet, hide_gridlines(2) for presentation sheets, activate, set_selection and set_top_left_cell for where the reader starts, and set_tab_color for navigation. Read the file back with openpyxl to confirm the settings in a test.

Frequently asked questions

How do I freeze the top row with XlsxWriter? Call worksheet.freeze_panes(1, 0); to freeze the top row and first column, use freeze_panes(1, 1).

How do I set the zoom level in XlsxWriter? Call worksheet.set_zoom(120) on each sheet; the value is a percentage between 10 and 400.

How do I choose which sheet is open when the file opens? Call activate() on that worksheet; otherwise the first sheet is active.

What is the difference between freeze_panes and split_panes? Frozen panes lock rows and columns in place; split panes create independently scrolling areas that the user can resize, positioned in points rather than rows.

Related

• Up one level: Building Excel Reports with XlsxWriter — reports with XlsxWriter.
• Freeze the Header Row in Excel with openpyxl — the openpyxl way.
• Hide Gridlines and Set the Zoom Level with openpyxl — view settings in openpyxl.
• Set Print Options, Headers and Footers with XlsxWriter — how the sheet prints.