When sharing sheets with your team, you may want to share only one sheetย or limit parts of the sheet that they can edit.
Google Sheets allows you to lock cells easily to prevent other people from changing these specific cells.
These could be some specific cells that you want to lock or could be cells in an entire row/column or even the entire sheet. In this tutorial, weโll show you how to lock cells in Google Sheets.
Let’s get started!
Table of Contents
In Short: How To Lock Cells in Google Sheets
Hereโs how to protect cells in Google Sheets by locking:
- Right-click on the cell that you want to lock.
- Go to “View more cell actions” and click the “Protect range” option.
- In the “Protected sheets and ranges” pane that opens up on the right, click on “Add a sheet or range.”
- [Optional]ย Enter a description for the cell you’re locking.
- In the “Range” option, you will see the selected cell’s reference. You can change this here if you want to lock any other cell.
- Click on the “Set permissions”ย button.
- In the “Range editing permissions,” ensure that “Only you” is selected in the “Restrict who can edit this range” option.
- Click on “Done.”
Why Lock Cells in Google Sheets
There are several reasons why you may want Google Sheets to lock cells from editing when sharing. This is especially useful when multiple people enter data or change formulas on the same sheet. There is a high chance someone may accidentally edit to delete some cell’s content.
To prevent this, it’s best to lock the cells and only give access to those cells to collaborators where you need them to make the changes. You can restrict your entire spreadsheet or protect cells in Google Sheets by locking them.
Letโs look at how to lock cells in Google Sheets.
Lock Specific Cells In Google Sheets
If your Google Sheets document has some cells that should not be changed, you can lock these so that no one is allowed to change them.
For example, suppose I have the data, as shown below, and I don’t want anyone to mess with the value in cell E2. I can lock the cell.
Below are the steps to lock a cell in Google Sheets (so that no one can change it except you):
- Right-click on the cell range that you want to lock.
- Go to “View more cell actions” and click the “Protect range” option.
- In the “Protected sheets and ranges” pane that opens up on the right, click on “Add a sheet or range.”
- [Optional] Enter a description for the cell you’re locking.
- In the “Range” option, you will see the selected cell range’s reference. You can change this here if you want to lock any other cells.
- Click on the “Set permissions”ย button.
- In the “Range editing permissions,” make sure “Only you” is selected in the “Restrict who can edit this range” option.
- Click on “Done.”
The above steps would lock the cell (E2), so only you can change it.
If anyone other than you tries to change the cell, they will get a dialog box as shown below: They wonโt be able to even change the row height.
If youโre wondering how to lock columns in Google Sheets, you can use the same steps to lock entire rows and columns. All you need to do is select the range you want to lock and then follow all the steps from Step 2 onwards. You donโt need to use absolute referencesย when locking cells.
Note: You can not lock a non-contiguous range of cells in one go. You must do it individually. Just select a range of cells, lock it, and then do the same for the other range of cells.
How To Lock a Single Cell
You may also want to lock a single cell in your spreadsheet for various reasons. The method for this shouldnโt be any different from locking multiple cells and locking columns in Google Sheets.
Here’s how to lock a cell in Google Sheets:
- Right-click on the cell you want to protect.
- Go to “View more cell actions”ย > “Protect range.”
- Click “Add a sheet or range.”
- Type a name or description for your cell.
- Click “Set permissions.”
- Choose the specific restrictions you want to use for that cell.
- Click “Done.”
With this method, a single cell will be locked depending on the permission settingsย you use, but the rest of the spreadsheet will be accessible to anyone you share with.
Lock Cells And Give Edit Permission to Selected People
Now, what if you want to lock a cell so that some selected people other than you can also edit the locked cells?
In the awesome world of Google Sheets, this can be done easily.
Suppose you want to lock cell E2, but this time, you also want to give your colleague the ability to edit it if needed.
Below are the steps to lock a cell (or range of cells) and give edit permission to someone (can be multiple people):
- Right-click on the cell that you want to lock.
- Click on “View more cell actions” > “Protect range” option.
- In the ‘Protected sheets and ranges‘ pane that opens up on the right, click on “Add a sheet or range.”
- [Optional]ย Enter a description for the cell you’re locking.
- In the “Range” option, you would already see the selected cell’s reference. You can change this here if you want to lock any other cell.
- Click on the “Set permissions”ย button.
- In the “Range editing permissions,” select the “Restrict who can edit this range” option, and then click “Custom.”
- In the “Add editors:” option, enter the email of the people you want to allow to edit the locked cell/range/sheet.
- Click on “Done.”
The above steps would lock the selected cell for everyone except for those people whose email you have entered in Step 8.
Note: You can add more people to this list if you want to give edit permission to more people later. And in case you want to revoke the permission of someone who already has it, you can do that as well.
I find this ability to allow selective access to cells/ranges while locking all the other cells really useful. Imagine you work with 10 people who need to enter data in the same Google Sheets document. You can set the permission in such a way that each person gets editable access to only those cells that they have to fill. This way, they can’t mess with other people’s data (intentionally or by accident).
Protect Entire Sheets in Google Sheets
Just like you can protect a cell or a range of cells, you can also protect an entire sheet.
And again, just like cells and ranges, you can also choose to keep it completely locked for everyone or give access to some people who can edit it.
Also, when protecting a sheet, you can choose to keep some of the sheets unlocked.
Below are the steps to protect an entire sheet in Google Sheets:
- Right-click on the sheet tab that you want to protect.
- Click on “Protect the sheet.”
- [Optional] In the “Protected sheets and ranges” pane that opens up, enter a description for the sheet you’re protecting.
- In the Sheet option, you will see the selected sheet name. You can change the sheet nameย here in case you want to protect some other sheet.
- Click on the “Set permissions”ย button.
- In the “Range editing permissions,” make sure “Only you” is selected in the “Restrict who can edit this range” option.
- Click on “Done.”
The above steps will protect the sheet so that no one other than you can edit it.
There are some additional things you can do when locking a sheet in Google Sheets:
- You can give access to some people who can edit it. To do this, select “Custom” in Step 6 and then specify the emails of the people who would have access to the sheets and can edit it.
- You can choose to keep some cells unlocked while the entire sheet is protected. To do this, in the “Protected sheets and ranges” pane, click on the “Except certain cells” option and then specify which cells you want to keep unlocked.
Show Warning But Allow Editing of Locked Cells
Another option that Google Sheets gives you when locking cells is to not lock these completely but show a warning message when someone (who has the right to edit the sheet).
This wouldn’t restrict the person from editing the cell you have locked, but they would see a warning (as shown below) when they change the cell content.
This could be useful as it would remind the user that these cells are to be handled carefully and they don’t end up changing the cell content or deleting it by mistake.
How To Lock Cell in Google Sheets Mobile App
Currently, locking cells in Google Sheets mobile app is impossible, as there isnโt an option to protect a range.
However, you can protect your entire sheet by managing access. To do this:
- Open your sheet in the mobile app.
- Tap the three dots to the right to open a menu.
- Go to “Share and export.”
- Choose “Manage access.”
- If you have people added to the spreadsheet, choose the person you want to change permission settings for and select the permissions you want to use. If you choose the “Viewer” or “Commenter” option, the other user can not edit the sheet. You can also choose to remove them completely so that they donโt have any access to the sheet.
- If you want to change general access for everyone, Tap “Restricted” and choose the settings you want.
How To Unlock a Locked Cell
In case you have locked cells in the sheet and you want to unlock these, follow the below steps:
- Click the “Data” option in the menu.
- Click on “Protect sheets and ranges.” This will open the “Protect sheets and ranges” pane on the right, which will list all the cells/ranges that you have locked.
- Click on the cell/range/sheet option you want to unlock for everyone.
- Click on the “Delete” icon, which is next to the “Enter a description”ย box.
- Youโll get a prompt asking if youโre sure you want to remove the protected range. Click “Remove.”
Your cell range will now be unlocked, and anyone with access to the spreadsheet can change it.
How To Hide and Unhide a Sheet
You can hide and unhide a sheet in Google Sheets by following these steps:
- Open your spreadsheet.
- Locate the sheet that you want to hide.
- Click the drop-down arrow on the sheet tab.
- Select “Hide sheet” from the context menu.
This will hide your sheet so that it does not appear in the sheet tabs at the bottom, and if you go to options, the tab for the sheet will be grayed out.
To unhide the sheet,
- Click the drop-down arrow on the sheet tab.
- Select “Unhide sheet” from the menu.
- Go to “View” > “Hidden sheets.”
- Click “Show Sheet1,” (it will show the name of the hidden sheet).
The sheet will be restored to view, and the tab for the sheet will be visible and active.
You can also click the three lines next to the sheet tab and click the hidden sheet, which is grayed out, and it will reappear.
Rules for Importing and Exporting Spreadsheets with Hidden Sheets
The following are some of the rules for importing and exporting spreadsheets with hidden sheets:
- Sheets that are hidden will remain hidden when you import and export the spreadsheet.
- Sheets that are hidden will remain hidden if you export the spreadsheet to HTML with the “/htmlview” parameter.
- Hidden sheets will show if you include the page parameter (#gid=N) in the URL.
- Theyโll remain hidden if you publish the spreadsheet.
Who Can Protect a Range or Sheet?
In Google Sheets, anyone with edit access to a sheet can protect a range or sheet. However, it’s important to note that protection settings can only be changed by the person who originally set them or someone who has been granted permission to edit the protected ranges or sheet.
For example, if you protect a range in a sheet and share it with others, those users can view the protected range but will not be able to edit it unless you permit them to do so. If you want to allow someone else to edit the protected range, you can add them to the list of users in the permission settings for the protected range.
Frequently Asked Questions
How Do I Lock Certain Cells in Google Sheets?
You can lock certain cells in Google Sheets by following these steps:
- Select the cells that you want to lock.
- Right-click on the selected cells.
- Go to “View more cell actions.”
- Click on the “Protect range”ย option.
- In the right panel that appears, click on the “Set permissions” button.
- In the “Range permissions” dialog box that appears, click on the “Restrict who can edit this range” checkbox.
- Select the users you want to allow editing access to, such as “Only you” or specific people in your organization.
- Click on the “Done” button to save your changes.
Why Can’t I Lock Cells in Google Sheets?
If you cannot lock cells in Google Sheets, it may be because you do not have Editor access to the sheet. Youโll need to ask the owner to give you “Editor permission” for that spreadsheet.
Another reason could be that the sheet is protected, and certain cells or ranges may be locked to prevent editing. Only the person who protected the sheet or someone who has been granted permission to edit the protected ranges can make changes to those cells.
Can You Lock Cells from Being Edited in Google Sheets?
Yes, you can lock cells from being edited in Google Sheets by using the “Protect sheets and ranges” feature. Once the cells are locked, other users canโt make any changes or format, like alternating row colors. Weโve shown all the ways to lock cells above with step-by-step guides.
Why Can’t I Protect a Range in Google Sheets?
If you cannot protect a range in Google Sheets, there could be several reasons for this. One of them is that you donโt have edit access to the sheet. Check and make sure that you have the necessary permissions to edit the sheet.
Another reason could be that another editor already protects the sheet. In that case, youโll need to ask to get permission from the editor who set the protection.
Conclusion
So these are the ways you can lock cells in Google Sheets, lock entire rows or columns, and protect sheets. Although there are no how-to lock cells in Google Sheets formulas, you can still protect your data using the protected range option.
You can also choose to give specific people editable access to the locked cell, which is great when collaborating with multiple people on the same Google Sheets document.
I hope you found this Google Sheets tutorial useful! If you’re still having trouble with this or any other Google Suite problem, you could consider taking a comprehensive G Suite Courseย to boost your learning. Or you could even just use our premade templatesย and use the code SSP to save 50% on all template purchases.
Related:
16 thoughts on “How To Lock Cells in Google Sheets (Easy 7 Min Guide)”
I want to lock a cell to prevent accidental edit. I don’t share a document with another user. I just want to lock the cell from myself so I can’t edit it until I unlock the cell. Is it possible?
Yes Kvrka. You can lock the cell just from yourself, or you can have it display a warning when you accidentally try to edit it.
This doesn’t actually work in Google Sheets. No matter what range or cell you specify, once you set the permissions to only allow yourself to edit, the entire sheet is locked to everyone else. So even the cells that weren’t selected in the range is not editable.
Hi Angie, I’m sorry to hear you’re having trouble. I think maybe there’s been an issue with the selection of the cells. In the Protect Range menu make sure the range is specified in the box (identified in the red box in step 5) so for example, if you only want to lock cells A1 to A5 it should read Sheet1!A1:A5
Actually I think the issue is that Google Sheets is not giving the option of locking cells anymore. If someone finds a solution, it would really help! I haven’t found one yet
Is there a way to lock a cell to eveyone, including myself? I have several cells (most of them actually) that will never change. Have already accidently changed them a couple times. Looking to prevent that.
Follow the steps above and choose “Show warning” that way you’ll get a pop up window that will let you know you’re about to edit a cell you shouldn’t.
I have copied the sheet from another sheet and then added data to it, now though I am owner of the sheet, the “The protect sheet” option does not appear at all. I tried closing the sheet and reopening from my drive as well but still the option does not appear.
Please help
How to I lock a cell that contains a formula I create so that I cannot accidentally delete or alter it? Obviously, I may want to be able to edit that cell at some future date, but I want to lock it so that even I cannot edit it (accidentally) without unlocking it.
You can use the steps above and choose “show warning” so a pop up message will warn you before you edit the cell.
How do I suppress the padlock icon in the tab? Takes up way too much room on my multi-tab doc – it needs – NEEDS – to disappear. Help!
Is there a way to lock/unlock a cell based on date ranges? For instance I have one sheet that I’m using to balance finances coming in, another tracking going out and one that will combine the two into a monthly summary but I want to record the monthly summary automatically once the new month starts and the old one ends.
There’s no simple way to do this. But if you sort the sheet by date it will only take a few moments to select the cells you wish to lock.
Hi,
I go through your all specified steps, but in my Google Sheet, there are not option like Protect Sheet or anything like that.. what to do ??
How can I keep a range of cells locked when I duplicate a sheet? For example, I’ll be duplicating the sheet each week and allowing my staff to edit certain cells, but I want to prevent the formulas from being changed/deleted. Any way to do this without locking the range each time I duplicate the sheet?
Permissions is the easiest way. In the side panel, click “Set Permissions,” and ensure that your staff has the appropriate editing rights. Then, duplicate the sheet each week. The protected range will remain locked, preventing accidental changes to formulas. This method avoids the need to manually lock the range each time you duplicate the sheet, providing a convenient way to secure specific cells while allowing staff edits in other areas.