1

How To Lock a Formula in Google Sheets

Posted by Matthew on September 14, 2018

Google Sheets makes collaborating with coworkers on spreadsheets a snap with its easy sharing technology. Unfortunately, when it’s that easy for multiple people to use the same spreadsheet, it’s also easy for a user to come through and change critical formulas that the spreadsheet relies on, throwing the whole sheet into chaos. Locking formula cells is an excellent way to protect spreadsheet data from being lost or mismanaged by users. We already showed you how to lock Excel formula cells in this Tech Junkie guide, but you can also lock, or protect, formula cells in Google Sheets. Locking formula cells will ensure that none of the spreadsheet users you share a sheet with can edit its functions.

Locking spreadsheet cells in Google Sheets is not entirely the same as in Excel. Google Sheets cell protection does not require any 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 much locking configurability as Excel does. 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. (You can also go through the Tools->Protect sheet menu option.) That will open the Protected sheets and ranges tool as below.

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 button 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, open the Protected Sheets and ranges sidebar. Press the Range button, and then click the Select data range option shown in the snapshot directly below. Left-click the mouse and drag the cursor over the formula cells you need to lock. Press the OK and Set permissions buttons just as you did to protect the whole sheet.

Share The Power

If you want to allow only certain users to edit cells, that’s easily accomplished. After clicking Set permissions, click the drop-down menu and select Custom. Then enter the email addresses of the other Google Sheets users you’re sharing the spreadsheet with in the Add editors text box. Press the Done button 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 have 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 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 the Remove button.

So that’s how you can ensure formulas in Google Sheets spreadsheets don’t get deleted by locking their cells. You can also lock cell ranges in Sheets spreadsheets with scripts. This YouTube video provides a further details for how you can protect specified cell ranges.

One thought on “How To Lock a Formula in Google Sheets”

Tiffany says:
Hi Matthew, I followed the steps above the protect my Google sheets from the documents formulas being altered by anyone but me; However the above steps did not work. When I emailed the document to a co-worker to test they were able to alter the formulas even though a lock icon appears on each sheet that I protected. Are you able to help trouble shoot further as I find using Google docs extremely frustrating compared to the Microsoft versions. Thanks!
Reply

Leave a Reply

Your email address will not be published. Required fields are marked *