Sometimes you need to lock cells in Google Sheets to prevent users from editing parts of a spreadsheet, even though they need edit access to other parts of the spreadsheet. For example, imagine a scenario in which users need to do calculations in a spreadsheet, but you do not want them to be able to edit the original data.
In this case, you cannot add users as only viewers because they need to do calculations in the spreadsheet. However, we have the solution in the rest of this article.
In one of our previous tutorials, we learned how to protect our spreadsheets and lock certain sheets in Google Sheets. We will now learn how to lock individual cells and ranges in Google Sheets.
How to Lock Cells in Google Sheets (and Cell Ranges)
Step 1: Select the cells in Google Sheets that you want to lock.
Step 2: Right click on the selected range. A long list of options will appear. Hover over the View more cell actions option, then select Protect range.
Step 3: A sidebar will appear on the right side of Google Sheets with title Protected sheets & ranges.
As you can notice on the sidebar, the range that you want to lock is already specified.
Step 4: In the sidebar, click Set permissions.
A pop-up box will appear, giving you the options for protecting the range.
Step 5: Select the option to "Restrict who can edit this range," then ensure that the dropdown menu is set to "Only you."
These are all of the options:
- Restrict who can edit - only a selected list of users can edit the cells:
- Only you - only the owner of the spreadsheet can edit the specified cells. This is what you need to select if you want to lock the cells for everyone else.
- Custom - a selected number of users can be added to have permissions to edit the specified cell range. In the box, type the email address, then type space to separate the entries.
Step 6: Click Done.
The selected cells are now locked for editing by anyone but the owner of the Google Sheets.
Now that you have learned how to lock cells in Google Sheets, we can move on to some variations that will give you more flexibility.
How to Lock Cells and Give Specific Editing Permissions
To lock cells in Google Sheets and give only specific people permission to edit the cells, you will follow similar steps to locking the cell. The only difference will be in the editing permissions that you set.
Follow these steps (the most important steps are Steps 5-7):
Step 1: Select the cell or the range of cells in Google Sheets to lock.
Step 2: Right click on the selected range. A long list of options will appear. Hover over the View more cell actions, then select Protect range.
Step 3: A sidebar will appear on the right side of Google Sheets with title Protected sheets & ranges.
If you will be locking many different cells, you can enter a description to specify why these cells are protected.
Step 4: Click Set permissions. A pop-up box will appear, giving you the options for protecting the cells.
Step 5: Select the option to "Restrict who can edit this range."
Step 6: In the dropdown menu, select "Custom."
Step 7: Add the email addresses or names of those who you want to permit to edit the cells.
Step 8: Click Done.
Now, the cells are locked to everyone except the people that you specified in the text box.
Protect a certain sheet in Google Sheets
There can be cases where you want to protect an entire sheet from editing while allowing edits in other sheets of the workbook.
Follow the steps below to do this:
Step 1: Look for the tab of the sheet that you want to lock on the lower portion of the screen and click the arrow besides the sheet name.
Step 2: A list of options will appear after clicking the arrow. Click Protect sheet.
Step 3: Click Set permissions from the sidebar labeled Protected sheets & ranges that will appear on the right side of Google Sheets.
Step 4: A pop-up box will appear containing the range editing permissions. Select the option to "Restrict who can edit," then ensure that the dropdown menu is set to "Only you."
You could also select "Custom" and give several people permission to edit the sheet.
The Only you option restricts editing to you, who is the owner of the sheet. Selecting Custom means you can add other users as Editor of that sheet while others who can access it would remain as Viewers or Commenters. A box will appear where you can add the Gmail addresses of the selected users.
Step 5: Click Done.
How to Show a Warning but Allow Editing
There are times when you want to warn people before they edit cells in Google Sheets. For example, the information is important and you want people to think twice before they edit it or double-check that they are editing the correct cell range of the spreadsheet.
Once again, we will follow similar steps to earlier tutorials. The only difference will be in the permissions that we set.
Follow these steps:
Step 1: Select the cell(s) that you want to lock.
Step 2: Right click on the selected range. A long list of options will appear. Hover over the View more cell actions, then select Protect range.
Step 3: A sidebar will appear on the right side of Google Sheets with title Protected sheets & ranges.
Step 4: Click Set permissions. A pop-up box will appear, giving you the options for protecting the range.
Step 5: Select the option to "Show a warning when editing this range."
This will allow each user to edit the cells, but a warning will appear before each edit.
This is the warning that will appear when any user tries to edit the protected cells:
A prompt to the user when he or she tries to edit protected ranges where a warning was set.
How to Unlock Protected Cells
Maybe you no longer need protected sheets and ranges in your Google Sheets. To remove them, follow these steps:
Step 1: Click Data, then select "Protected sheets and ranges."
Step 2: In the sidebar, select the protected sheets and ranges that you want to remove.
Step 3: Click the trash icon beside the description
Step 4: In the popup, click Remove.
The locked cells are now open to editing by any user with editing access to the spreadsheet.
Recommended articles
More resources
