You have prepared a template with careful formulas, you send it to ten colleagues to fill in, and you get back ten files with overwritten formulas, deleted columns and totals typed by hand. Sheet protection exists precisely to prevent this. It is, however, an often misunderstood tool: some protections prevent accidental mistakes, only one truly protects the confidentiality of the data. It is worth knowing which does what.
Locked cells and sheet protection
In Excel and LibreOffice Calc every cell has a "Locked" attribute, found in Format Cells, on the Protection tab. The detail that surprises many people is that all cells are locked by default, but the attribute has no effect until you switch on sheet protection.
The correct procedure therefore has two steps:
- select the cells the user must be able to fill in and untick "Locked";
- switch on sheet protection, in Excel from Review, Protect Sheet; in Calc from Tools, Protect Sheet.
From then on, locked cells cannot be edited, unlocked ones can.
Protection options
When you protect a sheet you can choose what remains allowed. The most useful options are:
| Option | When to enable it |
|---|---|
| Select unlocked cells | always |
| Select locked cells | if the user needs to copy the results |
| Format cells | rarely, it risks altering the template |
| Insert rows | if the list must be able to grow |
| Sort and use AutoFilter | if the user needs to browse the data |
Inserting rows deserves attention: if you allow it, new rows inherit the protection of the row they start from, and the formulas need checking.
Hiding formulas
On the same Protection tab there is the "Hidden" option. A hidden cell on a protected sheet shows the result but not the formula in the formula bar. It is useful to prevent edits or confusion, not to protect a secret: as we will see, sheet protection is easy to get around.
Workbook protection
In Excel, Review, Protect Workbook prevents changes to the structure: adding, deleting, renaming, moving or unhiding sheets. It complements sheet protection and is useful in a template where the calculation and parameter sheets must stay where they are.
What these protections do not do
This is the most important point. Sheet and workbook protection, even with a password:
- does not encrypt the file's content;
- does not prevent reading the data, which stays visible and can be copied;
- can be removed with readily available tools, because the mechanism is designed to prevent accidental edits, not to resist someone determined to bypass it;
- does not prevent copying the whole sheet into a new file.
Hiding a sheet with confidential data, even with the structure protected, is therefore not a safe way to keep it secret. If a colleague must not see certain data, that data must not be in the file they receive.
The opening password: this one encrypts
Excel and LibreOffice let you set a password to open the file. In Excel it is under File, Info, Protect Workbook, Encrypt with Password; in Calc in the Save As dialog, by ticking Save with password. In this case the file is encrypted: without the password the content cannot be read.
Some recommendations:
- use a long, unique password, ideally generated and stored in a password manager;
- do not send the password through the same channel as the file: if the file arrives by email, the password can arrive by phone or in a separate message;
- if you lose the password, the file is effectively lost. Keep a copy somewhere safe.
There is also a "modify" password, which lets people open the file read-only without a password: like sheet protection, it prevents edits but does not protect confidentiality.
Google Sheets
In Google Sheets the model is different, because the file lives on the server and access depends on the account:
- sharing determines who can view, comment on or edit the file: this is the real protection of confidentiality;
- from Data, Protect sheets and ranges you can limit editing of specific sheets or ranges to certain people, or just show a warning when someone tries to edit them;
- version history lets you see who changed what and restore an earlier version.
The rule applies here too: anyone with read access to the file sees every sheet, including hidden ones, which can be shown again. Protected ranges prevent edits, not reading.
A well-protected template
In my work, when I prepare a template to be filled in, I follow this pattern:
- I separate input sheets from calculation and results sheets;
- I unlock only the input cells and highlight them with a colour;
- I add data validation to the input cells;
- I protect every sheet and the workbook structure;
- if the file contains personal or confidential data, I add an opening password and communicate it separately.
In short
- Cells are locked by default, but locking only takes effect once the sheet is protected.
- Unlock the input cells, then protect the sheet and the workbook structure.
- Sheet and workbook protection prevent mistakes, they do not guarantee confidentiality.
- Only the opening password encrypts the file; share it through a separate channel.
- In Google Sheets confidentiality depends on sharing; protected ranges only limit edits.
