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

Protecting and Sharing Excel Workbooks

Control what readers can change in a generated Excel report: sheet and workbook protection, locked cells, hidden sheets, file encryption, comments and document properties.

A generated report rarely goes to somebody who should change it. They should read the totals, filter the detail, maybe type into three input cells — and not overwrite the formula column by accident. Excel has a layered set of controls for exactly this, and Python can set nearly all of them at write time: locking cells, protecting sheets and workbook structure, hiding working sheets, annotating cells with comments, and encrypting the file itself. This page maps the layers, explains which ones are genuine security and which are guard rails, and shows the code for each. It sits within Formatting and Charting Excel Reports with Python.

The four protection layers, and which of them is real security Four stacked bands. File encryption sits at the top as the only genuine confidentiality control, since the bytes cannot be read without the password. Workbook structure protection stops sheets being added, deleted or renamed. Sheet protection stops cell edits in Excel's interface. Hidden and very hidden sheets are obscurity only. The lower three are all trivially removed by any library that can open the file, so they guard against accident rather than intent. strongest at the top file encryption the bytes are unreadable without the password — genuine confidentiality real security workbook structure protection no adding, deleting, renaming or reordering sheets guard rail sheet protection + cell locking only the cells you unlock can be typed into guard rail hidden and very hidden sheets tidiness only — the values are plainly readable by any library obscurity

Be honest about what protection is for

This is the point to settle before writing any code, because the two things people mean by "protect this file" have completely different answers.

Sheet and workbook protection are interface guards. They tell Excel which operations to refuse. The password is stored in the file as a short, weak hash that was never designed to resist attack, and openpyxl will happily hand you a protected workbook with every value readable. Their real job is preventing the accident: the reader who tabs into a formula column and overwrites it, or drags a sheet to a new position and breaks a reference.

File encryption is the real control. The workbook is wrapped in an encrypted container; without the password there is nothing to read. This is what you use when the contents genuinely should not be seen.

Choosing the wrong one is a common and consequential mistake. A salary report shipped with sheet protection and no encryption is readable by anyone who opens it — the protection stops them typing, not looking.

The confusion is understandable, because Excel's own vocabulary blurs the line. The menu offers "Protect Sheet", "Protect Workbook" and "Encrypt with Password" in the same place, all under a heading about protection, and only the last of those does anything a security review would recognise. A useful test when deciding which you need: ask what would happen if the file were forwarded to somebody it was not meant for. If the answer is "they would see everything but could not edit it", you have chosen a guard rail. If the answer is "they would see nothing", you have chosen encryption. Both are legitimate — they simply solve different problems, and a report often wants one layer of each.

RequirementUse
Stop accidental edits to formulasCell locking + sheet protection
Stop sheets being reordered or deletedWorkbook structure protection
Keep a working sheet out of the wayHidden or very hidden sheet
Stop anyone reading the contentsFile encryption
Explain a figure to the readerCell comments
Record who produced the file and whenDocument properties

Locking cells and protecting a sheet

The mechanic surprises everyone the first time: every cell is locked by default, and that lock does nothing until sheet protection is switched on. So the pattern is not "lock the formulas" — it is "unlock the inputs, then protect the sheet".

Why you unlock cells rather than lock them Three states of the same sheet. Before protection, every cell carries a locked attribute but the attribute has no effect, so everything is editable. In the middle step the input range has its locked attribute cleared. After sheet protection is enabled, the still-locked cells refuse edits while the cleared input range remains editable. Enabling protection without clearing anything produces a sheet nobody can type into at all. 1 · as written every cell locked=True but protection is off so the flag does nothing everything editable 2 · unlock the inputs B2:B10 locked=False the cells a reader is meant to fill in Protection(locked=False) 3 · protect the sheet ws.protection.enable() locked cells refuse edits B2:B10 still accepts typing exactly the intended shape skip step 2 and you ship a sheet nobody can type into at all
Python
from openpyxl import Workbook
from openpyxl.styles import Protection, Font, PatternFill

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

ws["A1"] = "Region"
ws["B1"] = "Units (enter)"
ws["C1"] = "Unit price"
ws["D1"] = "Revenue"
for cell in ws[1]:
    cell.font = Font(bold=True)

for i, (region, price) in enumerate(
    [("North", 12.5), ("South", 11.0), ("West", 13.25)], start=2
):
    ws[f"A{i}"] = region
    ws[f"B{i}"] = 0
    ws[f"C{i}"] = price
    ws[f"D{i}"] = f"=B{i}*C{i}"

