In this tutorial, you will learn how to lock a formula in Google Sheets.
Google Sheets is great for collaborating, but sometimes it can get a little too collaborative. If you’ve ever taken the time to painstakingly write the perfect formula, only to have it unwittingly overwritten by a colleague, you’ll understand the importance of locking formulas to protect them from accidental edits.
Fortunately, Google Sheets provides a couple different levels of protection that can be applied to ranges. Read on to learn how to use these features to protect your formulas.
Warning Users Before Edit
If you’re looking to prevent accidental edits and warn users if they’re editing something important, but trust users to decide for themselves if they want to make an edit, the Warnings option is the right one to use.
Here’s how:
Step 1
Select the cell or range containing the formula(s) you want to lock, then open the Data menu and choose Protect Sheets and Ranges
Step 2
In the Protected Sheets & Ranges sidebar, click on Add a Sheet or Range
Step 3
You can add a description if you want, then click on Set Permissions
Step 4
Select Show Warning When Editing This Range and click Done
Step 5
Any user attempting to edit the range will now get a warning message and be given the opportunity to cancel the edit by clicking the cancel button
Completely Prevent Edits by Certain Users
If you’re not certain the above option is strong enough to avoid accidental edits, or think some users might want to make edits you don’t agree with, you can use these steps to lock a formula more effectively:
Step 1
Follow the above steps until you get to the Range Editing Permissions dialog, and there choose Restrict Who Can Edit This Range, instead of the warnings option
Step 2
In the dropdown beneath, you can select who is allowed to edit the range. Select Only You to prevent anyone else from making edits, or choose Custom to allow specific people to make edits. Note that if you are not the spreadsheet owner, the Only You option will be Only You and Spreadsheet Owner instead
Step 3
If you choose the Custom option, you’ll see a list of spreadsheet editors with check marks beside them to toggle their ability to edit the range. To add additional editors, type their email addresses in the Add Editors field
Step 4
Click Done when you’re finished setting the permissions and the range will now be locked to all edits made by anyone other than the specified users
Step 5
Note that if a user makes a copy of a spreadsheet with locked formulas, they will be the owner of the copy and as such will be added as an editor to all ranges
Summary
Example Spreadsheet: Make a copy of the example spreadsheet
In this tutorial, I covered how to lock a formula in Google Sheets. Want more? Check out all the Google Sheets Tutorials.
