Simpliguides

Simpliguides › Productivity

How to protect a range in a Google Sheet so others can't edit key cells

Google Sheets can lock a block of cells so the people you share the spreadsheet with can't change them, while the rest of the sheet stays editable. It's there to prevent accidents β€” a formula overwritten, a heading row deleted β€” rather than to hide anything. This guide covers the desktop browser version.

What you'll need: A Google Sheet you own or have edit access to. A desktop or laptop browser β€” Chrome, Firefox, Safari or Edge. The Sheets apps for iPhone and Android can't set up protection.

1

Open the spreadsheet

Go to sheets.google.com, sign in if asked, and open the spreadsheet you want to protect.

2

Select the cells to lock

Click the first cell and drag to the last one. To lock a whole column or row, click its letter or number at the edge of the grid instead.

You don't have to get the selection exactly right here. The next screen shows the range in a box you can edit by hand.
3

Open the protection panel

Click the Data menu, then Protect sheets and ranges. A panel called Protected sheets and ranges opens on the right-hand side of the screen.

Right-clicking the selected cells also works: choose View more cell actions, then Protect range.
4

Add the range

Click Add a sheet or range. The Range tab is selected and the box beneath it shows the cells you chose, in a form like this:

Sheet1!B2:D20

Type a short description in the box above it, such as Price formulas. It's optional, but it's what you'll see in the list later when you have more than one protected range.

5

Open the permissions

Click Set permissions. A dialog called Range editing permissions appears with two choices.

6

Choose who can edit the cells

Select Restrict who can edit this range and leave the dropdown on Only you. Everyone else who has edit access to the spreadsheet is blocked from changing those cells, and nothing else on the sheet is affected.

To let one or two named people edit as well, pick Custom from the dropdown instead and tick their names. The other option, Show a warning when editing this range, is covered under Variations.
7

Save the protection

Click Done. The range now appears in the Protected sheets and ranges panel, and the lock applies straight away β€” there's no separate save.

To see the locked cells at a glance, click the View menu, then Show, then Protected ranges. Protected cells get a striped background. This only changes what you see, not what anyone else does.

Variations

Lock a whole sheet except a few cells

In step 4, click the Sheet tab instead of Range and pick the sheet from the dropdown. Tick Except certain cells and enter the ranges people should still be able to type in β€” the cells where they enter figures, say β€” then continue from step 5. This is the better choice for a form-style sheet where most of it should stay as it is.

Warn people instead of blocking them

In step 6, choose Show a warning when editing this range. Anyone who tries to change the cells sees a message saying the range shouldn't be changed accidentally, with an Edit anyway choice. It stops the slip of the finger without stopping someone who genuinely needs to make a change.

Reuse the permissions from another range

Once you've set one range up with a custom list of people, the dropdown in step 6 gains a Copy permissions from another range entry. Pick the earlier range and the same people are applied, so you don't have to tick names again.

Troubleshooting

Protect sheets and ranges isn't in the Data menu

You have view or comment access rather than edit access, so Sheets hides the option. Ask the file's owner to change your access to Editor. The same applies in the phone apps, where the option doesn't exist at all β€” use a desktop browser.

Someone still changed the protected cells

The spreadsheet's owner can always edit a protected range and remove the protection, whatever you chose. If the person isn't the owner, check the panel from step 3: either the range is set to show a warning rather than restrict editing, or their name is ticked under Custom. Bear in mind that anyone with edit access can also make a copy of the whole file and change the copy freely β€” protection guards against accidents, not against someone determined.

I want to change or remove the protection

Click Data, then Protect sheets and ranges, and click the range in the panel. From there you can edit the range, click Change permissions, or click the bin icon and then Remove to unlock the cells.

Advertisement

Need a calculator rather than a guide? Try Simplicalcs β†’