How to Lock Cells in Google Sheets (or Lock Rows/Columns)

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!

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.

Dataaset where cells needs to be locked

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 optionRight-click and then click on Protect Range
  3. In the ‘Protected Sheets and ranges’ pane that opens up on the right, click on ‘Add a sheet or range’Click on Add a range or sheet
  4. [Optional] Enter a description for the cell you’re lockingEnter description about the locked cell
  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 cellCell reference of the cell that needs to be locked
  6. Click on the ‘Set permissions’ buttonClick on Set Permissions
  7. In the ‘Range editing permissions’, make sure ‘Only you’ is selected in the ‘Restrict who can edit this range’ optionSelect Only You in the restrict condition
  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:

Prompt when someone access the locked cell without permission

 

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.

Dataaset where cells needs to be locked

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’Select Custom as the range editing permission
  8. In the ‘Add editors:’ option, enter the email of the people who you want to allow to edit the locked cell/range/sheetEnter email of the people who can edit the locked cell
  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)
spreadsheet fundamentals

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’Right-click on sheet tab and then click on Protect 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 sheetSelect the sheet name that you want to protect
  5. Click on the ‘Set permissions’ buttonClick on Set Permissions button
  6. In the ‘Range editing permissions’, make sure ‘Only you’ is selected in the ‘Restrict who can edit this range’ optionSelect Only You in the restrict condition
  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.

Edit locked cell with Warning

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.Click on Data and then click on Protected sheets and ranges
  3. Click on the cell/range/sheet option that you want to unlock for everyone.Click on the cell or sheet you want to unlock
  4. Click on Delete icon which is next to the ‘Enter a description’ box.Click on the Delete button

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:

Most Popular Posts

Sumit

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. 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.

    Reply
    • 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

      Reply
  2. 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

    Reply
  3. 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.

    Reply
    • 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.

      Reply
  4. 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

    Reply
  5. 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.

    Reply
  6. 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!

    Reply
  7. 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.

    Reply
    • 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.

      Reply

Leave a Comment

Related Posts