How to Lock and Protect Specific Cells in Google Sheets

Collaborating on a shared spreadsheet with team members, clients, or stakeholders speeds up communication, but it also creates risk. A single accidental click can delete a complex INDEX/MATCH formula, overwrite a key financial model, or erase historic tracking data.

To keep shared workbooks functioning smoothly without locking people out entirely, Google Sheets provides built-in Protected Sheets and Ranges tools. This step-by-step guide covers how to lock specific cells, formulas, or entire tabs, grant custom permissions to designated team members, and display edit warnings on critical inputs.

Method 1: How to Lock Specific Cells or Ranges

If you have a sheet where team members need to fill out specific inputs (e.g., Column A and B) while formula calculations in Column C remain untouched, lock only the formula range.

Step 1: Select the Cells to Protect

1. Highlight the specific cell range you want to lock (e.g., C2:C100).
2. Right-click the selected range and choose View more cell actions > Protect range (or select Data > Protect sheets and ranges from the top menu).

Step 2: Set Range Description and Permissions

1. In the Protected sheets & ranges sidebar that opens on the right side of your screen, give your protection rule a clear description (e.g., Locked_Formula_Column_C).
2. Verify that the target range is correct (e.g., 'Data Entry'!C2:C100).
3. Click the green Set permissions button.

Step 3: Choose Permission Restrictions

A pop-up modal will present two main restriction options:

• Show a warning when editing this range: Anyone with edit access can still modify the cells, but Google Sheets displays a warning pop-up asking, "You're attempting to edit a protected cell. Edit anyway?"
• Restrict who can edit this range: Blocks unauthorized users completely. Choose Only you, or select Custom to enter specific email addresses of authorized team members who are allowed to edit those cells.

Click Done to save your protection rule.

Method 2: Lock an Entire Sheet (With Specific Unlocked Exceptions)

When creating master data collection templates or monthly survey forms, it is often faster to lock the entire tab and explicitly unlock a few designated data-entry cells.

1. Open the Protected sheets & ranges sidebar (Data > Protect sheets and ranges).
2. Select the Sheet tab at the top of the sidebar.
3. Choose the target sheet tab from the dropdown menu (e.g., Master_Budget).
4. Check the box that says Except certain cells.
5. Enter the cell range that you want users to be able to fill out (e.g., B5:B20).
6. Click Set permissions and set the rule to Only you or assign custom editor rights.

Now, the entire sheet tab is completely locked down, except for the explicit range B5:B20 where users can type their entries freely.

Permission Types Comparison: Warning vs. Strict Lockout

Important Security & Ownership Rules to Remember

1. Sheet Owners Can Always Edit Everything: Protecting a cell or range prevents Editors from modifying it, but the document Owner can always edit locked cells.
2. Protected Cells Do Not Hide Formulas: Locking a cell prevents users from altering its content, but users with View or Edit access can still view the underlying formula in the formula bar.
3. Viewers Can Copy the File: If a user has "Viewer" or "Commenter" access, they can select File > Make a copy to create their own unlocked duplicate of the spreadsheet. To prevent this, click the Share button, click the gear icon (Settings), and uncheck "Viewers and commenters can see the option to download, print, and copy."

Troubleshooting Common Protection Issues

• "Can't Edit Protected Cell" Error on Script Execution: If you run a Google Apps Script that writes data to a protected range, the user triggering the script must have permission to edit that protected range, or the script will crash.
• Formulas Still Calculate in Locked Cells: Protecting a cell stops users from changing its formula text, but the formula will continue to evaluate and update output values dynamically whenever input cells change.
• Remove Cell Protection: To remove protection rules, open Data > Protect sheets and ranges, click on the existing rule in the list, and click the Trash can icon.