How to Lock Specific Cells in Google Sheets (Without Ruining Your Whole Spreadsheet)

Last Tuesday at 4:15 PM, right as I was about to close my laptop and grab a coffee, my slack pinged. It was Sarah, our project lead. She was in a full-blown panic because the Q3 client budget tracker had suddenly turned into a sea of ugly #REF! errors.

Someone—I won't point fingers, but his name rhymes with Mark—had accidentally pasted unformatted sales figures directly over a nested SUMIFS formula I spent two hours building. I managed to pull up the version history and restore it, but my heart beat fast for a good ten minutes.

That was the exact moment I realized I couldn't keep leaving whole sheets wide open.

If you collaborate on spreadsheets, you've likely suffered through this exact scenario. You want your team to type in their monthly numbers, but you desperately need them to keep their hands off your math. Thankfully, Google Sheets lets you lock down specific cells while leaving the rest of the canvas completely editable. Here is how you do it, step by step.

Why Protecting Specific Cells Saves Your Sanity

When I first started using Google Sheets for collaborative reporting, I used to think in extremes. I would either leave the file completely unlocked (and pray nobody broke anything) or make the entire file "View Only" (which meant I had to input everyone's data myself). Neither option was great.

Locking specific range selections bridges that gap beautifully. It gives you precise control over your workspace without turning you into a bottleneck.

My Favorite Real-Life Use Cases

• Protecting complicated math: Lock the header rows and summary columns containing formulas (VLOOKUP, INDEX/MATCH, QUERY), while keeping raw data rows open.
• Standardized templates: Lock project milestones and dates so team members only update the "Status" dropdown box.
• Client intake forms: Freeze instructions and labels so clients only fill out designated input boxes.

Step-by-Step: How to Lock Specific Cells in Google Sheets

You don't need any complex scripts or add-ons for this. Everything you need is already built into the native menu. Grab a spreadsheet you want to secure and follow along.

1. Highlight the cells you want to protect. Click and drag your mouse over the specific range. If you want to select non-adjacent cells, hold down Ctrl (on Windows) or Command (on Mac) while clicking.
2. Open the Protection menu. Right-click anywhere on the highlighted cells and select View more cell actions > Protect range. Alternatively, you can click Data in the top menu bar, then choose Protect sheets and ranges.
3. Add a descriptive label. A sidebar will pop up on the right side of your screen. Type a quick description in the top field—something like "Monthly Total Formulas" or "Header Row Do Not Touch". Trust me, future-you will thank present-you when managing multiple ranges later.
4. Set your permissions. Click the green Set permissions button.
5. Choose who can edit. A pop-up window will appear with two main options:
   • Show a warning when editing this range: Anyone can still edit, but Google Sheets will show a soft pop-up asking, "Are you sure you want to edit this cell?"
   • Restrict who can edit this range: Select Only you, or pick Custom to choose specific team members who are allowed to modify those cells.
6. Save your settings. Click Done.

Pro Tip: If you work with a team of cautious people, the "Show a warning" setting is actually amazing. It doesn't hard-lock the cell, but that quick warning prompt makes people pause and double-check before they accidentally overwrite something important.

The Flip Side: Lock the Whole Sheet Except a Few Cells

Sometimes, doing the reverse is much faster. What if you have a massive sheet with 50 columns of sensitive data, and you only want your team to enter information into two specific columns?

Instead of protecting 48 separate columns individually, you can protect the entire sheet and create exceptions.

1. Open the Protect sheets and ranges panel (Data > Protect sheets and ranges).
2. Click on the Sheet tab at the top of the sidebar (instead of Range).
3. Pick the sheet tab you want to lock from the dropdown list.
4. Check the box that says Except certain cells.
5. Select or type the range you want to leave unlocked (for example, C2:D100).
6. Click Set permissions, set it to "Only you", and hit Done.

Now, your teammates can type away in columns C and D, but every other single cell on that sheet is completely safe from accidental edits.

Common Gotchas and How to Avoid Them

Over the years, I've had a few moments where cell protection didn't work quite the way I expected. Here are a couple of things to keep in mind so you don't get caught off guard:

• Editor vs. Viewer Access: Protected ranges only apply to people who have Editor access to the file. If someone is listed as a Viewer, they can't edit anything anyway.
• File Owners always win: If you are the owner of the Google Sheet, you cannot lock yourself out. You will always be able to edit protected ranges, even if you set permissions to "Only You".
• Copy-pasting quirks: If an Editor copies an unlocked cell and pastes it over a locked cell, Google Sheets will stop them. However, duplicating an entire sheet tab will carry over the protection rules to the new sheet tab.

Important Note: Locking a cell prevents people from changing its contents, but it does not hide the formula from view. Anyone with access to the sheet can still click the cell and see your formulas in the formula bar.

Frequently Asked Questions

Can someone with Viewer access edit locked cells if I add them to the permission list?

No. Permission levels work like a funnel. A user must first have general Editor access to the entire Google Sheet file before protected range permissions mean anything to them. A Viewer will always be restricted to just viewing.

Does locking cells stop people from sorting the sheet?

It depends on how they sort. If a user tries to sort the entire sheet using the main grid headers, Google Sheets will block the action if that sort affects locked cells. However, if they use Filter Views (Data > Filter views), they can sort and filter data for themselves without disturbing the locked layout for everyone else.

Can I lock specific cells using the Google Sheets mobile app?

As of right now, you cannot create or modify protected ranges using the iOS or Android Google Sheets app. However, any cell protection rules you set up on your desktop computer will still be strictly enforced if someone tries to edit those cells on mobile.

A Quick Wrap-Up

Setting up protected ranges takes about thirty seconds, but it saves hours of troubleshooting down the road. You don't have to be a tech genius or write complicated scripts—just point, click, set permissions, and get back to your day.

Give it a try on your busiest shared spreadsheet today. I promise your stress levels will drop the next time you see three different colored cursors hovering around your master formulas!