# Unlock ONLY the input column, and tint it so the reader can see where to type.
entry = 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

ws.protection.sheet = True
ws.protection.password = "report"      # deters, does not secure
ws.protection.enable()

wb.save("forecast.xlsx")

Tinting the unlocked range is worth the two extra lines. A protected sheet with no visual cue produces support requests from readers who cannot tell which cells will accept input.

Sheet protection is not all-or-nothing. Each permission is a separate flag, and the useful default is to keep sorting and filtering available so the sheet stays browsable:

Python
ws.protection.sheet = True
ws.protection.autoFilter = False       # False here means "allowed"
ws.protection.sort = False
ws.protection.formatCells = True       # True means "blocked"
ws.protection.insertRows = True
ws.protection.deleteRows = True
ws.protection.enable()

The inverted sense of those flags is a genuine trap: the attribute names what is protected, so False permits and True blocks. Set autoFilter and sort to False on any sheet with a table, or readers lose the filter dropdowns that make it usable — the ones added in creating Excel tables and autofilters with Python.

The complete treatment, including protecting only part of a sheet, is in locking cells and protecting a sheet with openpyxl.

Protecting the workbook structure

Sheet protection guards cells. Workbook protection guards the shape of the file — which matters when a dashboard's formulas reference sheets by name and a reader renaming one breaks everything:

Python
from openpyxl import load_workbook

wb = load_workbook("dashboard.xlsx")

wb.security.lockStructure = True       # no add, delete, rename, reorder, hide
wb.security.workbookPassword = "structure"

wb.save("dashboard_locked.xlsx")

This pairs naturally with the multi-sheet reports built in building multi-sheet Excel dashboards, where a summary sheet holds cross-sheet references that only work while the sheet names stay put.

Hidden and very hidden sheets

Generated reports often carry sheets the reader does not need: a lookup table feeding data validation, a raw extract behind a pivot, a parameters sheet. Excel has two levels of hiding, and openpyxl exposes both:

Python
from openpyxl import load_workbook

wb = load_workbook("report.xlsx")

wb["Lookups"].sheet_state = "hidden"        # right-click to unhide
wb["_raw"].sheet_state = "veryHidden"       # needs the VBA editor to reveal

wb.save("report_tidy.xlsx")

veryHidden keeps a sheet out of the unhide dialog entirely. It is a tidiness control, not a security one — the values are in the file and any library reads them straight through. Use it for the working sheets that would confuse a reader, and never for anything confidential.

One rule Excel enforces: at least one sheet must be visible. Hiding the last visible sheet produces a file Excel reports as corrupt, so a defensive helper is worth having:

Python
def hide_sheets(wb, names, state="hidden"):
    """Hide sheets, refusing to hide the last visible one."""
    targets = [n for n in names if n in wb.sheetnames]
    remaining = [
        ws.title for ws in wb.worksheets
        if ws.sheet_state == "visible" and ws.title not in targets
    ]
    if not remaining:
        raise ValueError("at least one sheet must stay visible")
    for name in targets:
        wb[name].sheet_state = state
    return targets

Hiding individual rows and columns follows the same idea at a finer grain, covered in hiding sheets, rows and columns with openpyxl.

Encrypting the file

When the contents genuinely must not be read, none of the above helps. You need the encrypted container, and that means a separate package — neither openpyxl nor xlsxwriter can write one.

Bash
pip install msoffcrypto-tool
Python
import io
import os
import msoffcrypto
import pandas as pd

def write_encrypted(df, path, password):
    """Write a DataFrame to a real password-encrypted .xlsx."""
    plain = io.BytesIO()
    with pd.ExcelWriter(plain, engine="xlsxwriter") as writer:
        df.to_excel(writer, sheet_name="Summary", index=False)
    plain.seek(0)

    office = msoffcrypto.OfficeFile(plain)
    with open(path, "wb") as out:
        office.encrypt(password, out)
    return path

df = pd.DataFrame({"employee": ["A. Chen", "B. Ortiz"], "salary": [72000, 68500]})
write_encrypted(df, "payroll.xlsx", os.environ["REPORT_PASSWORD"])

Reading one back needs the same package, and this is the piece worth wiring into an ingest pipeline that receives encrypted files from a bank or a payroll provider:

Python
import io
import msoffcrypto
import pandas as pd

