How to Lock Cells in Google Sheets

Protecting your work in a shared Google Sheet is one of those essential skills that separates careless collaboration from professional data management. Whether you’re building a team budget tracker, creating a form where only certain cells accept input, or safeguarding formulas that took hours to perfect, locking cells is your first line of defense. Without protection, one accidental keystroke from a colleague can overwrite critical data, break formulas, or mess up your entire spreadsheet layout.

The good news: Google Sheets makes cell protection straightforward. You don’t need technical knowledge or special permissions. In just a few clicks, you can lock down specific ranges, entire sheets, or even allow certain people to edit while keeping everyone else out. This guide walks you through every scenario, from simple single-range locking to complex setups where different team members have different editing rights.

Lock Cells vs. Freeze Rows: What’s the Difference?

Before diving into the how, let’s clear up a common source of confusion. Many people mix up cell locking and row freezing, but they serve completely different purposes.

Locking cells prevents people from editing, deleting, or changing the content in those cells. When you lock a range, you control who can edit it and whether they see a warning or get blocked entirely. It’s about protection and permission control.

Freezing rows or columns (which you can read more about in our guide on how to freeze rows in Google Sheets) keeps certain rows or columns visible while you scroll through the rest of your data. A frozen header row stays at the top of your screen even when you scroll down 500 rows. Freezing is about visibility and navigation, not protection.

You can absolutely use both together: freeze your header row so it stays visible, and lock the cells in that header row so nobody accidentally modifies your column names. But they’re different tools solving different problems.

How to Protect a Range of Cells

The most flexible protection method is range locking, which lets you select any cells you want to protect. This is perfect when you have a form where users can edit data-entry cells but you want to lock the instructions, headers, or formula cells.

Open your Google Sheet and select the range you want to protect. This might be a single cell, a row, a column, or a scattered set of cells. You can select multiple non-adjacent ranges by holding Ctrl (Windows) or Cmd (Mac) and clicking each range.

Once your range is selected, go to the Data menu at the top. Click “Protect sheets and ranges.” A panel opens on the right side of your screen. The “Range” tab should already be highlighted. You’ll see your selected range in the box at the top. Google Sheets automatically gives it a name like “Range1” or “Range2,” but you can change this to something meaningful like “Data Entry Only” or “Budget Formulas.”

In the description field, write a note about why this range is protected and what people should do if they need to edit it. Something like “Protected: Contains formulas. Contact accounting before requesting changes.” This description appears to anyone who tries to edit the locked range, so make it helpful.

Now you choose who can edit this range. By default, it says “Only me.” Click the dropdown to change it. Your options are: only the owner (you), a list of specific people you choose, or everyone except specific people. Select your preference.

If you choose “Specific people,” you’ll add individual email addresses. Type in the email of each person who should be able to edit this range. You can add as many people as needed, and each person will see the same warning message you wrote in the description field.

At the bottom, you’ll see “Show a warning when someone tries to edit” and “Restrict who can edit.” If you choose the warning option, people can still edit the range but they’ll see your message first, asking them to think carefully. If you choose restrict, they simply cannot edit it, period.

Click “Done” and your range is now protected. You’ll see a small lock icon next to the range name in the toolbar, letting you know it’s active.

How to Protect an Entire Sheet

Sometimes you want to lock down everything in a sheet at once, then create exceptions for specific cells. This approach is more efficient than locking dozens of individual ranges.

Go to Data menu, then “Protect sheets and ranges.” This time, click the “Sheet” tab instead of “Range.” You’ll see the current sheet name. Again, you can write a description and choose who can edit. The same warning vs. restrict options apply.

Protecting the entire sheet means every cell is locked by default. If you want certain cells to remain editable, you unlock them after creating the sheet protection. Here’s the workflow: protect the entire sheet with “only me” or “specific people” permissions, then go back to Data > Protect sheets and ranges and add a range exception for the data-entry cells you want to allow editing in. People can edit those exception ranges but nothing else.

This two-step approach is cleaner than locking thirty individual ranges and it scales much better as your sheet grows.

