Do you want to protect a sheet from accidental editing? This post is going to show you how to lock your sheet tabs in Google Sheets.

There are many cases where you will want to protect certain ranges or a whole sheet in your spreadsheet. After spending considerable time organizing and cleaning a dataset you might not want others to change your work.

If other people have access to the document, you might want to lock entire sheets that contain important information.

In this post, you’ll learn how to lock a sheet using the menu bar, right-click menu, a keyboard shortcut, and a custom apps script.

You’ll also learn how to lock multiple sheets at a time.

Get your copy of the example workbook used in this post and follow along!

Lock a Sheet Tab

There are a couple of ways to lock a sheet.

Lock a Sheet Tab with the Sheet Menu

Right-click anywhere on the sheet tab which you want to lock. This will open the Protected sheets and ranges menu where you will be able to lock the sheet.

Lock a Sheet Tab with the Right Click Menu

Right-click anywhere on the worksheet of the sheet tab which you want to lock. This will open the Protected sheets and ranges menu where you will be able to lock the sheet.

Lock a Sheet Tab with the Data Menu

Go to the Data menu then choose the Protect sheets and ranges option. This will open the Protected sheets and ranges menu where you will be able to lock the sheet.

Lock a Sheet Tab with a Keyboard Shortcut

Press Alt, H, O, P on your keyboard to open the Protect sheets and ranges menu.

Protected Sheets and Ranges Menu

When you follow any of the previous methods, you will open the Protected sheets and ranges dialogue window on the right side of the spreadsheet. From here you can protect a sheet or a selected range.

Follow these steps to protect a sheet.

1. Click on the Add a sheet or range menu.

2. Select the Sheet tab and write a description in the textbox.

Adding a descriptive name can help you remember the reason for locking the sheet.

3. Click on the box that displays the sheet name and select the sheet you want to lock.

4. Click on the Set permissions button.

The Range editing permissions dialogue box appears when you click on Set permissions. You can choose what happens when other users try to edit the locked sheet.

5. Select the Restrict who can edit this range radio button and choose Only you in the dropdown so only you can edit the locked sheet.

You can also choose the Custom option. This allows you to add the full names or email addresses of other users who can edit the locked sheet.

The last option from which you can select in the Restrict who can edit this range permission setting is the Copy permissions from another range option. This option is effective when you already have restrictions in place on other sheets.

Copy permissions from another range will list the ranges or sheets with restriction settings and you can select the one you want to use.

You might not want to completely block users from editing a sheet. For cases like this, you can use the Show a warning when editing this range option.

The user gets notified that the sheet contains crucial information, and care must be taken before editing.

Lock a Sheet Tab Except for Specific Cells

It is possible to protect a sheet but leave out certain cells.

For instance, in this illustration, some names in the Name column are missing. We can lock the sheet but leave the empty cells unlocked and this is very easy to do.

Enable the Except certain cells option by checking the box next to it.

Click on the grid icon and select the range you don’t want to lock. Click the OK button when you are done.

Click on Add another range to select another range.

You can use the X to remove any of the unprotected ranges. Then click the Done button to apply the new permission settings.

Now only those empty cells can be edited by anybody.

Lock Multiple Sheet Tabs at Once with the Sheets Manager Add-on

If your spreadsheet has multiple sheets and you want to lock a number of them, doing it one at a time can be daunting.

There’s no native solution within Google Sheets to lock multiple sheets at once. Nevertheless, there’s an add-on that you can download from the Google workspace market called Sheets Manager that makes it possible to lock multiple sheets at once.

To download the add-on, go to the Extensions menu ribbon and select Add-ons then click on Get add-ons. This takes you to the Google workspace market.

In the workspace market, search for Sheets Manager by Ablebits. Click on Install. You will be prompted to grant permission.

Exit the workspace market. Go back to the Extensions menu ribbon. You will find the Sheets Manager add-on in the dropdown. Select it and click Start to use.

When you start the add-on, a side panel appears on the right side of the spreadsheet. From here, you can lock multiple sheets.

The add-on lists the names of all visible sheets. To lock multiple sheets, press and hold the Ctrl key to highlight more than one sheet.

While the sheets you want to lock are selected, click on the padlock icon and select your lock preference. Here, the Restrict editing option is selected.

You can also get this option if you right-click on the selected sheets.

After a few seconds, all three selected sheets are locked.

The drawback to using this add-on is that you cannot exempt some cells when locking a sheet. With the add-on, all cells in the sheet are locked.

The Sheets Manager add-on is a free tool, but you can subscribe for a paid version to get the most out of it.

Lock Sheet Tabs with an Apps Script

You can also use an app script to protect your spreadsheet from unauthorized edits.

Go to the Extensions menu and select Apps script to open the editor window.

function sheetsProtect() {
  var ss = SpreadsheetApp.getActive();
  var sheets = ss.getSheets();
  var editorSheet = ss.getSheetByName('Editors');
  var editorList = editorSheet.getRange(1, 1, 1, 1).getValues();
  
  for(i = 0; i < sheets.length; i++) {
  sheets[i].protect().setDescription('Lock with Apps Script').addEditors(editorList);
  }
}

function onOpen(e) {
  SpreadsheetApp.getUi()
  .createMenu("Lock Sheet")
  .addItem('Protect Sheet', 'sheetsProtect')
  .addToUi();
}

Copy and paste the above code into the app script editor. Hit the Save and then Run button and grant the necessary permissions.

The script creates a Lock Sheet custom menu ribbon when you refresh the sheet. When you click on it, you will find the Protect Sheet submenu.

When you click on Protect Sheet, all the sheets will be protected at once, while granting editing permission to any specified user whose email address is in the first cell in the Editors sheet.

Conclusions

When working on a shared spreadsheet, security is usually very essential.

While locking a range or a sheet might not be a high-level security measure, it does allow you to control the level of interaction other users have with your dataset.

Whether you use the local protection option available in the spreadsheet, the Sheets Manager add-on, or the Lock Sheets custom app script, you can implement different measures that limit what other users can do in your spreadsheet.

Do you ever lock your sheets? Do you have other custom solutions for locking a sheet? Let me down in the comments!