def read_encrypted(path, password):
    decrypted = io.BytesIO()
    with open(path, "rb") as fh:
        office = msoffcrypto.OfficeFile(fh)
        office.load_key(password=password)
        office.decrypt(decrypted)
    decrypted.seek(0)
    return pd.read_excel(decrypted)

df = read_encrypted("payroll.xlsx", os.environ["REPORT_PASSWORD"])

Two operational points. Read the password from the environment or a secrets manager — never from source, where it is visible to everyone with repository access. And decide deliberately how the password reaches the recipient: emailing an encrypted workbook and its password in the same message provides no protection at all, which is a surprisingly common pattern. The delivery side is covered in emailing Excel reports with smtplib.

Comments, and telling readers why

Protection stops the wrong edit. A comment explains why the cell is what it is, which prevents the argument that follows. openpyxl attaches them directly:

Python
from openpyxl import load_workbook
from openpyxl.comments import Comment

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

note = Comment(
    "Unit price from the Q3 price list, effective 1 July.\n"
    "Update the Lookups sheet, not this cell.",
    "Reporting",
)
note.width, note.height = 260, 90
ws["C2"].comment = note

wb.save("forecast_annotated.xlsx")

Comments carry an author and appear on hover, which makes them the natural place for the provenance of a number — the source system, the as-at date, the assumption behind a forecast. The full walkthrough is in adding comments and notes to Excel cells with Python.

Document properties do the same job at file level, and they survive being forwarded in a way that a covering email does not:

Python
from datetime import datetime
from openpyxl import load_workbook

wb = load_workbook("forecast.xlsx")
props = wb.properties
props.title = "Regional revenue forecast"
props.creator = "Reporting automation"
props.description = "Generated nightly from the sales warehouse. Do not edit."
props.created = datetime(2026, 8, 15, 6, 0)
props.modified = datetime(2026, 8, 15, 6, 0)
wb.save("forecast.xlsx")

Marking a workbook read-only, and the alternative

Excel has a softer control than protection: the read-only recommendation. It shows a prompt when the file opens, suggesting the reader open a copy instead. openpyxl exposes it through the same security object:

Python
from openpyxl import load_workbook

wb = load_workbook("report.xlsx")
wb.security.lockStructure = True
wb.security.revisionsPassword = "audit"   # blocks turning off change tracking
wb.save("report_final.xlsx")

Be realistic about its effect: it is a dialog, and readers dismiss dialogs. Where the report genuinely must not be edited, the better answer is usually to stop sending a spreadsheet at all.

That is worth stating plainly, because it solves a whole class of problems at once. A large share of "Excel reports" are read and filed, never edited. Send those as PDF and the questions about protection, locking and versioning all disappear — nobody holds a divergent edited copy, because there is nothing to edit:

Python
from pathlib import Path
import shutil, subprocess, tempfile

def to_pdf(xlsx_path, out_dir="delivery"):
    """Render a finished workbook to PDF for distribution."""
    soffice = shutil.which("soffice") or shutil.which("libreoffice")
    if soffice is None:
        raise RuntimeError("LibreOffice not found")
    out = Path(out_dir).resolve()
    out.mkdir(parents=True, exist_ok=True)
    with tempfile.TemporaryDirectory() as profile:
        subprocess.run(
            [soffice, f"-env:UserInstallation=file://{profile}",
             "--headless", "--convert-to", "pdf",
             "--outdir", str(out), str(Path(xlsx_path).resolve())],
            capture_output=True, text=True, timeout=180, check=True,
        )
    return out / (Path(xlsx_path).stem + ".pdf")

The full conversion recipe, including page setup so wide tables paginate sensibly, is in converting an Excel file to PDF with Python. A common shape for a monthly pack is to send the PDF to everyone and the workbook only to the handful of people who need to model with it.

What survives, and what your script silently destroys

Protection settings are workbook state, and state is exactly what gets lost when a script rewrites a file rather than editing it. This is the failure people hit after everything above is working: the hardening step runs, the report ships, and the protection is gone — because a later stage wrote the sheet again through pandas.

Which write path keeps the protection you applied Two paths from a hardened workbook. The upper path calls to_excel on the same sheet name, which replaces the whole sheet and discards protection settings, cell comments, hidden state and column widths. The lower path loads the workbook with openpyxl and assigns to individual cells, leaving every other property intact. The rule that follows is to harden last, after all data has been written. hardened file protection set comments, hidden sheets df.to_excel(same sheet) the sheet is replaced wholesale load_workbook + cell writes only the cells you touch change protection lost comments and widths lost too everything preserved the safe way to update data

