Protecting a Generated Workbook From Accidental Edits

A script generates a workbook with formulas, a summary sheet and a few cells that people are meant to fill in. By Thursday somebody has typed over a formula, sorted a block of rows without its neighbors and deleted the sheet the chart read from. Nobody did anything malicious. The workbook offered every cell for editing, and people edit. Protection is the feature that limits where they can type. It is a guard rail and not a lock, and this post shows how to set it up from code, what to leave open and what it does not do. It is a spoke of automating Excel work without clicking.

How protection works in Excel

Three separate things get called protection.

1. Cell locking. Every cell has a Locked property, which is on by default. It does nothing until the sheet is protected.

2. Sheet protection. Turning it on makes locked cells read only. Cells marked as not locked stay editable. It can have a password.

3. Workbook structure protection. Prevents adding, deleting, renaming, moving or hiding sheets. Separate from sheet protection, and with its own password.

A fourth, file encryption with a password to open, is different in kind: it encrypts the contents. This post covers the first three, which are about preventing accidents.

The consequence for generated files: locking is already the default, so the work is to turn on sheet protection and clear the lock on the cells where input is expected.

Lock a sheet with openpyxl

from openpyxl import Workbook
from openpyxl.styles import Protection

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

ws.append(["Item", "Planned", "Actual", "Variance"])
for r in range(2, 7):
    ws.cell(r, 1, f"Item {r - 1}")
    ws.cell(r, 2, 1000)
    ws.cell(r, 4, f"=C{r}-B{r}")                       # formula: stays locked

# Inputs: clear the lock on the Actual column so people can type there
for r in range(2, 7):
    ws.cell(r, 3).protection = Protection(locked=False)

ws.protection.sheet = True                              # turn protection on
ws.protection.formatColumns = False                     # False means allowed
ws.protection.autoFilter = False
ws.protection.sort = False
wb.save("budget.xlsx")

The attribute values are inverted from what you might expect: in ws.protection, True means the action is prohibited on a protected sheet, and False means allowed. By default most actions are prohibited, so you set False for the ones to allow. Here column widths, filtering and sorting stay available.

ws.protection.set_password("secret") sets a password. Omitting it protects the sheet without one: anyone can click Unprotect Sheet, but accidents are blocked. For accident prevention, no password is often the right choice, because a forgotten password on a generated file means regenerating it.

Lock a sheet with XlsxWriter

import xlsxwriter

wb = xlsxwriter.Workbook("budget.xlsx")
ws = wb.add_worksheet("Budget")
unlocked = wb.add_format({"locked": False, "bg_color": "#FFF2CC"})

ws.write_row(0, 0, ["Item", "Planned", "Actual", "Variance"])
for r in range(1, 6):
    ws.write(r, 0, f"Item {r}")
    ws.write(r, 1, 1000)
    ws.write(r, 2, None, unlocked)                   # input cell
    ws.write_formula(r, 3, f"=C{r + 1}-B{r + 1}")

ws.protect("", {"autofilter": True, "sort": True, "format_columns": True})
wb.close()

In XlsxWriter, the options dictionary uses True for actions to allow, which is the reverse of openpyxl. An empty string as the password means no password. Highlighting the editable cells with a fill color tells people where to type, which prevents more mistakes than the protection itself. Both libraries follow the file format: the format stores flags for what is prohibited, and each library exposes them differently. Check the result in Excel, since a misread flag looks the same in code.

Which actions to allow

A fully locked sheet is frustrating. People need to read, filter and often sort. The protection dialog has a list; decide for each.

Sort is the one that surprises people: Excel refuses to sort a range that includes a locked cell, even with sorting allowed. A sortable table has to be free of locks, which means the table is editable. Offer the data on a separate sheet with no locks if sorting matters, and keep the formula sheet locked.

Protect the structure

Sheet protection leaves the sheets themselves open to deletion and renaming. A formula that refers to Data!A:D breaks when somebody renames Data. Protect the workbook structure so the set of sheets is fixed:

wb.security.lockStructure = True             # openpyxl

In XlsxWriter use workbook.set_vba_name for macros and, for structure, there is no direct method in older versions; check your version's documentation, or apply structure protection through openpyxl after writing. With structure locked, the Insert, Delete, Rename, Move and Hide commands are disabled. As with sheets, a password is optional.

What protection is not

It is not security. The sheet password in the file is stored as a hash produced by a weak algorithm, designed for convenience. Free tools and short scripts remove it in seconds, and a worksheet's protection can be defeated by editing the XML in the zip: delete the sheetProtection element and re-zip. Protection stops a person who clicks. It does not stop one who wants in.

It does not hide anything. Locked and even hidden cells are in the file and readable by any library, and a formula you protect can be read by anyone who can open the file. Do not rely on it to keep a number, a rule or a customer list private (hidden sheets and metadata leaking out of shared workbooks belongs in the same cluster as a later post).

It does not stop copying. Protected cells can be selected and copied into another workbook where they are fully editable. If someone must not copy the values, they must not receive them.

For confidentiality, the control is encryption on opening, which is real encryption with a strong password in current versions, with the caveat that a script holding the password to open it needs somewhere to keep it (password protected Excel files and scripts is a later topic). For integrity, the control is to keep the master in a system the reader cannot edit, and hand out copies.

Splitting a master sheet into one protected workbook per recipient is covered in splitting one Excel sheet into a workbook per group, and the layout details of a report sheet are in freezing panes, filters and column widths.

Alternatives that work better

• Read-only copies. Deliver a PDF for reading, or a workbook of values (no formulas) for people who only need numbers. There is nothing to break.

• File permissions. On a shared drive or SharePoint, give most people read access. The file cannot be saved over, though a copy can be edited.

• Regenerate on demand. If the workbook is cheap to rebuild, treat any edit as disposable: the next run overwrites it. State this on the sheet.

• Data validation. For input cells, restrict what can be typed: a list, a date range, a whole number between limits. It prevents bad inputs even in unprotected cells.

• A form. If people only submit a few values, a web form is safer than a spreadsheet.

Data validation pairs well with protection: clear the lock on the input cells and validate them. In openpyxl, DataValidation(type="decimal", operator="between", formula1=0, formula2=100000) added to the sheet enforces a range.

Test the protection

Open the generated file in Excel and try to break it: type in a locked cell, delete a row, rename a sheet, sort the table, filter, paste a value into an input cell. Each should do what you intended. Check in a second version if users run an older one. Add a small automated test that opens the file with openpyxl and asserts that ws.protection.sheet is True, that the input cells have locked == False and that formula cells have locked == True (testing that a generated Excel report is correct).

Put a line on the sheet saying what is editable and how to unprotect it, if you left the password off. A short note, such as "Yellow cells are inputs; the rest is calculated", answers most questions before they are asked.

When protection is the wrong control

The data is confidential. Use encryption or, better, do not send the data. Protection is a convenience.

People genuinely need to change the structure. A template they extend, with new rows and columns, fights with protection. Offer a different design.

It is a one-off. Locking a file you send once and never see again adds friction and no benefit.

The recipients are technical. They will unprotect and edit. Give them the code or the data instead.

Microsoft documents the features on its pages about protecting a worksheet and the openpyxl documentation covers worksheet protection. To convert a small result to JSON, use the CSV to JSON converter.