What Is Lock Cells in Google Sheets?
Lock cells in Google Sheets allow users to lock cells to stop users from editing them. Thus, you can protect your sheet from any accidental alterations like editing and deletion. It also secures your sheet and protects important data and calculations. Locking has many benefits which can help you control who can edit your document. Let us look at an example. Here, we have employees entering their salary to calculate the bonus. The bonus cell of C3 is locked using Data -> Protect Sheet and Ranges. When an employee enters his salary details, it calculates the bonus amount based on the percentage whose cell is locked.
In the above example, the cell C3 is locked so that the bonus percentage is not changed. This is extremely helpful if multiple team members are working simultaneously on the same sheet.
Table of contents
Key Takeaways
- Lock cells in Google Sheets is an ideal way to protect your spreadsheet formulas from unauthorized editing thereby ensuring that they are well protected.
- To lock a range or sheet or even specific cells, go to the Data tab – > “Protected sheets and ranges” tool. It locks a cell or range from all editing, and you can customize the protection according to your needs.
- Google Sheets locking does not require a password, unlike Excel.
- If you want to allow only viewing permission for your users, you can lock an entire sheet using the above option.
- Click on Change permissions in the side panel to change the editor permissions of lock cells in Google Sheets.
How to Lock Cells in Google Sheets?
Locking specific cells in Google Sheets is very simple and uncomplicated. Here’s how you go about it.
Step 1: The first step involves you opening the required Google sheet, and right-clicking on the cell or multiple cells you want to lock.
Move to “View more cell actions.”
Step 2: Once you right-click, the View more cell actions option is listed at the bottom of the dropdown menu.
Select “Protect range.”
Step 3: Now, hover over the “View more cell actions” to open up another menu. You will get an option called “Protect range.” Choose “Protect range” to give the cells a locked status.
Step 4: In the side panel, add the cell addresses you need to lock. First, we click on cell C3. Here, you get the option in the side panel, as shown.
Step 5: Click on “Set permissions.”. You get a pop-up that has different permissions.
Step 6: Save the changes by clicking on “Done.” You can see, the cell/range displayed in the side panel. To add the other cells/ranges, click on “Add a sheet or range.” You can add each of the cells and save them as shown below.
Alternatively, you can lock the cells the same way by going to the Data tab and choosing “Protect sheets and ranges.”
You can also lock ranges/different sheets similarly. Right now, we are in Sheet2. To lock Sheet1, in the side panel, choose the “Sheet” option.
You can choose any of the sheets you wish to lock from the dropdown.
Examples
We have seen how to lock specific cells, ranges, and sheets in Google sheets. Now, let us look at some interesting examples and go ahead.
Example #1 – Lock a Single Excel formula
Let us look at the basic example of how to lock a cell in Google sheets. First, let us look at how to lock a cell to be used within a formula. We can use $ to lock a particular cell in a row and column. Look at the data below in a sheet.
Below are the radii of some circles and the Pi value.
Step 1: Let us calculate the area of each circle. As we all know, the area of a circle is. Enter the following formula in cell B2.
=E1*A2*A2
Step 2: Press Enter. You get the area of the circle.
Now, to use Autofill, you can drag the formula to cell B6. However, the value of the cell containing Pi will also change. To lock this cell, change the formula as follows in cell B2.
=$E$1*A2*A2
Step 3: Now, drag the formula to cell B6. You will see that the cell has been locked due to the $ sign.
As seen above, the cell has been locked.
Example #2 – Lock Cells in Google Sheets other than Input Cells
When you have some important data in Google Sheets, you should try to protect it from random users and only authorized persons should be allowed to edit them. In such scenarios, you may lock all cells except the input cells where users will enter their data. It means you lock specific cells in Google sheets. Let us take an example of calculating the average marks of some students. Here, we can allow users to have the Edit Ranges feature to unlock selected cells.
Step 1: Here, the student is allowed to enter their marks in cells B1:B3. The formula in B5 is used to calculate the average, and B6 is the result of the student.
Step 2: We aim to lock all cells of this Google sheet, including the cells calculating the average and result, and leave only the input cells (B1:B3) unlocked. Follow the below steps for this.
Go to the Data tab and click on “Protect sheets and ranges.”
Step 3: In the side panel, click on “Add a sheet or range.”
Now, add the name of the sheet you wish to protect under the Sheet tab; in our example it’s Sheet4.
Step 4: You get a small option below called “Except certain cells.” Check this box.
Step 5: Now, enter the range you wish to protect, B1 to B3, in this case. Click on OK.
Step 6: Then, click on “Set Permissions” to set permissions on who can edit those cells. You can enter the email addresses of all the students who can enter their marks under the “Custom” option in the pop-up.
Thus, you can see that this particular sheet has been locked, except for the range specified, and even in this range, only those IDs that are specified can edit the values. Press Done in the side panel.
Example #3 – Lock all formulas in Google Sheets
Now, that we have seen how to lock particular cells, or unlock only input cells in a range, let us look at how to lock all formulas in a Google sheet.
Given below are some formulas that calculate the area of a square, rectangle, and circle, depending on the value chosen.
Step 1: We have a drop down where we have two options “Square” and “Rectangle which can be chosen in Column B.
For this, go to cell B1 and go to the “Insert” tab.
Step 2: In the side panel, enter the desired options. Here, we enter “Square” and “Rectangle.”
Step 3: Now, if we select the option as a triangle, we enter the side. If we select a rectangle, we enter the length and breadth.
Step 4: Now, we have to protect the cells containing formulas B4 and B5. In the Protected Sheets & Ranges side panel, click on “Add a Sheet or Range.”
Step 5: You can add any desired description and click on the option Set Permissions.
Step 6: You get a pop-up where you select the option “Show a warning when editing this range,” as shown below. Click Done.
Step 7: Any user attempting to edit the range will receive a warning message. They can cancel the edit by clicking the Cancel button.
Example #4 – Lock an Excel Cell
As seen in the examples above, we have locked a range of cells/sheet or formulas. Let us say we just want to lock a single cell. The procedure is the same as above. Consider the spreadsheet with another entry “Marla.”
Step 1: Right-click on the cell and choose “View Cell Actions”-> “Protect Range.”
Step 2: In the side panel, click on “Add a sheet or range.”
Step 3: You can see that the desired cell is displayed there. Click on “Set Permissions” and set the desired permissions.
Viewing all the Protected Ranges and Sheets
To view what are the protected ranges/sheets in a Google workbook, you have to go to the Data tab and choose “Protect sheets and ranges.” In the side Panel, choose the option, “Show all protected ranges.”
It will show you the list of all protected sheets and ranges in that workbook.
In case you need to protect multiple Google sheets, it is frustrating to do it one by one. Some third-party tools are available as Add-ons which can help you lock sheets simultaneously.
Important Things To Note
- Whenever we try to protect a sheet or range, we come across the Enter a description field. It is recommended to fill it in to remember why you decided to protect the sheet/range from the changes.
- To protect Google Sheets except for certain cells, check the box in the side panel called “Except certain cells.” You will need to enter those cells or ranges to unprotect them alone.
- As seen in the above example, when locking formulas, if you select “Show a warning when editing this range,” anyone with access to the file can edit this sheet as well. However, when they try to change something, they will get a warning message about editing the protected cells in Google Sheets. They will be asked to Confirm or Cancel. Meanwhile, the one who set the lock will get an email with the actions the users perform in the spreadsheet.
- To check if an entire sheet is protected, look to the bottom of the sheet. If an entire sheet is protected, you’ll see a padlock on a Google sheet conveying it’s protected. It shows that they lock Google Sheets from editing.
Frequently Asked Questions (FAQs)
Locking is very important to protect important data in Google Sheets. It prevents the users from unnecessarily tampering with the data. Follow the steps listed below to lock cells in Google sheets.
• Open the required spreadsheet in Google Sheets.
• Next, click on the Data tab. Select the option, “Protect sheets and ranges.”
• Click on “Add a sheet or range” or click on any existing protection to edit it.
• If you are looking to protect a range, click on Range.
• Here, you can select “Set permissions” or “Change permissions.”
• Choose how you want to limit the editing, whether it is to be done only by you, or to give a warning signal if you wish to customize the editing.
To lock specific cells and give editing permission to select users, follow these steps.
• First, right-click the selected cells.
• Move over to “View more cell actions.” and choose “Protect range.”
• In the side panel that opens, click on “Add a sheet or range.”
• Here, choose the cells you want to lock.
• Select the “Set permissions” option.
• Choose “Custom.”
• In the box below, add the editors’ email addresses.
Now, these select users have editing permissions to those cells.
• To unlock a Google sheet, go to the Data tab and choose “Protect sheets and ranges” in the menu.
• Pick the cells/range whose protection you’d like to remove.
• See all locked Google sheets and cells.
• Choose the range whose protection you want to delete. Click on the “Trash” icon on the right.
• You get the following message. Click on remove and unprotect the range/sheet.
• To lock particular rows or columns in a Google Sheet, do the following.
• Select the rows or columns you wish to protect by clicking on them.
• GO to Data-> Protect sheets and ranges. You will observe that the columns/rows you have selected are listed there.
• Click on set permissions and give the desired permissions.
Download Template
This article must help understand the Lock Cells in Google Sheets, with its formula and examples. We can download the template here to use it instantly.
Recommended Articles
Guide to What Is Lock Cells in Google Sheets. We explain how to use Lock Cells in Google Sheets with examples. You can learn more from the following articles.
Leave a Reply