How to lock formulas in Google Sheets for other users: a critical step

On this page
- Protect a range so others can edit around it
- Range or whole sheet: which protection fits
- Test the lock as the person you are guarding against
- Two other meanings of lock: the dollar sign and INDIRECT
- Catch formulas that someone overwrote anyway
- Give people a drop-down instead of a blank cell
- What protection does not do
- Locking cells in Excel
- Action steps
- Frequently asked questions
- Can other people still see my formulas after I protect them?
- Can I lock only some cells and leave the rest editable?
- Why can someone still edit a cell I protected?
- Does the dollar sign protect a formula from being edited?
- Does locking work the same way in Excel?
Learning how to lock formulas in Google Sheets for other users can save hours of repair work. The idea is simple: protect the cells that hold formulas and leave the cells people need to type in open. Most guides stop at the menu clicks, but a lock can still fail in ways you will not notice. This guide covers the setup, the testing, and a way to spot a formula someone overwrote anyway.
Protect a range so others can edit around it
Start by deciding which cells hold formulas. Select them, then open Data and choose Protect sheets and ranges. Older forum threads mention Tools and Protect Sheet instead, because Google has moved the menu over time. You can also right-click the selection, choose View more cell actions, and click Protect range. All of these routes open the same side panel, where you click Set permissions.
In the permissions box, pick Restrict who can edit this range, then choose Only you or Custom. Custom lets you name specific editors, such as the colleague who maintains the model. Everyone else can still read the cells, but they cannot change the formulas. Cells you leave unprotected stay open for typing.
Google also offers Show a warning when editing this range. That option does not lock anything, since people see a prompt and can click through it. Use it for gentle reminders, never for cells that must not change.

Range or whole sheet: which protection fits
Both methods work, but they suit different layouts. The table sums up the trade-offs.
| Method | Best when | Watch out for |
|---|---|---|
| Protect a range | Formulas sit in a few columns or blocks | A formula column you add later needs its own protection |
| Protect a sheet, with exceptions | Only a small input area should stay open | Any new input cell stays locked until you add it to the exceptions |
| Show a warning only | You trust the team and want a reminder | Anyone can dismiss the prompt and edit |
The sheet option suits input forms, where a few cells accept data and everything else is calculated. After you choose Protect a sheet in the panel, tick Except certain cells and list the input cells. When inputs and formulas sit side by side across the sheet, range protection is easier to maintain. Either way, Sheets does not cover a formula column you add later, so protect it the moment it exists.
Test the lock as the person you are guarding against
A lock you never tested is a guess. To lock your formulas in Google Sheets for other users with confidence, open the file with a second Google account that has edit access. Try to type over a formula, and Sheets should refuse the edit and show an error. Then paste a block of values across the protected range. If an edit goes through, check whether that account appears in the range's editor list.
Two other meanings of lock: the dollar sign and INDIRECT
People searching for this topic often mean one of two things. Protection stops others from changing a formula. Anchoring with a dollar sign stops a reference from sliding when you copy the formula down or across. They solve different problems, so many sheets need both.
=B2*$F$1
When you copy this formula to row 3, B2 becomes B3, but $F$1 stays put. The dollar signs lock both the column and the row. Keep a rate or target in one anchored cell, as in our guide to storing a tax rate for formulas in Google Sheets, and your formulas stay short. Our post on using the same value in multiple formulas shows the pattern in more detail. In Excel, the Corporate Finance Institute's list of Excel shortcuts includes shortcuts for locking references in formulas.
A third kind of lock is rarer. Exceljet's INDIRECT function guide explains that INDIRECT builds a reference from text, so the reference does not change when rows or columns are deleted. That helps when someone deletes a row and your formula range would otherwise shrink. The catch is that Exceljet calls INDIRECT volatile, which can slow large or complex worksheets, so use it sparingly.
=SUM(INDIRECT("A1:A100"))
Catch formulas that someone overwrote anyway
Knowing how to lock formulas in Google Sheets for other users is half the job. The other half is noticing when a lock fails, for example when you forgot a range or when a trusted editor pasted over a column. A highlight rule gives you a second line of defence by flagging any cell that no longer holds a formula.
- Select the formula column, for example C2:C100.
- Choose Format, then Conditional formatting.
- Under Format cells if, pick Custom formula is.
- Enter the formula below and choose a fill color.
=NOT(ISFORMULA(C2))
Empty cells count as not containing a formula, so match the range to the rows you actually use. The rule starts at C2 for a reason. Ben Collins' tutorial on conditional formatting across a row stresses that the first reference in a custom formula must match the first row of the selected range. He also locks the column with a dollar sign when a rule should look at one column across every row, which is worth copying if your layout runs sideways.
Give people a drop-down instead of a blank cell
Input cells need guarding too, because a typo in an input breaks your formulas as surely as an overwrite does. TechRepublic's walkthrough of drop-down lists in Google Sheets shows how Data validation offers a list of choices in a cell. Its On invalid data setting defaults to Show warning, but you can switch it to Reject input. Choose Reject input when only listed values make sense.
TechRepublic also recommends adding help text under Appearance, because without it Sheets shows a generic problem message. Point the drop-down at a range elsewhere in the sheet so you update the options in one place. If a bad input still produces an error, our guide to common Google Sheets errors helps you trace it.
What protection does not do
Protection stops edits, but it does not hide formulas. Anyone who can open the sheet can click a cell and read the formula, so keep private logic and sensitive figures out of shared files. People with view access can often make their own copy, depending on your sharing settings, so check the Share dialog before you rely on the lock. The file owner can also change protections at any time. That makes how to lock formulas in Google Sheets for other users a question of guard rails, not security, and a Workspace admin can tell you what stronger controls exist.
Locking cells in Excel
Excel works differently. Every cell starts out locked, but the lock does nothing until you protect the worksheet. So you first unlock the input cells through Format Cells and the Protection tab, then switch on Review and Protect Sheet. Excel also offers an optional password, so check Microsoft's documentation for what that password does and does not protect.
Action steps
- Mark your formula cells: Select every cell or column that holds a formula, and leave input cells out of the selection.
- Protect the range: Open Data, choose Protect sheets and ranges, and add the selection as a protected range.
- Set permissions: Click Set permissions, choose Restrict who can edit this range, and pick Only you or Custom.
- Test with a second account: Sign in as an editor, try to type over and paste over a formula, and confirm Sheets blocks both.
- Add a highlight rule: Use conditional formatting with =NOT(ISFORMULA(C2)) so any overwritten formula stands out.
- Guard the inputs: Add data validation drop-downs on input cells and set invalid data to Reject input.
Frequently asked questions
Can other people still see my formulas after I protect them?
Yes. Protection blocks edits but does not hide anything. Anyone who can open the sheet can click a cell and read its formula, so keep sensitive logic out of shared files.
Can I lock only some cells and leave the rest editable?
Yes. Protect just the range that holds formulas, or protect the whole sheet and tick Except certain cells to leave your input cells open.
Why can someone still edit a cell I protected?
Check whether their account is on the range's editor list, and whether you chose Show a warning, which only displays a prompt. Testing with a second account shows which case applies.
Does the dollar sign protect a formula from being edited?
No. A dollar sign only keeps a reference fixed when you copy the formula. To stop people from changing the formula itself, you need range or sheet protection.
Does locking work the same way in Excel?
Not quite. Excel cells are locked by default, but the lock only takes effect after you protect the worksheet. Unlock your input cells first, then use Review and Protect Sheet.