How To Lock Cells In Google Sheets
When collaborating on spreadsheets, sometimes it’s necessary to prevent certain users from modifying data in portions of the spreadsheet.
For example, you might want to allow users to enter raw data into certain cells but lock other cells containing formulas so the formula isn’t accidentally overwritten.
Formula Cells Should be Locked to Prevent Accidental Edits
Google Sheets allows you to lock or protect a range of cells or an entire sheet. If you choose to protect a range, that range can be any size, from a single cell to the whole sheet.
How Protected Ranges Work
Protected ranges should be used to prevent accidental edits, but not as a security measure.
Users will still be able to print the spreadsheet, copy and paste data from the protected range(s) and even create duplicates of the spreadsheet where they have full access to all data.
Only share spreadsheets with users who should have access to all the data contained in that spreadsheet.
How to Protect a Range
To protect a range in Google Sheets:
Select the range and then open the Data menu.
Scroll down to the Protected Sheets and Ranges option and click on it:
The Protected Sheets and Ranges sidebar will appear:
In the sidebar, you can add an optional description for the range. Edit the range to ensure you’re protecting the right cells.
Click on the green Set Permissions button and the Range Editing Permissions dialog will appear:
Select if you want to restrict who can edit the range or warn users who edit the range (more info on these options below)
Click Done and your range is protected
Restrict who can edit this range
With this protection option, you can choose who can edit the protected range.
By default only you and the spreadsheet owner can edit the range, or only you if you are the spreadsheet owner. You can also choose specific users by selecting the Custom option.
Show a warning when editing this range
If you select this option, all users who edit the range will see a warning:
They can then decide if they want to cancel the edit or proceed with the edit.
Editing & Removing Range Protection
To edit or remove range protections:
- Open the Protected Sheets and Ranges sidebar from the Data menu
- To view all of the ranges with protections, click Cancel
- Click on the protected range and make your edits, then click Done to save
- You can delete the protected range by clicking the trash can icon beside the range description
Example Spreadsheet: Make a copy of the example spreadsheet
In this tutorial, I covered how to lock cells in Google Sheets. Want more? Check out all the Google Sheets Tutorials.