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

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)

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:

Leave a Comment