Python Excel AutomationPractical guides for developers
Start learning
Guide
Formatting And Charting Excel Reports With PythonDeep dive

Freeze the Header Row in Excel with openpyxl

Freeze the header row in Excel from Python with openpyxl using ws.freeze_panes. Freeze rows, columns, or both, understand the anchor cell, and unfreeze — runnable.

When a report is taller than the screen, the column headers scroll out of view and every number loses its label. Freezing the header row pins it in place so it stays visible as you scroll. With openpyxl it is a single attribute — ws.freeze_panes — but the anchor-cell rule trips people up. This guide, part of Styling Excel Cells with openpyxl, covers freezing rows, columns, both, and removing the freeze. Every block runs in order against a sample workbook built first.

freeze_panes A2 keeps the header row pinned while the body scrolls Setting freeze_panes to A2 pins the header row above the anchor cell so it stays visible while everything below it scrolls. Header row — pinned A2 anchor cell row 2 row 3 row 4 … scrolls ws.freeze_panes = "A2"

Prerequisites

You need Python 3.8+ and the openpyxl library. Freezing panes works on any workbook openpyxl can load, so if you already write reports with openpyxl for Excel file manipulation you have everything you need. Install the library:

Bash
pip install openpyxl

Build a sheet with enough rows that scrolling matters:

Python
from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.title = "Ledger"

ws.append(["Date", "Account", "Description", "Amount"])
for i in range(1, 41):
    ws.append([f"2024-08-{i:02d}", f"ACC-{i:03d}", f"Entry {i}", i * 100])

wb.save("frozen_report.xlsx")
print("Sample workbook created with", ws.max_row, "rows")

Freeze the header row

Set ws.freeze_panes to the cell below the rows you want frozen and right of the columns you want frozen. To freeze just row 1, anchor at "A2" — everything above row 2 (that is, row 1) stays pinned:

Python
from openpyxl import load_workbook

wb = load_workbook("frozen_report.xlsx")
ws = wb["Ledger"]

ws.freeze_panes = "A2"   # row 1 stays visible while scrolling

wb.save("frozen_report.xlsx")
print("Header row frozen")

How the anchor cell works

The anchor is the top-left cell of the unfrozen (scrolling) region. Everything above it freezes, and everything to its left freezes. Read it as "freeze up to here":

The anchor cell B2 splits the sheet into frozen and scrolling regions A spreadsheet grid with anchor cell B2 highlighted. Row 1 and column A are shaded as frozen, the block from B2 down and right is shaded as the scrolling region. The freeze boundary runs along the top and left of the anchor. A caption states the anchor digit is one below the last frozen row and the anchor letter one right of the last frozen column. A B C D 1 2 3 4 row 1 frozen col A frozen B2 scrolls ↓→ Frozen — stays pinned Scrolls freely B2 anchor cell first cell of the scrolling region Anchor digit 2 = one below the last frozen row 1 · anchor letter B = one right of the last frozen column A
AnchorFrozenUse case
"A2"Row 1Pin the header row
"B1"Column APin a label column
"B2"Row 1 and column APin both at once
"C2"Row 1 and columns A–BPin a header plus two label columns
NoneNothingRemove all freezing

So the digit in the anchor is one greater than the last frozen row, and the letter is one greater than the last frozen column.

Freeze the first column

To keep a label column visible while scrolling sideways, anchor in column B of row 1:

Python
from openpyxl import load_workbook

wb = load_workbook("frozen_report.xlsx")
ws = wb["Ledger"]

ws.freeze_panes = "B1"   # column A stays visible while scrolling right

wb.save("frozen_report.xlsx")
print("First column frozen")

Freeze both the header row and the first column

Anchoring at "B2" freezes row 1 and column A together — the most common layout for a wide, tall report where you scroll in both directions:

Python
from openpyxl import load_workbook

wb = load_workbook("frozen_report.xlsx")
ws = wb["Ledger"]

ws.freeze_panes = "B2"   # row 1 and column A both pinned

wb.save("frozen_report.xlsx")
print("Header row and first column frozen")

Remove the freeze

Setting ws.freeze_panes = None clears any frozen panes so the whole sheet scrolls freely again:

Python
from openpyxl import load_workbook

wb = load_workbook("frozen_report.xlsx")
ws = wb["Ledger"]

ws.freeze_panes = None   # unfreeze everything

wb.save("frozen_report.xlsx")
print("Freeze removed")

Freeze panes on every sheet

freeze_panes is a per-worksheet attribute, so a multi-sheet dashboard needs it set on each sheet you want pinned. Loop over wb.worksheets:

Python
from openpyxl import load_workbook

wb = load_workbook("frozen_report.xlsx")

for sheet in wb.worksheets:
    sheet.freeze_panes = "A2"   # header row on every sheet

wb.save("frozen_report.xlsx")
print("Froze the header on", len(wb.worksheets), "sheet(s)")

Common pitfalls

