Locking Down Shared Input Sheets Without Breaking Them

The first time you lock a shared sheet, one of two things happens. Nobody can type anywhere, and the complaints arrive within the hour; or the lock is so loose that it protects nothing, and the damage arrives a month later. Getting the balance right takes a few decisions about what to protect, what to allow and how to avoid trapping yourself. This article walks through them for an input sheet that many people fill in: stock counts, timesheets, a quarterly submission from each department. It extends protecting formula cells from edits in shared workbooks with the sheet-level and workflow decisions, and belongs to controlling what goes into a spreadsheet.

Decide what is the input area

Draw a boundary. On an input sheet there are usually four kinds of cell:

• Headers and labels, which people must not change.

• Input cells, which they fill in.

• Calculated cells, such as totals, status flags and lookups, which they must not overwrite.

• Reference data, such as lists and rates, which one owner maintains.

Only the second kind should be editable. Mark it visually: a pale fill on input cells tells people where to type, and the contrast with the rest of the sheet shows what is off limits. Put reference data on a separate sheet, and protect that sheet more tightly than the input sheet.

If the sheet is a table that grows, the input area is the body of the table, and its extent changes. See the table section below, since it changes the protection recipe.

Protect the sheet, then the workbook

Clear the Locked property on the input cells (Format Cells, Protection) and protect the sheet (Review, Protect Sheet). Then protect the workbook structure (Review, Protect Workbook, Structure). Structure protection stops people adding, deleting, renaming, hiding, unhiding and moving sheets. Without it, protection on a sheet is easy to sidestep by copying the sheet's contents to a new one, and a renamed Lists sheet breaks every dropdown.

The two protections are separate. Protect Sheet governs cells and objects on a sheet. Protect Workbook governs the set of sheets. Do both for a template; either alone leaves a gap.

Choose the allowed actions with a real user in mind

The Protect Sheet dialog lists actions to permit. The defaults allow only selecting cells, which is too strict for anything but a form. Go through them with the work in mind.

Sort has a catch: it works on a protected sheet only when every cell in the sort range is not locked. If the sorted range includes locked formula cells, the sort is refused. Keep calculated cells outside the sorted range or place the sort on a table whose calculated columns follow (using Excel tables for structured input).

Check which of these you chose by testing as a user would: resize a column, apply a filter, sort by date, add a row.

Tables, pivots and protection

Excel tables and protection interact in ways that surprise people. On a protected sheet, a table does not auto-expand when you type beneath it, because adding a row changes locked structure. Options:

1. Pre-build the rows. Make the table 500 rows tall with empty rows, validation in place, and tell people to fill from the top. The loader skips empty rows. Simple, with a hard ceiling.

2. Allow inserting rows, and let people insert within the table. Awkward but workable.

3. Leave the input sheet unprotected and rely on validation, conditional formatting and the import check, while protecting the sheets with formulas and lists. A reasonable choice when the input area is a plain table of values and no formulas share the sheet.

Pivot tables on a protected sheet can be refreshed only if the pivot's own cells are not locked and "Use PivotTable" is allowed; otherwise the refresh fails with an error. Put pivots on their own unprotected sheet, reading from the protected data.

Macros can write to protected cells only if the code unprotects and reprotects the sheet around the write. If your workbook has macros (and macros are best avoided in data workflows), include the password handling in the code.

Passwords: choose to have one or not

A sheet protected without a password can be unprotected by anyone with one click, which stops accidents and nothing else. A password adds a barrier for the casual meddler. Whichever you choose:

• Never lose it. Keep it in a password manager or a secret store, not in an email. Keep an unprotected master copy of every template in version control or a shared drive with restricted write access. A template locked with a lost password is a template to rebuild.

• Do not reuse a valuable password. Sheet protection passwords are weak; the stored value is a hash that is easy to attack or remove. Never use a password here that you use elsewhere.

• Do not put it in the file or the instructions sheet if the point is to keep people out. If the point is to stop accidents, writing it on the README is fine and honest.

• Use different passwords for the sheets that hold reference data and those that hold inputs, so an editor of one cannot change the other.

Hide what people do not need

Hide helper columns and calculation sheets only if there is a reason, because hiding creates surprises. Instead, group and collapse them, or place them to the right of the print area with a gray header and a note. If you hide the formulas (the Hidden property on locked cells), people see values and not the logic; that may suit rates and thresholds. It does not hide them from anyone who unzips the file (hidden sheets and metadata leaking out of shared workbooks).

Never hide a sheet to protect data that others should not see. Hidden sheets are readable with an unzip tool.

Keep a master and version the template

Treat the template like code. Keep one master, unprotected, in a controlled location. Build the distributed copy from it, protected and with the version number set. Store both in version control or a document system with history. When a change is needed (a new column, a new list value), change the master, bump the version, rebuild and announce. Do not edit distributed copies, and do not accept returned files as a new master.

Generating the protected copy from code makes this repeatable. With openpyxl:

from openpyxl import load_workbook
from openpyxl.styles import Protection

wb = load_workbook("master/stock_count.xlsx")
ws = wb["Count"]
for row in ws["A2:F501"]:
    for c in row:
        c.protection = Protection(locked=False)      # input area
ws.protection.sheet = True
ws.protection.formatColumns = False                  # False = allowed in openpyxl
ws.protection.autoFilter = False
ws.protection.sort = False
wb.security.lockStructure = True                     # workbook structure
wb.properties.title = "Stock count 2026.3"
wb.save("dist/stock_count_2026.3.xlsx")

Note that openpyxl's SheetProtection flags are True when an action is blocked, so False allows it; confirm against the library version's documentation and by opening the output. For the broader template design see building Excel templates that import cleanly.

Test with a real person

Give the protected file to someone who was not involved in building it and ask them to do their normal task. Watch for the places they get stuck: a locked cell they need, an action that is blocked, a message they do not understand. Fix the template, not the person. Then try to break it yourself: type into locked cells, paste over formulas, delete a column, rename a sheet, insert a sheet. Everything you can do by accident, someone will.

Include these cases in your checklist before release, and add a short loader test that opens the distributed file and asserts the sheet is protected, the structure is locked and the input cells are not locked.

What to say in the instructions

Tell users what is protected and why, in two sentences: "Yellow cells are for your entries. Everything else is locked so the totals and lists stay correct. If you need a change, contact [email protected]." Give a route for requests, because a locked sheet with no escape hatch trains people to export to a new file and work there. If you expect people to need an occasional override, document the unprotect step and make it clear who may use it.

When locking down is the wrong approach

Locking is the wrong approach when the audience is a few trusted analysts who need freedom and who would route around restrictions: use a good template and an import check. It is wrong when you need to know who changed what; use a system with a history. It is wrong when the sheet is the product, a model that people are supposed to modify. And it is wrong as the only control: protection does not validate values, so combine it with data validation and a returned-file check (data validation lists and their limits in Excel sheets and checking an incoming workbook against a template by code). Microsoft's protect a workbook guide covers structure protection in the interface, and the openpyxl protection API lists every flag.