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

Lock Cells and Protect a Sheet with openpyxl

Stop readers overwriting formulas in a generated report: unlock the input cells, enable sheet protection, choose which operations stay allowed, and verify it on the way out.

Ship a report with a formula column and somebody will type over it. Not maliciously — they will tab one cell too far, or paste a block that lands one column left of where they meant. Excel's sheet protection exists for exactly this, and openpyxl sets it in a few lines. The catch is that the mechanic runs backwards from how people expect: you do not lock the formulas, you unlock the inputs. This guide covers the pattern, the inverted permission flags, partial protection, and verifying the result. It is the practical core of Protecting and Sharing Excel Workbooks.

What a well-protected report sheet looks like to a reader A four-column sheet. The Region, Unit price and Revenue columns are locked and shown plain; the Units column is unlocked and tinted amber so it is obvious where input is expected. The Revenue column holds formulas that reference the input column, so the numbers update as the reader types, while the formulas themselves cannot be overwritten. only the tinted column accepts typing Region Units (enter) Unit price Revenue North 0 12.50 =B2*C2 South 0 11.00 =B3*C3 locked=False · tinted the reader can type here locked=True · the default formulas and labels are safe

Prerequisites

Bash
pip install openpyxl

One concept to hold before starting: locked is a cell style attribute, and it does nothing on its own. Excel only consults it when the sheet is protected. So a workbook where every cell is locked and protection is off behaves exactly like one where nothing is locked — which is why the flag catches people out.

Step 1 — Build a sheet worth protecting

A small forecast with a formula column gives the protection something to defend:

Python
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment

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

ws.append(["Region", "Units (enter)", "Unit price", "Revenue"])
for row, (region, price) in enumerate(
    [("North", 12.50), ("South", 11.00), ("West", 13.25)], start=2
):
    ws.append([region, 0, price, f"=B{row}*C{row}"])

ws.append([])
ws.append(["Total", None, None, "=SUM(D2:D4)"])

header = Font(bold=True, color="FFFFFF")
header_fill = PatternFill("solid", fgColor="4338CA")
for cell in ws[1]:
    cell.font = header
    cell.fill = header_fill
    cell.alignment = Alignment(horizontal="center")

for col, width in zip("ABCD", (16, 15, 13, 14)):
    ws.column_dimensions[col].width = width

wb.save("forecast.xlsx")

Step 2 — Unlock the inputs, then protect

The two halves, in the only order that works:

Python
from openpyxl import load_workbook
from openpyxl.styles import Protection, PatternFill

wb = load_workbook("forecast.xlsx")
ws = wb["Forecast"]

# 1. Clear the lock on the cells readers are meant to fill in.
entry_fill = PatternFill("solid", fgColor="FFF7E6")
for row in ws.iter_rows(min_row=2, max_row=4, min_col=2, max_col=2):
    for cell in row:
        cell.protection = Protection(locked=False)
        cell.fill = entry_fill

# 2. Turn protection on. Now the still-locked cells refuse edits.
ws.protection.sheet = True
ws.protection.enable()

wb.save("forecast_protected.xlsx")

Reversing the order does not work, and the failure is silent — enable() does not re-read the cells, but neither does it complain. What you get is a sheet where the protection is on and the unlock never registered, so nobody can type anywhere.

The tint is not decoration. On a protected sheet with no visual cue, a reader who clicks a locked cell sees a modal error with no explanation of where they should type. Two lines of fill saves the support message.

For a large or scattered input area, address it by range rather than by loop bounds:

Python
for row in ws["B2:B40"]:
    for cell in row:
        cell.protection = Protection(locked=False)

Step 3 — Choose what stays allowed

Why protection flags read backwards A note explaining that each protection attribute names the operation being protected, not the permission being granted. Setting autoFilter to True protects filtering, meaning readers cannot filter. Setting it to False leaves filtering unprotected, meaning readers can filter. Two example rows show the same flag producing opposite reader experiences, with the recommendation to set autoFilter and sort to False on any sheet carrying a table. the attribute names what is PROTECTED, not what is allowed autoFilter = True filtering is protected the reader CANNOT filter autoFilter = False filtering is not protected the reader CAN filter on any sheet with a table: set autoFilter and sort to False the default protects both, so the filter dropdowns appear but silently do nothing

Protection is a set of independent permissions, not a single switch. The naming is the thing to internalise: each flag names what is protected, so True blocks and False permits.

Python
ws.protection.sheet = True          # master switch: protection is ON

# Keep the sheet usable.
ws.protection.autoFilter = False    # filtering allowed
ws.protection.sort = False          # sorting allowed
ws.protection.selectLockedCells = False   # locked cells still selectable

# Block the destructive operations.
ws.protection.formatCells = True
ws.protection.insertRows = True
ws.protection.insertColumns = True
ws.protection.deleteRows = True
ws.protection.deleteColumns = True

ws.protection.enable()

The two worth setting on every report are autoFilter and sort. A protected sheet defaults to blocking both, which quietly disables the filter dropdowns added in creating Excel tables and autofilters with Python — the table looks normal and the controls simply do nothing.

FlagTrue meansSet to False when
autoFilterfiltering blockedthe sheet has a filter or table
sortsorting blockedreaders browse the data
formatCellsrestyling blockedalmost always leave True
insertRowsinserting blockedreaders append their own rows
deleteRowsdeleting blockedalmost always leave True
selectLockedCellslocked cells unselectableyou want values still copyable

Leaving selectLockedCells = False matters more than it sounds: with it set to True, readers cannot even click a locked cell to copy its value, which makes a report feel broken.

Step 4 — Protect only part of a sheet

Sometimes one group should edit a block that everyone else cannot. openpyxl supports Excel's "allow users to edit ranges" through ws.protection.add:

