TL;DR:
A formula breaks the moment someone types over it. Locking only the formula cells stops that while everyone can still enter data.
Decide first which cells are inputs and which are formulas, then lock the formulas, then choose who can edit what.
Write the rules on the sheet itself, so the next person knows why a cell refuses to change.
Picture a quote sheet shared by five people. Someone clicks the Total cell, types 500 to "fix" it, and from then on that row no longer follows the quantity and the price. Nobody notices for a week. The formula was fine. The sheet simply let a person overwrite it.
When protection is worth the trouble
Protect a sheet when other people will type into it, when the output feeds a decision or an invoice, or when you will not remember in three months which cells were formulas. A personal scratch sheet needs none of this.
Protection is a guard rail, not security. It stops accidents. It is not a safe place for secrets, and in many apps a determined person with edit rights to the whole file can remove it.
Sort the sheet into inputs and formulas first
Before touching any menu, walk through the sheet and mark each cell as one of two kinds. Inputs are cells a person is supposed to type in, like quantities, dates or names. Formulas are cells that calculate from other cells, like totals, taxes and lookups. Headings and labels belong with the formulas, since nobody should rewrite them by accident.
A quick way to find the formulas is to select a cell and read the formula bar, or use your app's option to show formulas instead of values. If you have a lot of cells, work column by column.
Lock the formulas and open the inputs
The two big apps think about this differently, so check your own app's help page for the exact menu names in your version.
Excel style: every cell has a Locked setting, and it is on by default. Locking does nothing until you also protect the sheet. So the usual order is: unlock the input cells, then turn on sheet protection.
Google Sheets style: nothing is locked until you protect a range or a sheet. You choose the cells you want guarded, and you can protect everything except a few cells you list as exceptions.
Mark the input cells: Select the cells people should type in. In an Excel style app, open the cell format options and clear the Locked setting for them. In a Google Sheets style app, remember them: you will exclude them in a moment.
Turn on protection: In an Excel style app, protect the sheet and add a password only if you are sure you will not lose it. In a Google Sheets style app, protect the whole sheet or the formula range, and list the input cells as exceptions.
Choose who can edit: Where the app offers it, set the range to editable by only you, or by a short named list. Some apps also offer a gentler mode that shows a warning when someone edits the range instead of blocking them. Use the warning mode when the sheet is shared with people you trust but who sometimes click wrong.
Test as another person: Open the sheet in a private window or ask a colleague to try. Type into an input cell, then try to overwrite a formula. The first should work and the second should refuse or warn.
Worked example: locking formula cells in a shared sheet
Say the quote sheet has three columns and a total row. Rows 2 to 4 hold items.
Cell | What it holds | Kind |
|---|---|---|
A2:A4 | Item name | Input |
B2:B4 | Quantity | Input |
C2:C4 | Unit price | Input |
D2:D4 |
| Formula |
D5 |
| Formula |
Check the numbers by hand before you protect anything. If the three rows are 2 × 10, 1 × 25 and 4 × 5, the line totals are 20, 25 and 20, and D5 must show 65. Because 20 + 25 + 20 = 65, the sheet is correct. Now you know what "working" looks like, and you can tell later whether someone broke it.
Now apply the steps. Unlock or exclude A2:C4. Protect D2:D5 and the header row. Set the editors to yourself, and use a warning for everyone else if your app offers it. In the test, change B2 from 2 to 3: D2 becomes 30 and D5 becomes 75. Then try typing 500 into D5. It should be refused or flagged, and D5 should stay 75.
Write down the rules on the sheet
Add a small note, either in a spare cell outside the print area or on a separate tab named Read me. Say which cells are inputs, who owns the formulas, who to ask to change them, and the date you last changed them. If you used a password, do not write it in the sheet. Store it where your team already keeps shared secrets.
This documentation matters as much as the lock. A locked cell with no explanation makes people angry, and angry people copy the sheet and work from the copy, which defeats the point.
Mistakes that make protection backfire
Protecting the whole sheet and forgetting to open the input cells, so nobody can enter anything.
Locking formulas but leaving the labels and headers open, so a renamed column breaks a lookup.
Using a password and then losing it. Keep an unprotected backup copy somewhere safe.
Never testing as a second person. As the owner you can usually edit everything, so you will not see the problem.
Treating the lock as security for private data. Share data only with people who should see it.
Doing it faster
If you are unsure which cells are formulas, DuckyHelper can look at your screen while the pill is open and ask out loud, and it can point at the cells on the sheet. You still make the changes yourself, and you should still run the test above.
Frequently asked questions
Does locking a cell stop it from calculating?
No. A locked cell still recalculates when its inputs change. Locking only stops people from typing over it.
Can I still edit protected cells myself?
Usually yes if you are the owner or listed as an editor, but in some apps you must first switch protection off. Check your app's help page.
Get started
