Protect formulas in Google Sheets, keep the rest editable
The usual problem: a shared sheet where people should fill in numbers, but keep typing over the total, the lookup or the percentage column.
The built-in way
- Data > Protect sheets and ranges > Sheet.
- Tick Except certain cells and add every input range people should still edit.
- Set permissions to Only you.
This inverts the problem: you list the editable cells instead of the formulas. It works well for a fixed form. It breaks down when inputs and formulas are mixed in the same columns or the layout changes weekly, because every new input row needs adding to the exception list.
Find every formula first
Press Ctrl + ` (backtick) to show formulas instead of results, so you can see which cells hold formulas before you protect anything. Press it again to switch back.
The one-click way
FormulaLock has Lock every formula on this tab: it finds each formula cell, colours it, and restores it if a collaborator changes it, while every non-formula cell stays editable. New formulas? Run it again.