How To Lock A Formula In Google Sheets

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.

Get more Google Sheets stuff

Subscribe to our mailing list and get interesting Google Sheets stuff and updates to your email inbox.

Thank you for subscribing.

Something went wrong.