How To Lock a Formula in Google Sheets
Google Sheets makes collaborating with coworkers on spreadsheets a snap with its easy sharing options. Unfortunately, when it’s that easy for multiple people to use the same spreadsheet, it’s also easy for a user to intentionally or unintentionally change critical formulas that the spreadsheet relies on, throwing the whole sheet into chaos. The good news is that Google Sheets gives you a lot of control over permissions for users.
Locking formula cells is an excellent way to protect your Google Sheet’s formulas from unauthorized changes. If you’re an Excel user, you might also be interested in this article on How to Lock a Formula in Excel.
Locking formula cells will ensure that none of the spreadsheet users with whom you share Google sheet spreadsheets with can edit its functions.
Locking spreadsheet cells in Google Sheets is not done exactly the same way as it is done in Excel. Google Sheets formula protection does not require a password. Thus, you don’t need to enter a password to unlock cell protection to edit your own spreadsheets.
However, Google Sheets doesn’t give you quite as many locking configuration options as Excel does but Google Sheets feature for locking formulas is more than sufficient for most use cases. And Google Sheets’ Protected sheets and ranges tool locks a cell range from all editing.
Lock a Full Sheet
If you just want other users to be able to view, but not modify, a sheet, the simplest approach is just to lock the whole sheet. First, open the spreadsheet that includes formula cells you need to lock. To protect all the cells within a spreadsheet, click the downward-pointing arrow on the sheet tab next to the name of the sheet at the bottom left of the spreadsheet and select Protect sheet, which will open the Protected sheets and ranges dialogue box as shown in the example below
Alternatively, you can also select the Protect sheet from the Tools pull-down menu. That will open the Protected sheets and ranges dialogue box as shown below.
in the Protected sheets and ranges dialogue box, follow these steps:
- Press the Set Permissions button to open further editing permissions
- Click the Restrict who can edit this range radio button
- Then select Only you from the drop-down menu.
- Press the Done to lock the spreadsheet
That will lock all the sheet’s cells for whomever you share it with. If somebody tries to modify a formula, an error message will open stating, “You are trying to edit a protected cell or object.”
Lock a Selected Cell Range
If you only need to lock one, or more, formula cells in a spreadsheet, follow these instructions:
- Open the Protected Sheets and ranges dialogue box
- Select the Range tab
- then click the Select data range option shown in the screenshot below
- Left-click the mouse and drag the cursor over the formula cells you need to lock
- Click the OK then Set permissions buttons just as you did to protect the whole sheet
Giving Permissions to other Google Sheets users
If you want to allow only certain users to edit cells, that’s easily accomplished:
- Go to the Tools pull-down menu
- Select Protect Sheet
- Click Set permissions
- Select Custom from Restrict who can edit this range
- Then enter the email addresses of the other Google Sheets users with whom you are sharing the spreadsheet within the Add editors dialogue box.
- Press the Done to save the changes.
Copy permissions from another range is also an option you can select on the Restrict who can edit this range drop-down menu.
That option provides a handy shortcut for entering the same permissions in multiple cell ranges. Instead of having to type or paste in the whole list for every range, you can copy the same permissions from another protected range already included in the spreadsheet by selecting the Copy permissions option.
Show a warning when editing this range is another editing-permission option you can select. Selecting that setting applies a notification to the selected cell range that states, “You’re trying to edit part of this sheet that shouldn’t be changed accidentally.”
That notification opens when the other spreadsheet users edit one of the protected cells. However, that option doesn’t really lock a cell as they can still edit the formula – it just warns users to be careful of the edits they make.
You can also exclude some cells from being locked. For example, you might have had a sheet where you want the users to be able to enter data in a few cells, but not to change anything else in the sheet. This is also easy. Lock the entire sheet, but select the Except certain cells option. Select a cell range to keep unlocked. Click Add another range to select multiple cell ranges.
The Protected sheets and ranges dialogue box (sidebar) lists all protected cell ranges as shown in the snapshot directly below. To unlock the cells, select a cell range listed there. Click the Delete range or Sheet protection option, and then press Remove.
So that’s how you can ensure formulas in Google Sheets spreadsheets don’t get deleted or modified by unauthorized users. You might also enjoy this article on How to Get Absolute Value in Google Sheets.
Do you have any tips or tricks for protecting Google Sheets? Please comment below.