Remove Sheet Protection from a Workbook You Own with openpyxl

Protected templates are common in reporting: the budget template with locked formulas, the monthly pack whose structure is locked so nobody renames the data sheet. Sooner or later the team that owns those files needs to change them — a new column, a corrected formula, a sheet added to fifty regional copies — and unprotecting each file by hand in Excel is slow and error-prone. openpyxl can turn protection off, make the change and put the protection back exactly as it was. This guide, part of Protecting and Sharing Excel Workbooks, shows how, for workbooks you own or are responsible for maintaining.

Prerequisites

pip install openpyxl

A protected template to work on — a budget sheet with protection, a locked structure and a formula that needs fixing:

import re
import zipfile
from copy import copy

from openpyxl import Workbook, load_workbook
from openpyxl.workbook.protection import WorkbookProtection
from openpyxl.worksheet.protection import SheetProtection

wb = Workbook()
ws = wb.active
ws.title = "Budget"
ws.append(["Item", "Amount"])
ws.append(["Rent", 1200])
ws.append(["Staff", 5400])
ws["B4"] = "=SUM(B2:B2)"                         # the bug: should include B3
ws.protection.sheet = True
ws.protection.password = "team-password"
ws.protection.formatColumns = False              # readers may still resize columns
wb.security = WorkbookProtection(workbookPassword="team-password", lockStructure=True)
wb.save("budget_template.xlsx")

Step 1 — See what protection a workbook has

Before changing anything, list the protection on each sheet and on the workbook. openpyxl reads both the legacy password hashes it writes itself and the salted SHA-512 hashes current Excel versions write:

def describe_protection(path):
    wb = load_workbook(path)
    lines = [f"structure locked: {bool(wb.security and wb.security.lockStructure)}"]
    for sheet in wb.worksheets:
        p = sheet.protection
        kind = p.algorithmName or ("legacy hash" if p.password else "no password")
        lines.append(f"{sheet.title}: protected={bool(p.sheet)} ({kind})")
    return lines

print(describe_protection("budget_template.xlsx"))
# ['structure locked: True', 'Budget: protected=True (legacy hash)']

Knowing which kind of protection is in place tells you what you are about to change and what you must put back. A sheet marked protected=False with a password is unusual but possible — someone unprotected it in Excel without removing the password setting.

Step 2 — Keep a copy, then unprotect

Copy the protection objects before touching them. Then replace each sheet's protection with a fresh, empty SheetProtection and clear the workbook's structure lock:

wb = load_workbook("budget_template.xlsx")
saved_sheets = {sheet.title: copy(sheet.protection) for sheet in wb.worksheets}
saved_workbook = copy(wb.security)

for sheet in wb.worksheets:
    sheet.protection = SheetProtection()
wb.security = None

A fresh SheetProtection() is off and has no password, so nothing is written to the sheet at all. Setting only sheet.protection.sheet = False also works, but leaves the password hash and permission flags in place — harmless, but confusing for the next person who inspects the file. Cells keep their locked and hidden flags either way; those take effect only while a sheet is protected. The hidden flag is the one to remember: on a protected sheet it hides a cell's formula from the formula bar, so while protection is off — and in any unprotected copy you save along the way — those formulas are visible to anyone who opens the file.

Step 3 — Make the change and restore the protection

With protection off, edit as usual. Then assign the saved objects back. The stored hashes go back into the file unchanged, so the team's existing password keeps working in Excel — without the script ever needing to know it:

wb["Budget"]["B4"] = "=SUM(B2:B3)"                # the fix
wb["Budget"].column_dimensions["B"].width = 14

for title, protection in saved_sheets.items():
    wb[title].protection = protection
wb.security = saved_workbook
wb.save("budget_template_fixed.xlsx")
print(describe_protection("budget_template_fixed.xlsx"))
# ['structure locked: True', 'Budget: protected=True (legacy hash)']

The same works for workbooks protected in a recent Excel version: the SHA-512 hash, salt and spin count are copied and written back as they were. Strictly, openpyxl does not enforce protection at all, so the edits would also succeed with protection left on; switching it off and back explicitly makes the script's intent clear, and the pattern also covers tools that do respect protection.

Step 4 — Maintain many copies at once

Protected templates tend to be copied — one per region, per client, per year. A loop applies the same fix to every file and records what it did:

for region in ["North", "South"]:
    load_workbook("budget_template.xlsx").save(f"budget_{region}.xlsx")

