In this tutorial, you'll learn how to lock cells in Google Sheets. You'll also learn sheet protection to ensure that critical data is never removed from your spreadsheets. Let's jump in.
Google Sheets makes collaborating with co-workers on spreadsheets with its easy sharing technology. Unfortunately, when it’s that easy for multiple worker or 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.
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 configuration as Excel does. Google Sheets’ Protected sheets and ranges tool locks a cell range from all editing.
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.”
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
Google Sheets makes collaborating with co-workers on spreadsheets with its easy sharing technology. Unfortunately, when it’s that easy for multiple worker or 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.
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 configuration as Excel does. Google Sheets’ Protected sheets and ranges tool locks a cell range from all editing.
How to Lock a Full Sheet in Google Sheets
f 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.”
How to Lock a Selected Cell Range in Google Sheets
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.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
COMMENTS