The rule that falls out of this is simple and worth enforcing in the structure of your script: harden last. Generate all the data, write every sheet, then apply protection, hiding and encryption as the final step before delivery. Anything that rewrites a sheet after hardening undoes it.

A quick assertion catches a regression in that ordering before the file goes anywhere:

Python
from openpyxl import load_workbook

def assert_hardened(path, expect_hidden=()):
    wb = load_workbook(path)
    for ws in wb.worksheets:
        if ws.sheet_state == "visible":
            assert ws.protection.sheet, f"{ws.title} is not protected"
    assert wb.security and wb.security.lockStructure, "structure not locked"
    for name in expect_hidden:
        assert wb[name].sheet_state != "visible", f"{name} is visible"
    return True

The same discipline applies to template-based reports, where the template already carries protection and your script only fills cells — the reason populating an Excel template without losing formatting insists on cell-level writes rather than sheet replacement.

Putting the layers together

A realistic report applies several layers in one pass, and the order matters: unlock before protecting, hide before locking the structure.

Python
import os
from openpyxl import load_workbook
from openpyxl.styles import Protection

def harden(path, dest, input_ranges=(), hide=(), structure=True):
    """Apply the standard protection layers to a generated report."""
    wb = load_workbook(path)

    # 1. Unlock the ranges readers are meant to fill in.
    for sheet_name, ref in input_ranges:
        for row in wb[sheet_name][ref]:
            for cell in row:
                cell.protection = Protection(locked=False)

    # 2. Tuck away the working sheets.
    for name in hide:
        if name in wb.sheetnames:
            wb[name].sheet_state = "hidden"

    # 3. Protect each visible sheet, keeping sort and filter usable.
    for ws in wb.worksheets:
        ws.protection.sheet = True
        ws.protection.autoFilter = False
        ws.protection.sort = False
        ws.protection.password = os.environ.get("SHEET_PASSWORD", "")
        ws.protection.enable()

    # 4. Freeze the workbook's shape.
    if structure:
        wb.security.lockStructure = True

    wb.save(dest)
    return dest

harden(
    "report.xlsx",
    "report_final.xlsx",
    input_ranges=[("Forecast", "B2:B10")],
    hide=["Lookups", "_raw"],
)

Because every step here is deterministic, it belongs in the test suite alongside the rest of the output checks — assert that the protection flag is set, that the input range is unlocked, and that the working sheets are hidden, using the approach in testing Excel output with pytest.

Key takeaways

  • Sheet and workbook protection are guard rails, not security. They prevent accidents; they do not prevent reading.
  • Unlock, then protect. Every cell is locked by default and the lock is inert until sheet protection is enabled.
  • The protection flags are inverted. False permits, True blocks — so set autoFilter = False to keep filtering usable.
  • veryHidden is tidiness. Any library reads a very hidden sheet without effort.
  • File encryption is the only real confidentiality control, and it needs msoffcrypto-tool rather than openpyxl.
  • Keep passwords out of source, and never send an encrypted file and its password in the same message.
  • Comments and document properties carry the "why" with the file, where a covering email does not.

Frequently asked questions

Does openpyxl's sheet protection actually stop anyone? No. Sheet and workbook protection are interface guards — they stop accidental edits in Excel and nothing more. The password is stored as a weak hash and any library, including openpyxl, can strip it. For real confidentiality you need file-level encryption.

What is the difference between protecting a sheet and encrypting the file? Sheet protection restricts what Excel's interface will let a reader change once the file is open; anyone can still read every value. Encryption makes the file unreadable without the password, so nothing can be opened at all until it is supplied.

Why are all my cells still locked after I protect the sheet? Every cell is locked by default. The lock only takes effect when sheet protection is enabled, so the pattern is to unlock the input cells first, then protect the sheet.

Can Python encrypt an .xlsx file? Not with openpyxl or xlsxwriter. Use the msoffcrypto-tool package to write an encrypted container, or have headless LibreOffice save the file with a password.

Is a very hidden sheet actually hidden? It is hidden from the normal unhide menu and needs the VBA editor to reveal, which is enough to stop casual browsing. It is not a security control — the data is plainly readable by any library that opens the file.

Should I put the password in my script? No. Read it from an environment variable or a secrets manager. A password committed to a repository is available to everyone with repository access, which is usually more people than the ones meant to open the report.