Have you ever needed to lock cells in Google Sheets to prevent users from modifying certain parts of a spreadsheet while still allowing them to access and make changes to other parts? For example, imagine a scenario where users need to perform calculations in a spreadsheet, but you don’t want them to modify the original data.
In this guide, we will explore how to lock individual cells and cell ranges in Google Sheets, giving you complete control over your spreadsheets.
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 “Show more actions on cells” option, then select “Protect range.”
Step 3: A sidebar titled “Protected sheets and ranges” will appear on the right-hand side of Google Sheets.
In the sidebar, you can see that the range you want to lock is already specified.
Step 4: In the sidebar, click on “Set permissions.” A pop-up box will appear, offering you options to protect the range.
Step 5: Select the option “Restrict who can edit this range,” and make sure the dropdown menu is set to “Only you.”
Here are all the options:
- Restrict who can edit: Only selected users can edit the cells.
- Only you: Only the spreadsheet owner can edit the specified cells. This is what you should select if you want to lock cells for other users.
- Custom: A number of users can be added to have editing permissions for the specified cell range. In the box, enter the email address and press the space bar to separate the entries.
Step 6: Click on “Done.”
Congratulations! The selected cells are now locked and can only be modified by the owner of the Google Sheets spreadsheet.
Now that you know how to lock cells in Google Sheets, let’s explore 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 editing permission only to specific people, follow similar steps to cell protection. The only difference lies in the editing permissions you set.
Follow these steps (the most important steps are steps 5 to 7):
Step 1: Select the cell or cell range 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 “Show more actions on cells” option, then select “Protect range.”
Step 3: A sidebar titled “Protected sheets and ranges” will appear on the right-hand side of Google Sheets.
If you need to lock multiple different cells, you can enter a description to specify why these cells are protected.
Step 4: Click on “Set permissions” in the sidebar. A pop-up box will appear, offering you options to protect the cells.
Step 5: Select the option “Restrict who can edit this range.”
Step 6: In the dropdown menu, select “Custom.”
Step 7: Add the email addresses or names of the people you want to allow to edit the cells.
Step 8: Click on “Done.”
Now, the cells are locked for everyone except the individuals you specified in the text box.
Protecting a Specific Sheet in Google Sheets
There may be times when you want to protect an entire sheet from modification while allowing changes in other sheets within the workbook.
Follow the steps below to do so:
Step 1: Look for the sheet tab you want to lock at the bottom of the screen and click on the arrow next to the sheet name.
Step 2: A list of options will appear after clicking on the arrow. Click on “Protect sheet.”
Step 3: Click on “Set permissions” in the sidebar titled “Protected sheets and ranges” that appears on the right-hand side of Google Sheets.
Step 4: A pop-up box will appear, containing the permissions for modifying the range. Select the option “Restrict who can edit,” and make sure the dropdown menu is set to “Only you.”
You can also select “Custom” and give permission to multiple people to edit the sheet.
The “Only you” option restricts modification to you, the owner of the sheet. By selecting “Custom,” you can add other users as editors of that sheet, while other users with access will remain as “Viewers” or “Commenters.” A field will appear where you can add the Gmail addresses of the selected users.
Step 5: Click on “Done.”
How to Display a Warning but Allow Modification
Sometimes, you may want to warn users before they edit cells in Google Sheets. For example, the information is important, and you want users to think twice before making changes or to ensure they are modifying the correct cell range in the spreadsheet.
Once again, we will follow similar steps to the previous tutorials. The only difference lies in the permissions we set.
Follow these steps:
Step 1: Select the cell(s) you want to lock.
Step 2: Right-click on the selected range. A long list of options will appear. Hover over the “Show more actions on cells” option, then select “Protect range.”
Step 3: A sidebar titled “Protected sheets and ranges” will appear on the right-hand side of Google Sheets.
Step 4: Click on “Set permissions.” A pop-up box will appear, offering you options to protect the range.
Step 5: Select the option “Show a warning when editing this range.”
This allows each user to edit the cells, but a warning will appear before each modification.
How to Unlock Protected Cells
Perhaps you no longer need protected sheets and ranges in your Google Sheets. To remove them, follow these steps:
Step 1: Click on “Data,” then select “Protected sheets and ranges.”
Step 2: In the sidebar, select the protected sheets and ranges you want to delete.
Step 3: Click on the trash can icon next to the description.
Step 4: In the pop-up window, click on “Delete.”
The locked cells are now open for modification by any user with edit access to the spreadsheet.
We hope this guide has helped you gain a better understanding of how to lock cells in Google Sheets. For more tips and tutorials on Google Sheets and other productivity tools, visit Crawlan.com.
Remember, protecting your data is crucial, and with the right knowledge, you can ensure that your spreadsheets are secure while still allowing collaboration.
