Even though collaboration is an integral feature of Google Sheets, sharing your worksheet with someone can result in accidental or intentional changes to formulas. Unfortunately, this can result in errors, and incorrect data, which might cause a delay in a project you’re working on.
Thankfully, Google Sheets has an option for locking cells.
Today’s tutorial will show you how to lock cells in Google Sheets to prevent you or anyone you’re sharing your worksheet with from changing the formulas you have used hours to write.
Without any further ado, here is how you can lock certain cells in Google Sheets:
Warn users before they make any changes to your worksheet
There are times when you might want to prevent accidental edits to your worksheet by adding a warning message when users attempt to make any changes to the worksheet.
Unfortunately, not many people know how to activate this feature in their worksheets.
Good for you; we will show you how to add a warning message to your Google Sheets projects.
For this tutorial, we will use the following sample data.
Here is how to add a warning sign to your worksheet to prevent anyone from changing the formulas you have in your spreadsheet:
Step 1: Select the cell or range containing the formula
Before you can lock a cell in Google Sheets to prevent any unauthorized changes to the formulas in the worksheet, you need to select the cell or range containing formulas.
So for our sample data, we will go ahead and select the data in column F.
After selecting the cell or range containing the formulas you want to lock, navigate to the data menu and select the option for Protect Sheets and Ranges.
Step 2: Make changes from the Data protection window
After selecting the option for Protect sheets and ranges, Google Sheets automatically launches the Protect sheets and ranges editor on the sidebar. Here is what it looks like.
Now, head to the Protected sheets and ranges editor and select the cell range you intend to lock. For this guide, we want to lock cells F2:F7. So we’ll go ahead and input that in the box provided.
Note: If you look at the image we have above, you’ll notice that it automatically has the cell range we intend to lock, and that’s because we earlier selected the cells we wanted to lock.
Step 3: Set permission
Once you’re done selecting the range of cells you’d like to lock; you need to click on the option for “Set permission.”
After selecting the option for Set permission, Google Sheets automatically launches a pop-up window where you’ll choose your desired permission. For this tutorial, we will select the option “Show a warning when editing this range.” Then click on Done.
Testing changes
Once you have selected the range of cells you want to lock and configured the appropriate permissions, it’s important to test your worksheet to see whether the changes have been effected. So for our example, we will head to Column F and attempt to make some changes to see whether we will get a warning message.
Good enough, after attempting to make some changes, we instantly get a warning not to proceed. Here is a short video showing you how your worksheet should react after locking some cells:
That wasn’t too difficult, was it?
How to completely prevent edits by users
Having shown you how to add a warning to your worksheet to prevent anyone from making unauthorized changes to the formulas you have in that sheet, we want to take things up a notch by showing you how to prevent edits to your worksheet completely. This is a surefire way to prevent anyone from making unauthorized changes to your worksheet.
Step 1: Select the range of cells you want to lock the formulas
As we did in the earlier method for adding a warning to your worksheet, you need to select the range of cells you want to lock. For this example, we will select cells F2:F7.
Step 2: Data menu > Protect sheets and ranges
Having highlighted the cells you want to lock, head over to the Data menu and click on “Protect sheets and ranges.”
Step 3: Make changes from the Protect sheets and ranges editor
When you select Protect sheets and ranges, Google Sheets automatically launches the Protect sheets and ranges editor to the extreme right of your spreadsheet.
Note: If you look at the image above, you’ll notice it already has a range of selected cells. That’s because we already selected the range of cells we want to lock.
Step 4: Set permission
With the range of cells we want to lock now selected, we need to click on the “Set permissions” option just like we did when adding a warning to our worksheet.
When you select the option for “Set Permission,” Google Sheets automatically launches a pop-up window where you need to select your preferred permission. But instead of choosing the option for “Show warning when editing this range,” we will go with the second option, “Restrict who can edit this range.”
After selecting the option for “Restrict who can edit this range,” you’ll be provided with several options. Click on the dropdown menu and select the people who can edit the range. Since we don’t want anyone to edit the sheet, we will select the option for “Only you.” By selecting this option, only you can make changes to the worksheet.
If anyone attempts to make changes to your spreadsheet, they won’t be able to make any changes because of the restrictions you have in place. So for us, this is the most effective way to lock cells in Google Sheets.
Note: While the steps we have shown you above will automatically prevent anyone from making any unauthorized changes to locked cells in Google, it’s important you know that if a user decides to make a copy of your spreadsheet with locked formulas, they will become the owner of the copy. To this end, they will be able to edit locked ranges.
Final thoughts
If you have an important Google Sheets project you’re working on, especially one with a lot of formulas, it makes a lot of sense to lock some cells to prevent anyone with access to the sheet from making unauthorized changes.
In today’s guide, we showed you how to lock cells in Google Sheets to prevent the editing of formulas. While we showed you how you could add a warning pop-up message to your spreadsheet to prevent other users from editing specific cells in your spreadsheet, we also showed you how to outrightly prevent anyone from making changes to your spreadsheet. Depending on what works best for you, you can choose either option.
We have included pictures and video illustrations to simplify the entire process. In case you hit a brick wall while attempting to lock cells in Google Sheets, we are sure these resources will provide some guidance.
Want more exciting Google Sheets tutorials? Feel free to check our blog, as we have plenty of Google Sheets tutorials that will help you resolve any challenges you might have with Google Sheets.
Also, if you have specific Google Sheets issues you want us to cover, feel free to leave a comment, and we will be happy to help.