How to Lock Cells in Google Sheets (and What It Can't Do)

There's nothing like opening a shared spreadsheet to find that someone has typed over your formulas. If you share a Sheet with a team, a bookkeeper or a client, you need a way to say "look, but leave these cells alone".

Google Sheets does this through protection, not a padlock icon. I've followed Google's help page for protected ranges, including the warnings it gives about what protection can't do.

Protecting a range to lock cells in Google Sheets

On a computer, Google's steps are:

1. Open your spreadsheet in Google Sheets.
2. Go to Data, then Protect sheets and ranges. A panel opens on the right.
3. Click Add a sheet or range, or select an existing protection to edit it.
4. Choose Range, then use the spreadsheet icon to select the cells you want to lock.
5. Click Set permissions and choose how editing should be limited.
6. Click Done (or Save) to finish.

Pick the narrowest range you can. Locking a few formula cells is far easier to live with than locking a whole tab.

Locking a whole sheet but leaving some cells open

If you'd rather lock the lot and open up a few input cells, choose Sheet instead of Range, pick the sheet, and tick "Except certain cells". Then select the cells people are allowed to edit.

This suits the classic setup for small businesses: a booking sheet where the formulas and headings are locked, and your team only fills in the dates and names.

Who can edit your protected cells

When you set permissions, Google gives you two broad choices:

Option	What it does, per Google's help
Show a warning when editing this range	Doesn't block edits. Editors see a message asking them to confirm the change
Only you	Only you can edit, plus the owner if you aren't the owner
Only domain	Only people in your organisation, available for work or school accounts when everyone in the domain can edit the file
Custom	Only the people you select
Copy permissions from another range	Reuses the permissions from another range or sheet

The warning option is gentle. It's good for cells you'd like people to think twice about. For anything that must not change, use Only you or Custom.

What locking cells in Google Sheets can't do

Here's the honest bit, straight from Google. Protection shouldn't be used as a security measure. Anyone with access to the file can still print, copy, paste, or import and export copies of it.

Google also says you can't protect data with a password, and you can't lock cell formatting while also letting users edit input values on the same sheet. So if a spreadsheet holds anything confidential, the answer is to share it with fewer people, not to lock cells and hope.

Using locked cells in shared client templates

Locked cells shine when you hand a template to someone else. If you send a client a planning sheet, you can lock your formulas and headings and leave the input cells open. They fill in what they need, and your maths stays intact.

If you build documents as well as sheets, my walk-through on filling in a Google Docs template correctly shows the same idea of locking down structure and letting people fill in only what matters.

Planning which cells to lock in your Google Sheet

Before you click anything, spend two minutes deciding what really needs protecting. It stops you locking so much that nobody can use the Sheet, which is the most common complaint I hear about shared spreadsheets.

• Lock formulas, because one accidental overwrite can quietly break every total below it.
• Lock headings and labels, so columns keep their meaning as the Sheet grows.
• Lock reference lists, such as a list of products or services that other cells depend on.
• Leave input cells open: dates, names, quantities, notes and anything else you expect people to change.

Once you've done it, test the Sheet as if you were a collaborator. Try typing in a locked cell and in an open one, and check the message or behaviour matches what you intended. If you set the warning option, you should see the confirmation message appear.

Changing or removing protection on a Google Sheets range

1. Go to Data, then Protect sheets and ranges to reopen the panel.
2. Select the protection you want to change.
3. Edit the range or click Change permissions to alter who can edit.
4. Save your changes.

Only people with the right access can do this, so if the panel won't let you in, ask the owner of the Sheet.

For more on setting up shared sheets that stay tidy, see my notes on what to look for in a productivity template.

If you can't lock cells in Google Sheets

1. Check you have edit access. View-only access won't let you set protection.
2. Make sure you opened the Sheet in a computer browser. The steps above are for a computer.
3. Check the permission choice. The warning option still lets people edit.
4. If it still won't work, look at the Docs Editors Help page for protected ranges.

Frequently asked questions

How do I lock cells in Google Sheets?

Go to Data, then Protect sheets and ranges, click Add a sheet or range, choose Range, select the cells and set permissions. Google doesn't use a padlock icon.

Can I lock a sheet but leave some cells editable?

Yes. Choose Sheet and tick Except certain cells, then select the cells people can still edit.

Does locking cells stop people copying my data?

No. Google says protection isn't a security measure, and anyone with access can still print, copy, paste, import or export copies.

Can I put a password on protected cells?

No. Google's help says you can't protect data with a password.

What does the warning option do?

It doesn't block edits. Editors see a message asking them to confirm the change before it goes through.