Python
from openpyxl import load_workbook
from openpyxl.worksheet.protection import SheetProtection
from openpyxl.workbook.protection import WorkbookProtection

wb = load_workbook("forecast.xlsx")
ws = wb["Forecast"]

ws.protection.sheet = True
ws.protection.password = "report"
ws.protection.enable()

# A named range that a specific team can unlock with their own password.
ws.protection.add(
    name="ForecastInputs",
    sqref="B2:B4",
    password="planning",
)

wb.save("forecast_partial.xlsx")

In practice, the simpler unlock-the-range approach covers most needs and does not require anyone to remember a second password. Reach for add only when genuinely different groups need different access to the same sheet.

Step 5 — Verify before shipping

Protection is state that a later stage can silently discard, so assert on it. This is a cheap test and it catches the most common regression in a reporting pipeline:

The three checks that prove a report is protected as intended Three assertions run against the saved workbook. The first confirms the sheet protection flag is set at all. The second confirms every cell in the intended input range has its locked attribute cleared, so readers can type. The third confirms the formula column is still locked, which catches an over-broad unlock. Together they detect both a missing protection step and one that unlocked too much. 1 · protection is on ws.protection.sheet is True catches a later to_excel that replaced the sheet 2 · inputs are unlocked every cell in B2:B4 has locked False catches the ordering bug that locks the whole sheet 3 · formulas still locked column D remains locked True catches an unlock range that was too wide
Python
from openpyxl import load_workbook

def assert_protected(path, sheet, editable="B2:B4", locked_col="D"):
    wb = load_workbook(path)
    ws = wb[sheet]

    assert ws.protection.sheet, f"{sheet} is not protected"

    for row in ws[editable]:
        for cell in row:
            assert cell.protection.locked is False, \
                f"{cell.coordinate} should be unlocked"

    for (cell,) in ws.iter_rows(min_row=2, max_row=4,
                                min_col=4, max_col=4):
        assert cell.protection.locked is not False, \
            f"{cell.coordinate} in the formula column is unlocked"

    return True

assert_protected("forecast_protected.xlsx", "Forecast")
print("protection verified")

Note is not False rather than is True in the third check: cells that were never touched carry the inherited default rather than an explicit True, so a strict identity test against True gives a false failure. Drop this into the suite described in testing Excel output with pytest.

Common pitfalls and fixes

SymptomCauseFix
Nothing is editableProtection enabled without unlocking any cellsAssign Protection(locked=False) to the input range first.
Everything is still editablews.protection.sheet never setSet sheet = True and call enable().
Filter dropdowns do nothingautoFilter defaults to blockedws.protection.autoFilter = False.
Readers cannot copy valuesselectLockedCells = TrueSet it to False.
Protection gone after the job runsA later to_excel replaced the sheetProtect as the final step.
Unlocking a whole column locked nothingAssigned to the column dimension, not the cellsIterate the cells; column_dimensions has no protection.
Password prompt never appearsPassword set but sheet not enabledBoth are required.
Formatting lost after protectingReassigned cell.protection on a styled cellProtection replaces only the protection part; check you did not also reassign cell.style.

Performance and scale notes

Assigning cell.protection creates a style entry, and openpyxl deduplicates identical ones — so unlocking a thousand cells with the same Protection(locked=False) object costs one style, not a thousand. Create the object once outside the loop rather than constructing a new one per cell:

Python
from openpyxl.styles import Protection

unlocked = Protection(locked=False)          # one object, reused
for row in ws.iter_rows(min_row=2, max_row=50_000, min_col=2, max_col=2):
    for cell in row:
        cell.protection = unlocked

Constructing inside the loop still deduplicates on save, but it allocates fifty thousand short-lived objects along the way, which is measurable on large sheets.

Two structural notes for big reports. Protection cannot be used with openpyxl's write_only mode — that mode streams rows and never holds cells to style — so a workbook that needs both bulk volume and protection has to be written in normal mode, or written streaming and then re-opened to protect. And Excel's ceiling of roughly 64,000 distinct cell formats counts protection variants too, which is another reason to reuse one shared Protection object rather than creating them ad hoc.

Where a report is genuinely large, the pragmatic split is the same one used for macro workbooks: keep the protected, formula-bearing summary small, and push bulk detail to a separate sheet or file written with the streaming approach in writing large DataFrames with write-only mode.

Conclusion

Sheet protection in openpyxl is two steps in a fixed order: clear locked on the cells readers should be able to edit, then set ws.protection.sheet = True and call enable(). Tint the input range so it is obvious where typing is expected. Remember the flags name what is protected, so set autoFilter and sort to False to keep the sheet browsable. Protect last, after all data is written, and assert on the result — a protection step that silently stopped working looks exactly like one that is fine.

Frequently asked questions

Why is my whole sheet read-only after protecting it? Every cell starts with locked=True, so enabling protection locks all of them. Clear the flag on the cells readers should be able to type into by assigning Protection(locked=False) before you enable protection.

Do I need a password on sheet protection? Not for the guard-rail effect — protection works with no password at all and still prevents accidental edits. A password only stops a reader clicking Unprotect Sheet, and it is easily removed, so treat it as a speed bump.

Why did autoFilter stop working after protection? The flags name what is protected, so True blocks and False permits. Set ws.protection.autoFilter = False and ws.protection.sort = False to keep filtering and sorting available on a protected sheet.

Can I protect only part of a sheet? Yes, in two ways. Leave the editable range unlocked, which is the usual approach, or add an unprotected range with its own password through ws.protection.add so a specific group can edit one block.

Does protection survive a pandas write? No. to_excel replaces the whole sheet, discarding protection along with comments and column widths. Apply protection as the final step, after all data has been written.