SymptomCauseFix
Header still scrolls awayAnchored at "A1""A1" freezes nothing; use "A2" to pin row 1
Two header rows wanted, only one frozenOff-by-one anchorAnchor below the last header row: "A3" freezes rows 1–2
Freeze missing on some tabsfreeze_panes is per worksheetSet it on every sheet, e.g. loop wb.worksheets
Froze the wrong columnLetter is one past the last frozen column"B1" freezes column A; "C1" freezes A–B
Freeze ignored after a pandas writepandas overwrote the sheetApply freeze_panes after the pandas DataFrame export, reopening with openpyxl

A note on scale

Freezing is metadata on the worksheet, not per-cell data, so it costs nothing as the sheet grows — a 1,000,000-row export freezes its header just as cheaply as a 10-row one. There is no performance reason to skip it on large reports.

Freezing more than the header

freeze_panes takes the cell that becomes the top-left of the scrolling area, so everything above and to the left of it is pinned. That one rule covers every arrangement a report needs:

Three freeze arrangements and the cell each uses Setting freeze_panes to A2 pins the header row. Setting it to B1 pins the first column. Setting it to B2 pins both the header row and the first column at once. freeze_panes = "A2" the header row is pinned columns scroll freely the usual choice freeze_panes = "B1" column A is pinned rows scroll freely for wide tables freeze_panes = "B2" header and first column both pinned at once for wide and long
Python
from openpyxl import load_workbook

wb = load_workbook("report.xlsx")
for ws in wb.worksheets:
    if ws.max_row > 1:
        ws.freeze_panes = "B2"          # labels and header stay visible
    ws.sheet_view.zoomScale = 100
wb.save("report_frozen.xlsx")

Applying it in a loop over every sheet is the habit worth forming: a multi-tab workbook where one sheet is frozen and the others are not looks unfinished, and the check costs one line.

Two details cause confusion. freeze_panes = "A1" removes the freeze entirely rather than pinning nothing — it is the way to clear a template's existing panes. And the setting is per sheet view, so it survives a pandas rewrite only if applied afterwards, like every other piece of decoration.

Freeze panes or a printed title row?

They solve the same problem in different media. freeze_panes keeps the header visible while scrolling on screen; print_title_rows repeats it at the top of every printed page. A report that will be both read and printed wants both, and setting them together takes two lines:

Python
ws.freeze_panes = "A2"
ws.print_title_rows = "1:1"

Neither affects the data, and forgetting the second is the reason a printed report's second page is a wall of anonymous numbers.

Apply it to every sheet

A workbook where one tab has a frozen header and the others do not feels unfinished, and the inconsistency is entirely accidental — it comes from freezing the sheet that was being worked on. Looping over wb.worksheets and freezing each one that has data costs a line, and it is the kind of uniformity readers notice only when it is missing.

Freezing is not protecting

A frozen pane keeps the header visible; it does nothing to stop a reader editing it. The two are often wanted together on a delivered report, and they are separate settings — freeze_panes for the view, ws.protection for the edits. Applying only the first and assuming the second is a common misunderstanding, and it surfaces as a report that comes back with the header row overwritten.

Ranges come from the data

The habit that keeps a monthly report correct is deriving every range, row count and column position from what was just written rather than from a number typed once. ws.max_row after the write, a header map built from row one, a table reference rebuilt on each run — each of those removes a class of failure that is silent rather than loud. A hardcoded range does not raise when the data outgrows it; it simply stops covering the rows nobody looks at, and the report is wrong in a way that takes months to notice. Deriving costs a line and removes the whole category.

Small settings, large effect

Freezing a header is one of a handful of one-line settings — alongside column widths, a sensible zoom and the active sheet — that decide whether a workbook feels finished. None of them affects a single number, and collectively they are what a reader responds to before reading anything.

Applying them together in a finishing function, over every sheet, is what makes that impression consistent across a report rather than dependent on which tab someone was working on last.

Frequently asked questions

Which cell do I anchor to freeze the header row?"A2". The anchor is the first scrolling cell, so everything above it — row 1 — stays frozen. "A1" freezes nothing.

How do I freeze the top two rows? Anchor at "A3". The row digit is always one greater than the last row you want frozen, so "A3" pins rows 1 and 2.

Can I freeze a row and a column at the same time? Yes. Anchor at the intersection of the first scrolling row and column. "B2" freezes row 1 and column A together.

Why is the freeze only on one sheet?freeze_panes is set per worksheet. Iterate wb.worksheets and assign it to each sheet that needs a pinned header.

How do I remove a freeze? Set ws.freeze_panes = None and save. That clears all frozen panes and the sheet scrolls normally again.

Conclusion

Freezing panes in openpyxl is one line: ws.freeze_panes set to the top-left cell of the scrolling region. "A2" pins the header row, "B1" pins the first column, "B2" pins both, and None clears it. Remember the anchor is one row below and one column right of what you want frozen, and that the attribute lives on each worksheet — so loop your sheets when a workbook has several.