def fix_budget(path):
    wb = load_workbook(path)
    sheets = {s.title: copy(s.protection) for s in wb.worksheets}
    structure = copy(wb.security)
    for s in wb.worksheets:
        s.protection = SheetProtection()
    wb.security = None
    changed = wb["Budget"]["B4"].value != "=SUM(B2:B3)"
    wb["Budget"]["B4"] = "=SUM(B2:B3)"
    for title, protection in sheets.items():
        wb[title].protection = protection
    wb.security = structure
    wb.save(path)
    return changed

print({region: fix_budget(f"budget_{region}.xlsx") for region in ["North", "South"]})
# {'North': True, 'South': True}

Reporting whether each file actually needed the change, rather than just rewriting it, makes the run safe to repeat — a second run reports False everywhere — and gives you a record of which copies were touched. Keep the originals until someone has opened a fixed copy in Excel and confirmed that the protection prompts as before, and write the fixed files to a separate folder first when the change is large.

Step 5 — Prove the protection came back unchanged

"The protection is restored" is a claim worth checking automatically, because a maintenance script that quietly drops a permission flag — say, the right to resize columns — annoys readers for months. Compare every protection attribute of the original file with the fixed one:

def protection_state(path):
    wb = load_workbook(path)
    state = {"workbook": dict(wb.security) if wb.security else {}}
    for sheet in wb.worksheets:
        state[sheet.title] = dict(sheet.protection)
    return state

before = protection_state("budget_template.xlsx")
after = protection_state("budget_template_fixed.xlsx")
print(before == after)                                  # True
print(after["Budget"].get("formatColumns"))             # 0 — resizing still allowed

openpyxl's protection objects can be turned into dictionaries of their XML attributes, which makes the comparison a single equality test: the password hash, every permission flag and the workbook lock must all match. Run it for every file the maintenance loop touched, and fail the run on any difference. The formatColumns flag shows the kind of detail that survives this way — it was set when the template was created, and it is still there after the fix.

Encrypted files are a different thing

Everything above concerns sheet and structure protection, which are settings inside a readable file. A workbook saved with a password to open is encrypted; openpyxl cannot load it and there is nothing to switch off. Decrypting it requires the real password — see Open a Password-Protected Excel File with Python. And whichever kind of protection is involved, only change files you own or are responsible for; protection set by someone else is a request not to edit, and removing it without permission is not a technical question.

Common pitfalls

• Forgetting to restore. A maintenance script that unprotects and saves ships unprotected templates. Restore in the same function, as above, so the two steps cannot be separated by a later edit to the script.
• Restoring the wrong sheet's settings. Key saved settings by sheet title and restore by title; sheet order can change.
• Saving the unprotected state by accident. If the script fails between unprotecting and restoring, a wb.save in an exception handler writes the unprotected file. Save only after restoring, and let failures leave the original untouched.
• Assuming protection hides data. Unprotected or not, every value and formula is readable by any tool. Protection is about editing in Excel, not confidentiality.

Conclusion

To maintain protected workbooks you own, list their protection first, copy each sheet's protection and the workbook's security, replace them with an empty SheetProtection() and None, make the change, and assign the saved objects back. The stored password hashes — legacy or SHA-512 — are restored unchanged, so the existing passwords keep working. Encrypted files need their real password, and protection set by others is not yours to remove.

Frequently asked questions

How do I remove sheet protection with openpyxl? Replace the sheet's protection with a fresh SheetProtection() object, or set ws.protection.sheet = False, and save. For workbook structure protection, set wb.security = None. Only do this on workbooks you own or are authorised to maintain.

Can I re-protect the sheet with the original password afterwards? Yes, without knowing the password. Copy the protection object before removing it and assign the copy back after your changes. The stored password hash — legacy or SHA-512 — is written back unchanged, so the original password still works in Excel.

Does removing protection change the cells? No. Cells keep their locked and hidden flags; those only take effect while the sheet is protected. Values, formulas and formatting are untouched.

Can openpyxl remove a password to open? No. A password to open encrypts the whole file; openpyxl cannot read it at all. You need the real password to decrypt it, for example with msoffcrypto-tool, before any Python library can work with the contents.

Related

• Up one level: Protecting and Sharing Excel Workbooks — protection, comments and sharing.
• Lock Cells and Protect a Sheet with openpyxl — setting protection up in the first place.
• Protect Workbook Structure with openpyxl — the workbook-level lock.
• Fill an Excel Template with Python and openpyxl — templates that are often protected.