In this tutorial, we’ll learn how to protect a range of cells in Google Sheets. We’ll see different options related to this feature.
Google Sheets is often used for collaborating with colleagues or fellows. However, sometimes you might need to restrict access to your file. You might want only a certain group of people to view or edit the document. Likewise, it is often necessary to restrict the edit or modification privileges.
In Google Sheets, you can protect a certain range of cells or entire sheets. This protection can restrict any modification that can be done intentionally or accidentally. Therefore, in this article, we are aiming to teach you this useful feature.
So how should we go about this problem?
We’ll first learn about different options related to granting permissions. Then we will move on to the actual tutorial. We have created multiple tutorials that cover different use cases. After you go through this article, you’ll learn practically everything related to this feature in Google Sheets.
We’ve also added a tutorial that will show you how to unlock protected cells or sheets.
Different Options In Cell Locking
Before moving to the main topic, we will get familiar with different options available to us while we restrict edit privileges in our spreadsheets. The Range editing permissions menu will pop up every time you try to protect or lock a certain range of cells or an entire sheet. There are several options on this menu.
- Show a warning when editing this rangeIf you proceed with this option, then the range of cells or the sheet to which the restriction is applied is not protected. That’s because when a user tries to modify it, then Google Sheets shows a warning message.This doesn’t actually restrict the user from making any modifications because the warning message can simply be ignored.
- Restrict who can edit this range
This is the right option for protecting/locking a range of cells or entire sheets. That’s because it allows you to restrict access either for all other users or allow certain users to make edits and restrict every other. This option will be used in the following tutorials. We’ll restrict edits or modifications from all other users by selecting the Only you option. Then, we will allow certain people to make edits by choosing the Custom option.
Now we are familiar with different options in the Range editing permissions menu. This will become handy once we practically use it in the following tutorials.
How to Lock Specific Cells In Google Sheets
In the first tutorial of this article, we’ll see how to protect a certain range of cells in Google Sheets. We have got a lot of things to cover, so let’s start without further ado.
- We’ll be using our Sales Data – January spreadsheet for this tutorial.
- Select the range of cells that you want to protect/lock. Let’s say we select the Purchases column.
- In the top menu, navigate to the Data tab.
- In the Data drop-down, navigate to the Protected sheets and ranges.
- The Protected sheets and ranges menu will appear on the right side of the screen.
- Click on the Add a sheet or range.
- You’ll be redirected to the following menu. As you can see, the Range has already been selected. As for the Description, it is optional. We’ll leave it empty.
- Then click on the Set permissions button.
- Afterward, we’ll be redirected to the Range editing permissions menu. We have already seen these different options. We’ll set it to Only you in this case. Then, we will click the Done button.
- We’re good to go. Now, the Purchases column has been protected. Therefore, it can not be altered or modified by other users. To confirm this, we have tried to edit the Purchases column from another account, and we get this warning as shown.
This tutorial shows how to protect cells from modifications. However, sometimes it is necessary to draw exceptions. You might have a colleague or a friend whom you might wish to grant edit privileges for the protected range as well. Let’s see in the subsequent tutorial how to do this.
How to Lock Cells with Edit Permission
We’ll learn how to grant privileges to certain users over the protected range of cells. This is going to help you, particularly when you’re working in collaboration with a colleague or friend. Let’s see how.
- We’ll repeat Steps 1 to 9 as done in the preceding tutorial until we get to the Range editing permissions menu.
- In this menu, we’ll click on the Restrict who can edit this range drop-down.
- We’ll change it from Only you to Custom.
- As you can see my account is already in the Choose who can edit list. To add a user, we will enter the user’s email address. Also, you can add multiple users by entering their emails separated by a comma.
- Click on the Done button to add the user.
- Once this is done, you can see that the user has been added to the list.
- Click on the Done button and you’re done.
We have covered the scenario where you might need to grant edit privileges even for the protected cells. Next, we will see how to lock/protect an entire sheet.
How to Lock Entire Sheets In Google Sheets
Sometimes, you might need to prevent modifications or edits to the entire sheet. This is often a necessary thing to do when you don’t want any intentional or accidental modifications from other users. Let’s see in these steps how to make the entire sheet protected.
- We’ll switch to the Product Sales – Quarter 1 sheet for this tutorial.
- In the top menu, navigate to Data > Protected sheets and ranges.
- In the Protected sheets and ranges menu, switch to the Sheet tab.
- In the Sheet tab, click on the drop-down to see the list of available sheets.
- We’ll select the Product Sales – Quarter 1 sheet.
- Click on the Set Permission button. You’ll be then redirected to the Range Editing Permissions menu.
- As we have already seen different options for the range restriction in the preceding sections, therefore, we will go with the Only you option. Click on the Done button.
- You can also select the Custom option instead of Only you. We have already seen that in the preceding tutorial. Custom allows you to allow certain users to modify or edit the protected sheet or range of cells.
- Voila!. Now, if other users cannot modify the protected sheet.
Next, we’ll assume a scenario where we need to protect a sheet while keeping a certain range of cells unlocked. In the following tutorial, we’ll show you how to go about this problem.
How to Lock A Sheet Except for Certain Cells
Google Sheets is quite robust and flexible. It allows you to draw exceptions. You might need to protect a sheet except some of its cells. We’ll see how this is done exactly.
- We’ll repeat the Steps from 1 to 5 as done in the preceding tutorial.
- Now, we will check the Except certain cells option. This will enable us to select the range of cells.
- Click on the grid-shaped icon.
- Select the range of cells. We’ll select the Sales column. As we select the cells, the range is automatically updated in the Protected sheets & ranges menu. Click on the OK button afterward.
- Then click on the Set Permissions button.
- You’ll be redirected to the Range editing permissions menu. We have already seen how different options work in this menu. We’ll simply proceed with the default Only you option. Click on the Done button.
- That’s all about it. We hope that you would have learned how to protect sheets except for a certain range of cells.
We have seen how to lock/protect a range of cells or an entire sheet. Now, we will move on to see how we can unlock a protected/locked range of cells or sheets.
How to Unlock a Locked Range of Cells in Google Sheets
This tutorial will teach you how to unlock a protected/locked range of cells or sheets
- In the top menu, navigate to Data > Protected sheets and ranges.
- The Protected sheets & ranges menu will pop up. As you can see, we already have a rule defined. We created this in the preceding tutorial.
- Click on the Product Sales – Quarter 1 except C1:C11.
- Next, the menu for it will open up. Click on the bin icon at the right.
- Google Sheets will ask for confirmation. Click on the Remove button to continue.
- Voila!. This removes the lock/protection. Now anyone can edit the sheet.
We have covered a lot of ground in this article. We have assumed different scenarios and learned about different options related to protection/locking a range of cells or an entire sheet.
That should be all you need. You now know how to protect a range of cells in Google Sheets. Check out our other numerous Google Sheets formulas to create even more complex and useful functions in Google Sheets.