How to make certain cells uneditable in Google Sheets

One of the best things about Google Sheets is that allows you to collaborate with other people easily.

When sharing your work with other people, you may also want to lock some cells in Google Sheets (or maybe lock specific rows/column or lock the entire sheet itself). You might want to share only one sheet with your coworkers which is also possible.

Google Sheets allows you to easily lock cells so that you can prevent other people from making changes to these specific cells.

This is especially useful when you have multiple people entering data or changing 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 only give access to those cells to collaborator where you need them to make the changes.

In this tutorial, I will show you how to lock cells in Google Sheets. 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.

So let’s get started!

Table of Contents

Lock Specific Cells In Google Sheets

In case your Google Sheets document has some cells that should not be changed, you can lock these so that no one is allowed to make any changes to it.

For example, suppose I have the data as shown below and I don’t want anyone to mess with the value in cell E2. So, 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):

1. Right-click on the cell that you want to lock
2. Click on Protect range option
   
3. In the ‘Protected Sheets and ranges’ pane that opens up on the right, click on ‘Add a sheet or range’
   
4. [Optional] Enter a description for the cell you’re locking
   
5. 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
   
6. Click on the ‘Set permissions’ button
   
7. In the ‘Range editing permissions’, make sure ‘Only you’ is selected in the ‘Restrict who can edit this range’ option
   
8. Click on Done

The above steps would lock the cell (E2) so that only you can change it.

In case anyone other than you try to change the cell, they will get a dialog box as shown below:

While in this example I have just locked one single cell, you can use the same steps to lock a range of cells or even entire rows and columns. All you need to do is selected the range that you wan to lock and then follow all the steps from Step 2 onwards.

Note: You can not lock a non-contiguous range of cells in one go, but you can do that one by one. Just select a range of cells, lock it, and then do the same for the other range of cells.

Lock Cells And Give Edit Permission to Selected People

Now, what if you want to lock a cell in such a way 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):

1. Right-click on the cell that you want to lock
2. Click on Protect range option
3. In the ‘Protected Sheets and ranges’ pane that opens up on the right, click on ‘Add a sheet or range’
4. [Optional] Enter a description for the cell you’re locking
5. 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
6. Click on the ‘Set permissions’ button
7. In the ‘Range editing permissions’, in the ‘Restrict who can edit this range’ options, select ‘Custom’
   
8. In the ‘Add editors:’ option, enter the email of the people who you want to allow to edit the locked cell/range/sheet
   
9. 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 that 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 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 get 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:

1. Right-click on the sheet tab that you want to protect
2. Click on ‘Protect the sheet’
   
3. [Optional] In the ‘Protected Sheets and ranges’ pane that opens up, enter a description for the sheet you’re protecting
4. In the Sheet option, you would already see the selected sheet name. You can change sheet name here in case you want to protect some other sheet
   
5. Click on the ‘Set permissions’ button
   
6. In the ‘Range editing permissions’, make sure ‘Only you’ is selected in the ‘Restrict who can edit this range’ option
   
7. Click on Done

The above steps would 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:

1. You can give access to some of the 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.
2. 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 which 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 that 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 Unlock a Locked Cell

In case you have locked cells in the sheet and you want to unlock these, follow the below steps:

1. Click the Data option in the menu
2. 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.
   
3. Click on the cell/range/sheet option that you want to unlock for everyone.
   
4. Click on Delete icon which is next to the ‘Enter a description’ box.
   

So these are the ways you can lock cells in Google Sheets, lock entire row or column, and protect sheets. 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!

Other Google Sheets tutorials you may like:

• How to Freeze Rows in Google Sheets
• How to Group Rows and Columns in Google Sheets
• How to Merge Cells In Google Sheets
• How to Combine Cells in Google Sheets (using simple Formulas)

Most Popular Posts

5 Simple Ways to Highlight Duplicates in Google Sheets

IF CONTAINS Google Sheets Formulas [2 Clever Options]

How to Apply Formula to Entire Column in Google Sheets

How to VLOOKUP From Another Sheet in Google Sheets

Sumit

Sumit is a Google Sheets and Microsoft Excel Expert. He provides spreadsheet training to corporates and has been awarded the prestigious Excel MVP award by Microsoft for his contributions in sharing his Excel knowledge and helping people.

11 thoughts on “How to Lock Cells in Google Sheets (or Lock Rows/Columns)”

1. Angie

   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.

   • Christopher Daniel

     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

2. Luidgi

   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

3. Jimmy Carpenter

   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.

   • Christopher Daniel

     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.

4. Mihir Kanabar

   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

5. Gary Barnes

   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.

   • Christopher Daniel

     You can use the steps above and choose “show warning” so a pop up message will warn you before you edit the cell.

6. Brock McEwen

   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!

7. Steven

   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.

   • Christopher Daniel

     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.

     How do I restrict editing for specific cells in Google Sheets?

     Protect a sheet or range.

     Open a spreadsheet in Google Sheets..

     Click Data. Protect sheets and ranges. ... .

     Click Add a sheet or range or click an existing protection to edit it..

     To protect a range, click Range. ... .

     Click Set permissions or Change permissions..

     Choose how you want to limit editing: ... .

     Click Save or Done..

     Can I lock certain cells in Google Sheets?

     You can lock some of the cells while still allowing collaborators to make changes to others. Start by identifying and selecting the cells that you want to lock. Open the Data menu at the top of the document, and then select Protected Sheets and Ranges in the dropdown menu.

     Why can ti protect cells in Google Sheets?

     You must give another user permission to edit the whole spreadsheet before you can protect a range. For example, if you want someone to only be able to edit column B, you would need to first give them permission to edit the spreadsheet, and then follow the procedure below to protect all the other columns.

     How do I lock a cell in a formula in Google Sheets?

     To lock it, click on the cell reference in the formula bar (B2), and enter $ before column and row ($B$2). You can also press F4 on the keyboard to freeze a cell.