How to Lock Cells in Google Sheets (Step by Step)

puneet-gogia-excel-champs

- Written by Puneet

In Google Sheets, lock a cell refers to the process of restricting the ability of other users to edit it and make changes to it. This feature is particularly useful in collaborative environments where multiple users have access to the same sheet, allowing the owner or specified users to control who can modify parts.

In this tutorial, you learn all the details of how to lock a single as well as multiple cells in Google Sheets. And all the other details with it.

Benefits of Locking Cells in Google Sheets

There are a few amazing benefits of using the lock cells option in Google Sheets:

  • Control Editing Permissions: Allows selective access to sensitive or important cells which helps in maintaining the correct input and formulas.
  • Enhance Collaboration: Safely share spreadsheet among team members without risking data corruption or unintended changes.
  • Preserve Data Integrity: Prevents accidental or unauthorized changes to data.

Steps to Lock Cells in Google Sheets

To lock a cell or a range of cells in Google Sheets is quite simple and easy, you don’t need to make much change to the settings and other options. You just need to follow a few quick steps for this:

  • Select the Cells You Want to Lock – Select the cells or range that you want to protect. You can also click on a column or row label to select the entire column or row, or use the keyboard keys Shift + Arrows to select the range.
  • Open the “Protect sheets and ranges” Option – Go to the Data menu on the top menu bar. Click on Protect sheets and ranges. This will open a sidebar on the right side of your screen titled “Protected sheets and ranges”.
  • Verify the Selected Range – On the right side of the screen you have the pane opened. And in this pane, you have the address of the range or the cell which you have selected. Even if you want to change the range, you can change it from there by typing or by “Select range button”.
  • Set Permissions – Click on the Set permissions button which will open a new dialog. Here you’ll decide who can edit the protected cells. You will have a few options for permissions.
  • Restrict who can edit this range: Select “Only you” to prevent others from editing, or “Custom” to select specific people who can edit. If you select “Custom,” you can enter the email addresses of those you wish to allow to edit the cells. You can also select groups if they have been set up in your organization.
  • Done – Click Done after setting the permissions. Your selected cells/range or sheet is now locked.

The protected range will appear listed in the “Protected sheets and ranges” sidebar. It will show the range, description (if added), and who has permission to edit.

Now when a use will try to edit a cell from the range “A1:B4”, he/she will get an error show that this range or cells is locked.

Note – When you lock a cell or a range of cell, you can still change its formatting, height and width and copy and

Show Warning When Editing this Range.

Instead of the protect a range, you can also use the “Show Warning When Editing This Range” which shows a prompt or warning message to the user when they attempt to edit cells within a specified range.

When you go to the “Set permissions”, instead of choosing “Restrict who can edit this range”, select the option that says “Show a warning when editing this range”. As I Said, this won’t prevent users from editing the selected cells, but will show them a warning message when they try to do so.

This can be helpful when you want to allow edits but also remind users to be careful or double-check their changes.

Protect an Entire Sheet in Google Sheets (Lock all the Cells in a Sheet)

Go to the Data menu on the top menu bar. Select Protected sheets and ranges from the dropdown menu. In the sidebar, click on the “Sheet” tab. Now click on the dropdown menu under “Sheet” and select the sheet you want to protect.

Once you have selected the sheet to protect, click on Set Permissions to specify who can edit the protected area. You will see a new window where you can configure who has permission to edit the sheet.

Click “Done” after setting the permissions. The entire sheet is now protected; only the people you have specified can make changes.

In the same pane, you also have the option to lock all the cells from the entire sheet but along with that unlock certain cells from the user to edit and make change.

Note – You need to repeat the process for each sheet you want to protect in the same spreadsheet.

Copy Permission from another Range

If you already have a lock range or cell in the sheet, and now you want to share a new one with the same set of people or use the same editing permission, you can use permissions from another range.

With this option, you can save a lot of time as you don’t have add the name of the users again and again with whom you want to share the locked cells.

Open “Protected sheets and ranges” from the Right Click Option

When you select a range or a cell and you want to lock it, you can open the “Protected sheets and ranges” option from the right click menu as well. When you right click, in the last of the menu you will find the option “View more cells actions” and in this you will find the option “Protect range”.

When you click on this option, you will have the side pane to protect cells and sheets. The same option which we have used in the above examples.

Changing Edit Permissions

When you are locking a cell or a range and give access to a user to edit that cell. But may be in future you want to remove that access or permission from the user. In this case, you can un-tick that user name from the list of custom.

And if you want to give the permission back to the user you just need to tick mark that user again.

Locking a Cell in Google Sheets if you are using Mobile App (Android and iPhone)

As you can use Google Sheets on you Mobile Phone, right now there’s no direct option to lock a single cell or multiple cells in a Google Sheets from the Mobile App. All you can do is to restrict the access.

Click on the three dots (…) on the top right of the screen and then go to the Share and Export > Manage Access > Restricted > Restricted. And you can choose the person to give access or you can give access to anyone with the link.

Unlock Cells (which are Locked) in Google Sheets

Click on Data in the menu and then the Protected sheets and ranges. The right sidebar will then show all the protected ranges and sheets.

In the sidebar, you will see a list of all protections. Each entry corresponds to a protected range. Click on it, and a details panel will appear, showing the range, description, and permissions. Click on the “Trashcan” icon to delete the protected range.

Confirm the removal; the cells will now be unlocked and editable by anyone with access to the Sheets.

In the same way, you want to unlock

Lock Selected Cells with Google Apps Script

Below is a Google App Script to lock all the selected cells.

function lockSelectedCell() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var cell = sheet.getActiveCell();  // Gets the currently selected cell

  var protection = cell.protect();  // Protects the selected cell

  // Set permissions: change this part to restrict access appropriately
  protection.removeEditors(protection.getEditors());
  if (protection.canDomainEdit()) {
    protection.setDomainEdit(false);
  }

  // Optionally, add a description to the protection
  protection.setDescription("Locked cell to prevent edits");

  SpreadsheetApp.getUi().alert('The cell ' + cell.getA1Notation() + ' has been locked.');
}

Unlock All the Locked Cells in from a Sheet in One Go

Copy and paste the following script into the script editor, and then run it.

function delProtectedRanges() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var protections = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);

  for (var i = 0; i < protections.length; i++) {
    protections[i].remove();
  }
}

Quick Tips to Note Down

Here are a few quick tips you can note down to take care while locking cells in Google Sheets

  • Use Descriptive Labels: When locking up cells, use the description field to note why the range is protected. This helps prevent confusion and gives collaborators clear indications of the range’s purpose.
  • Protect Formulas: Ensure that cells containing critical formulas are locked to prevent accidental modification which could affect calculations across the sheet.

Owners Vs. Editor

Owners have full permissions over all aspects of a Google Sheet, including adding, modifying, and removing protections. Editors can usually protect ranges or sheets, but their capabilities might be limited based on restrictions set by the owner.

Can I Edit the Locked Cells?

Last Updated: April 19, 2024