How to lock cells in Google Sheets
Learn how to lock cells in Google Sheets using restricted range permissions, then watch SheetsX protect five formula cells while leaving input values and the rest of the workbook unchanged.
SheetsX team · Updated
Technical review: Codex (AI-assisted) · Sources and examples checked on August 28, 2026.
Follow the stepsQuick answer
How do you lock specific cells?
Select your cells, open Data → Protect sheets and ranges, and add a Range rule. Confirm the exact range, choose Set permissions, and select Restrict who can edit this range. Choose Only you or an approved custom editor list, then save. A collaborator who is not permitted to edit that range should be blocked from changing it.
Protection is an editing control, not a privacy boundary or password. People who can access the file may still view or copy its data. Freezing a row or hiding a tab does not lock its cells.
Real Google Sheets demonstrations
Watch the SheetsX workflow
Recorded in Google Sheets with fictional data and a new English Agent conversation for each keyword. Processing waits are shortened; the actions and results are real.
How to Lock Cells in Google Sheets: Protect Formulas | SheetsX
Watch SheetsX protect E2:E6, read back the owner-only rule, and verify totals of 48.00, 24.00, 30.00, 30.00, and 24.00. The demo preserves A1:D6 and the rest of the workbook; it does not include an excluded-editor access test.
Exact English prompt used in this demo
Protect Sheet1!E2:E6 so only the file owner can edit these formulas. Use restricted editing, not warning-only. Keep A1:D6, all formulas and values, sharing settings, and other worksheets unchanged. Verify the saved range and totals. Reply briefly in English; do not display account names or email addresses.
Step by step
Lock a formula range step by step
The manual steps below use a larger Orders sheet: inputs in B2:D100 and calculated totals in E2:E100. The recorded demo uses the smaller Sheet1!E2:E6 range shown in its example table. Start with a copy if you are still deciding who should edit the workbook.
- 1
Choose the exact cells
Select Orders!E2:E100. Check the first and last rows so that the rule covers the formulas without accidentally blocking the input columns.
- 2
Add a range protection
Open Data → Protect sheets and ranges. Click Add a sheet or range, select Range, and enter a useful description such as Calculated order totals.
- 3
Confirm the sheet and range
Check that the sidebar points to Orders!E2:E100. A similarly named tab or an incomplete range can leave the intended formulas editable.
- 4
Set editor permissions
Click Set permissions and choose Restrict who can edit this range. Use Only you or Custom for the approved editors. Warning-only mode is not a lock. The spreadsheet owner retains editing access.
- 5
Save and check the rule
Click Done or Save. Reopen the rule to confirm its range and editor list. View → Show → Protected ranges can display a striped overlay on protected cells.
- 6
Verify both inputs and formulas
In a test copy, have an existing editor who is excluded from the rule confirm that formula cells are blocked and input cells are still editable. Testing only as the owner does not prove that the restriction works for collaborators.
Recorded example: protect five formula cells
The demo starts with fictional stationery orders in Sheet1!A1:E6. SheetsX saves a restricted, owner-only rule on E2:E6 and reads it back with warning-only mode disabled. The formulas and their calculated values remain unchanged.
What was verified
The saved rule covers exactly Sheet1!E2:E6, uses restricted editing rather than a warning, and allows only the owner. A1:D6, formulas, totals, sharing settings, and other worksheets were left unchanged.
What still needs a collaborator test
The recording runs as the owner. It does not show an excluded editor attempting a blocked edit. Verify that separately in a test copy before depending on the rule for a team workflow.
| Item | Quantity | Unit price | Protected formula | Result |
|---|---|---|---|---|
| Notebook | 4 | 12.00 | E2: =B2*C2 | 48.00 |
| Marker | 3 | 8.00 | E3: =B3*C3 | 24.00 |
| Folder | 5 | 6.00 | E4: =B4*C4 | 30.00 |
| Binder | 2 | 15.00 | E5: =B5*C5 | 30.00 |
| Planner | 1 | 24.00 | E6: =B6*C6 | 24.00 |
Protect a sheet except input cells
For a reusable order form, it may be easier to protect the whole sheet and open a small input area. An exception only removes that sheet protection; it does not grant access to the file or override another protection.
Choose Sheet instead of Range
In the protection sidebar, add a rule, choose Sheet, and select the worksheet containing the form.
Set the exceptions
Enable Except certain cells and list the inputs, for example B2:D100. Keep formula columns, headings, and reference tables outside the exceptions.
Save and test the boundaries
Restrict the sheet to the approved maintainers. Check an input cell, a formula cell, and the next row beyond your planned input area with an excluded editor in a test copy.
| Range | Role | Intended access |
|---|---|---|
| A1:E1 | Column headings | Maintainers only |
| B2:D100 | Quantity, unit price, and notes | Input area for file editors |
| E2:E100 | Calculated totals | Maintainers only |
| E101 | Grand total | Maintainers only; include it in the rule |
Lock, warn, freeze, or hide?
These controls solve different problems. Choose one based on whether the goal is editing, navigation, or file access.
| Control | Use it for | What it does not do |
|---|---|---|
| Restricted range | Limit edits to specified maintainers | Hide values or encrypt the data |
| Warning-only range | Remind editors before a change | Prevent an editor from proceeding |
| Frozen row or column | Keep labels visible while scrolling | Restrict any edits |
| Hidden sheet | Reduce visual clutter | Make sheet contents private |
| File sharing | Control who can open or edit the file | Replace a carefully designed range rule |
Why can someone still edit the cells?
Check the actual rule before assuming protection is broken. The owner and approved range editors are expected to retain access.
You selected a warning
Reopen the rule and switch to restricted editing if edits must be blocked. A confirmation message alone is not an access restriction.
The range misses new rows
Review the rule after expanding your table. A rule ending at row 100 does not establish your intended policy for every future row.
The protection menu is missing
Check that you are editing a native Google Sheets file. If the file is still in Office editing mode, use File → Save as Google Sheets first. Viewers and commenters cannot configure protections.
You want values editable but formatting locked
Google Sheets does not offer that combination through sheet protection. Use a clear template and review formatting separately instead of promising an input-only formatting lock.
How to unlock cells or change editors
Open Data → Protect sheets and ranges, select the existing rule, and change its permissions or delete that rule. Review overlapping range and sheet rules if a cell is still blocked. Ask the owner or an authorized editor to make the change if you do not have permission; do not work around someone else's restrictions.
SheetsX workflow
Protect and verify a formula range with SheetsX
Open a new English Agent conversation and give SheetsX the exact range and intended editing policy. In this real demo, the authorized owner asks it to apply the protection and then read back the saved rule and calculated totals.
Workflow prompt
Protect Sheet1!E2:E6 so only the file owner can edit these formulas. Use restricted editing, not warning-only. Keep A1:D6, all formulas and values, sharing settings, and other worksheets unchanged. Verify the saved range and totals. Reply briefly in English; do not display account names or email addresses.
- Confirm the exact sheet and formula range before approving changes.
- Verify restricted editing is enabled, not warning-only mode.
- Read back the saved editor policy and unchanged formula values.
- Verify blocked edits separately with an excluded editor in a test copy.
Only request protection changes you are authorized to make. SheetsX uses the connected account's permissions and does not bypass protections or change file sharing in this demo. Range protection is not a privacy barrier, and the owner retains access.
Add SheetsX to Google SheetsFrequently asked questions
Can I lock only formulas in Google Sheets?
Yes. Protect the exact cells or columns containing formulas and leave the input ranges outside those rules. Google Sheets does not automatically turn every formula into a protected cell just because you protect one formula range.
Can I password-protect individual cells?
No. Google Sheets range protection uses editor permissions, not a cell password. Keep confidential data out of files shared with people who should not see it.
Why can I edit a protected cell?
You may be the file owner, an approved range editor, or using a warning-only rule. Check the rule and test with an excluded editor rather than only with the owner account.
Can I lock a sheet but leave some cells editable?
Yes. Protect the sheet and enable Except certain cells for the input ranges. Collaborators still need file editing access, and other protections can still restrict those cells.
Does locking a cell stop its formula from recalculating?
No. Protection controls who may edit the formula, not whether its result recalculates when referenced input values change.
Does freezing a row lock its cells?
No. Freezing keeps rows or columns visible during scrolling. Use Data → Protect sheets and ranges to restrict edits.
Can I lock cells from a phone?
Use Google Sheets on a computer to configure sheet and range protection. The desktop steps in this guide are not instructions for the mobile apps.