Setting Warning vs. Hard Lock

When you set up cell protection, you face a choice: show a warning or restrict editing. Each option serves a different purpose, and picking the wrong one can frustrate your team.

A warning tells people “this cell is protected, are you sure you want to edit it?” but they can still proceed. It’s a polite nudge. Use warnings when you want to prevent accidental changes but you trust your team to edit if they really need to. Warnings are great for formula cells in a team dashboard where someone might legitimately need to update a calculation.

Restrict editing means users literally cannot edit the cell. Google Sheets blocks the edit and shows your description message. Use restriction when the cell must not change under any circumstances, like a template header, a critical formula, or a header row in a shared data entry form.

For most collaborative sheets, a mix works best: restrict the stuff that must not change, warn on the stuff that probably shouldn’t change but might need to in emergencies.

Allowing Specific Users to Edit

One of the most powerful features of cell locking is the ability to give edit access to specific people. This lets you create shared sheets where different team members have different permissions.

When you protect a range or sheet and select “Specific people,” a text field appears where you paste or type email addresses. Add each person’s email on a new line or separated by commas. Every person you add will be able to edit that range, even though everyone else is locked out.

You can manage these exceptions anytime. Go back to Data > Protect sheets and ranges, click on the protected range or sheet, and you’ll see the list of people. You can remove someone by clicking the X next to their email. Changes take effect immediately, so if you remove someone’s email, they lose editing rights right away.

One important note: the sheet owner (you, if you created it) can always edit everything, even if you didn’t add yourself to the exception list. You cannot lock yourself out of your own sheet.

Locking Cells with Formulas

A common scenario: you build a calculation sheet with formulas in some cells and data-entry cells in others. You want people to enter numbers in column B but you absolutely do not want them touching the formulas in columns C, D, and E.

Here’s the setup. First, protect the entire sheet. Use Data > Protect sheets and ranges, click the Sheet tab, add your description, set permissions to “specific people” or “only me,” and choose “Restrict who can edit.” Click Done.

Now every cell in the sheet is locked. But you want to unlock the data-entry cells in column B. Select cells B2 through B100 (or however far your data goes). Go back to Data > Protect sheets and ranges, click the Range tab, and this time when you set permissions, choose “Only me” but select “Show a warning” instead of “Restrict.” Actually, wait, that’s backwards. Let me clarify.

When the entire sheet is restricted, you create an exception by protecting the data-entry range with “Specific people” and adding the people who should be able to edit it. But here’s the trick: since the sheet is already restricted, the range protection acts as an override. The people you add to the range will be able to edit those cells even though the sheet is locked.

Alternatively, use a simpler workflow: protect the formula cells and leave the data cells unprotected. Select your formula cells (in this example, C2:E100), protect them with “Restrict,” then don’t protect the data-entry cells at all. Users can edit B2:B100 freely but they cannot touch your formulas.

Locking Cells on Mobile

Google Sheets mobile apps (iOS and Android) have limited protection features compared to the web version. You can open and view protected sheets on mobile, but you cannot change who can edit a range or modify protection settings from the app.

If you try to edit a locked cell on mobile, you’ll get the same warning or restriction as on the web. But if you need to adjust who has access or change the protection rules, you have to do it from a desktop browser. Set up all your cell locking on desktop first, then use the mobile app as a read-only or restricted-edit interface.

This limitation is worth knowing about if you’re managing sheets across a team that works on phones and tablets. Always test protection rules on mobile before rolling them out to others.

Removing or Editing Protection

Protection rules aren’t permanent. You can change them anytime, remove people from the exception list, or delete a protection rule entirely.

Go to Data > Protect sheets and ranges. You’ll see a list of all your protected ranges and sheets in the panel on the right. Click any one to edit it. You can change the description, add or remove people from the permission list, or switch between warning and restrict mode.

To delete a protection rule, click on it in the list and then click the trash icon at the bottom of the panel. You’ll be asked to confirm. Once you confirm, that range or sheet is no longer protected.

