Protecting Formula Cells From Edits in Shared Workbooks

Someone types over a formula. It happens in every workbook that more than one person touches. The total at the bottom becomes a number someone remembered, the commission column stops following the price and nobody notices until quarter end. Excel has a mechanism for stopping this, cell locking with sheet protection, and it is widely misunderstood: people think locking a cell protects it, when locking does nothing until the sheet is protected, and think a protected sheet is secure, when it is not. This article shows how to set it up properly, what the options do, how to apply it from code and where the guarantees end. It supports controlling what goes into a spreadsheet and pairs with locking down shared input sheets without breaking them.

Locked is a property, protection is a switch

Every cell has a Locked property, on by default. It is in Format Cells, Protection tab. The property has no effect on its own. Only when the sheet is protected (Review, Protect Sheet) does Excel refuse edits to cells marked Locked. So the order is:

1. Decide which cells people may edit (inputs).

2. Clear the Locked box on those cells.

3. Protect the sheet.

If you protect a sheet without clearing Locked on the inputs, nobody can type anywhere, which is the most common first-time failure. If you clear Locked on the inputs and forget to protect, nothing is protected, which is the second.

Set it up by hand

Select the input cells, press Ctrl+1, open the Protection tab and clear Locked. Then Review, Protect Sheet. The dialog offers a password (optional) and a list of actions to allow for all users:

• Select locked cells

• Select cells that are not locked

• Format cells, columns, rows

• Insert columns, rows, hyperlinks

• Delete columns, rows

• Sort

• Use AutoFilter

• Use PivotTable and PivotChart

• Edit objects, edit scenarios

Allow what people need to work. For an input sheet the typical set is: select locked and editable cells, format columns (widths), sort and use AutoFilter if the sheet is a table people review. Leaving "Select locked cells" on lets people click a formula and copy its result. Turning it off forces them to the editable cells, which is a clean experience for a form.

If you will protect many sheets, protect the workbook structure too (Review, Protect Workbook), which stops adding, deleting, renaming and moving sheets.

Hidden formulas

On the same Protection tab is a Hidden property. A hidden cell on a protected sheet shows its value but not its formula in the formula bar. It is useful when the formula is not for the reader (a rate lookup, an internal check) and you do not want it fiddled with or copied.

Hidden does not encrypt anything. The formula is stored in the file, and a person with the file can read it by unzipping and opening the sheet XML, or by removing the protection (hidden sheets and metadata leaking out of shared workbooks covers what a file holds). If a formula contains a secret, such as a key or a rate that must not be known, it does not belong in a workbook that others receive.

Set it up from code

A generated workbook should arrive protected. With openpyxl:

from openpyxl import Workbook
from openpyxl.styles import Protection

wb = Workbook()
ws = wb.active
ws["A1"], ws["B1"], ws["C1"] = "Qty", "Price", "Total"
for r in range(2, 12):
    ws[f"A{r}"].protection = Protection(locked=False)    # input
    ws[f"B{r}"].protection = Protection(locked=False)    # input
    ws[f"C{r}"] = f"=A{r}*B{r}"                          # stays locked
ws.protection.sheet = True
ws.protection.sort = False             # False means allowed in openpyxl's inverted flags
ws.protection.autoFilter = False
ws.protection.formatColumns = False
ws.protection.password = "review-only"
wb.save("quote.xlsx")

Note the inverted naming: in openpyxl's SheetProtection, the attributes such as sort and autoFilter are True when the action is blocked, so setting them to False allows the action. Check the semantics against the documentation for your version, and test by opening the file.

With XlsxWriter:

import xlsxwriter

wb = xlsxwriter.Workbook("quote.xlsx")
ws = wb.add_worksheet()
unlocked = wb.add_format({"locked": False})
ws.write_row("A1", ["Qty", "Price", "Total"])
for r in range(1, 11):
    ws.write(r, 0, "", unlocked)
    ws.write(r, 1, "", unlocked)
    ws.write_formula(r, 2, f"=A{r+1}*B{r+1}")
ws.protect("review-only", {"sort": True, "autofilter": True, "format_columns": True})
wb.close()

Here the options dictionary lists the actions to allow, the opposite of openpyxl. The mismatch is a classic way to ship the opposite of what you meant. Whichever library you use, test the output.

A password or no password

A sheet-protection password prevents someone from clicking Unprotect without the password. A sheet protected without one can be unprotected by anyone who clicks the button. Which is right depends on the purpose.

For preventing accidents, no password is often enough, and it avoids the problem of a lost password on a template. A person who has to click Unprotect to change a formula has thought about it. For preventing casual meddling by colleagues, a password in the instructions ("the password is on the README") adds a speed bump.

Whatever you choose, know what the password is. It is stored in the sheet XML as a hash of 16 bits, or, with newer Excel settings, as a salted SHA-512 hash with many iterations. The older form is trivially removed, and even the newer form is only checked by the application's interface; the protection element itself can be deleted from the XML. The password does not encrypt the cell contents. Protection is a rule for the user interface, and it ends where the interface ends (password protected Excel files and scripts that read them explains the difference between protecting a sheet and encrypting a file).

What protection stops and does not stop

It stops: typing over a locked cell, deleting a locked cell's contents, changing its format (if not allowed), inserting or deleting rows and columns (if not allowed) and moving objects.

It does not stop: reading any cell, copying a range to another workbook, removing protection by anyone with the password or the skill, editing the file with a script or another application, and replacing the whole file. It also does not stop a macro with access, and it does not protect against paste operations into editable cells that carry bad values, which is the job of validation and an import check.

A related gotcha: if a protected sheet contains a table, rows cannot be added unless you allow inserting rows, and even then Excel's table behavior on protected sheets is limited. Tables on protected sheets do not auto-expand when typing beneath them. Test the behavior before you promise users that they can add rows.

Common problems

Users complain they cannot do anything. The inputs still have Locked on, or "Select cells that are not locked" was not allowed.

A macro or script fails with a protection error. It writes to a locked cell. Unprotect, write and reprotect inside the code with the password, or write the workbook before protecting it.

Pivot tables will not refresh on a protected sheet. The pivot's target range is locked. Allow "Use PivotTable" and place the pivot on an unprotected sheet.

The password is lost. There is no recovery in Excel itself. Keep passwords in a secret store, and keep an unprotected master copy of every template.

Comments, hyperlinks and charts are locked too. Objects have their own Locked property; editing them is controlled by "Edit objects".

Copying protected formulas to a new workbook. The values travel, and the protection does not. That is by design.

Test it like a user

After building a protected sheet, try to break it. Type into every locked cell and confirm Excel refuses. Type into every editable cell and confirm it accepts. Try sort, filter, column resize, row insert and delete according to the permissions you chose. Paste a value over a formula cell. Open the file in the oldest Excel version your readers have. Try to unprotect without the password.

Automate a version of this: load the file with openpyxl and assert that the sheet is protected (ws.protection.sheet), that formula cells are locked (ws["C2"].protection.locked) and that input cells are not. These checks catch a refactor that drops the protection (testing a generated Excel report).

When cell protection is not real protection

If a person with bad intentions can reach the file, cell protection will not stop them. If the numbers must not be altered, the right control is outside the workbook: a read-only copy published to a location that only the report job can write to, a PDF, or a system of record with access control and an audit log. If different people may edit different cells and you need to know who did, you need a shared workbook with version history in a managed service or a database with permissions (audit trails and history Excel cannot give you). Use sheet protection for what it does well, which is stopping an honest colleague from overwriting a formula by mistake. Microsoft's protect a worksheet guide walks through the dialogs, and the XlsxWriter protection page lists the options.