If you want to see all protected ranges at once, look at the toolbar at the top of the sheet. You’ll see a small lock icon next to the names of protected ranges in the name box dropdown. This gives you a quick overview of what’s locked.

Common Mistakes and How to Avoid Them

Even with clear steps, protection mistakes happen. Here are the ones I see most often and how to sidestep them.

Protecting the wrong range is surprisingly common. You think you selected column C, but you actually selected column D. Always double-check your selection by looking at the cell reference box at the top left before you apply protection. It should show something like “C:C” for the entire column or “C2:C500” for a range.

Forgetting to add yourself as an exception is less common with sheet protection since you’re always the owner, but it happens with range exceptions. If you protect a range and only add other people’s emails, you might accidentally restrict your own access later if you forget that you did this. Rare, but annoying.

Confusing sheet protection with sharing settings causes trouble. Sharing settings control who can view and edit the entire sheet. Cell protection controls who can edit specific cells within a sheet you’ve already shared. You need both configured correctly. You might lock cells but forget to share the sheet with the people who need to edit them. Always share the sheet first, then set up cell locking.

Using warnings when you meant to restrict, or vice versa. Test both settings before deploying to your team. Warnings feel patronizing if overused, and restrictions feel heavy-handed if you apply them to cells that legitimately need occasional editing.

Practical Examples

Three real-world setups show how this works in practice.

Setup One: Shared Expense Tracker. You have a sheet where team members log expenses. Column A is date, column B is category, column C is amount, column D is description. You want everyone to enter their own expenses in rows 5 and below, but you want to lock the header row and lock the formula cells in column E that calculate monthly totals.

Protect the header row (row 1) with “Restrict” and “Only me.” Protect the formula cells in E2:E1000 with “Restrict” and “Only me.” Do not protect the data-entry range A5:D1000, so everyone can edit it freely. Result: your team fills in their expenses, the formulas update automatically, and nobody can accidentally break your headers or calculations.

Setup Two: Team Project Template. You have a sheet that serves as a template for tracking projects. There are instructions in column A, a dropdown list in column B for status, and date fields in columns C and D. You want new users to fill in their project info but not modify the template structure.

Protect the entire sheet with “Restrict” and “Only me.” Then create a range exception for the cells where users enter data (say, B2:D100). For this range, set permissions to “Specific people” and add the email addresses of everyone who should be able to use the template. They can edit those cells, but the instructions and structure stay locked.

Setup Three: Budget Dashboard. You have a dashboard that pulls data from another sheet using VLOOKUP in Google Sheets or IMPORTRANGE. All the formulas are critical, and you want to lock everything except a few input cells where the CFO can adjust budget assumptions.

Protect the entire sheet with “Restrict” and “Only me.” Create a range exception for the assumption cells (say, D2:D10) and add the CFO’s email. The formulas cannot be broken, the dashboard cannot be modified, but the CFO can adjust the inputs that drive the entire calculation.

Combining Cell Locking with Other Tools

Cell locking works best as part of a broader sheet strategy. You might combine it with conditional formatting in Google Sheets to highlight cells that need attention, or with adding checkboxes in Google Sheets for status tracking. You could use filter data in Google Sheets on the unlocked cells to let people see only their own rows.

The most powerful approach locks the formulas and structure, freezes the header rows so they stay visible, and then lets team members freely edit the data cells. This balances protection with usability.

Advanced users sometimes combine locking with IFERROR in Google Sheets to handle errors gracefully when people do enter data in the wrong format. If someone pastes text into a numeric cell, the formula catches it and displays a helpful message instead of showing an error.

Final Thoughts

Cell locking in Google Sheets is a small feature that prevents big problems. It takes seconds to set up but saves hours of recovery time when someone accidentally deletes a critical formula or overwrites your data structure. Start simple with locking just your formula cells, then expand to more complex setups as you grow comfortable with the tool.

The key is testing. Before sharing a protected sheet with your team, open it in an incognito window and log in as someone else to verify that permissions work as intended. You’ll catch mistakes before they frustrate your collaborators.