To protect cells in Google Sheets, select the range and choose Data, then Protect sheets and ranges. Add a range, verify its coordinates and choose Set permissions. You can show a warning or restrict editing to specific people, depending on your role and the worksheet’s settings.
To lock formula columns while leaving data entry open, protect the calculation range and keep the input columns outside it. For a worksheet that is mostly fixed, protect the sheet and use Except certain cells for the approved input ranges. Test both an allowed edit and a blocked edit before handing the worksheet to a team.
- Use a range rule for one formula column or small block.
- Use sheet protection with exceptions when most of the tab should stay fixed.
- Choose warning or restriction deliberately.
- Use Drive sharing to control who receives the file.
- Check the workbook from the perspective of an ordinary contributor.
Protect a formula column on a computer
Imagine a club ledger with Item in A, Quantity in B, Unit price in C and Line amount in D. Contributors need to enter items and quantities. A formula calculates the line amounts. Select the calculation cells, such as D2:D100, and open Data, then Protect sheets and ranges. Add a range rule and check the sheet name and range shown in the sidebar.
Name the rule if a description will help a maintainer. Choose Set permissions. Select the intended restriction option and the authorised editors. Save it and inspect the sidebar again. Check whether the header or any totals outside the selected range also need protection. A rule on D2:D100 will not protect D101.
Start with the area that should remain editable
Write down the intended contributor actions before locking anything. In the ledger example, they might need to add the item, quantity and unit price, but should leave the calculated amount alone. Mark those input columns with headings that describe their job. If a contributor needs to enter an adjustment, provide an approved input field rather than asking them to overwrite a formula.
Check how much of the column the rule covers
A formula may currently end at row 100. The register might grow to row 101 next week. Decide whether to protect a longer range or extend the rule as data grows. Keep other tables beneath the register in mind. Review the last protected row and the first unprotected row. This is easier to catch in a test copy before the team starts using it.
Back to contents · Next section
Protect a whole sheet and leave specific cells editable
If a worksheet contains a designed form with a few input areas, a sheet rule may be easier to maintain. In the protection sidebar, choose Sheet, select the correct tab and use Except certain cells when the design calls for editable areas. Add the intended ranges, such as B4:B12 and E4:E12. Review them before setting permissions.
Use exceptions for the fields the contributor actually needs
A worksheet might have two input blocks and a total at the bottom. Leave the input blocks open and protect the rest. Check the boundary row on both sides of each exception. A broad exception that includes a formula cell can undo the intended protection. A narrow exception that omits one input can block legitimate work. Verify each field against the task.
Keep exceptions understandable
If several people will maintain the sheet, label the purpose of each exception. A range covering B4:B12 is more understandable when the heading and a short note identify it as the quantities section. If the same workbook has many unrelated forms, separate tabs can make the rule layout easier to review. Do not spread exceptions through dozens of isolated cells unless that is the simplest structure for the actual task.
Check the effect of a copied template
A copy can have different owners or contributors. Review its protected sheets and ranges under its own sharing policy. Test both an allowed input cell and a protected calculation cell. If a template is reused for another term, verify the rule names, the authorised editors and the new input boundaries before circulating it.
Previous section · Back to contents · Next section
Choose between a warning and an edit restriction
A warning asks an editor to reconsider an edit. It does not block that person from continuing. A restriction limits who can edit the protected range, subject to their role and the rules Google Sheets applies. Choose based on the actual workflow. If the team only needs a reminder, a warning can be enough. If a formula should be editable only by a maintainer, use the appropriate restriction.
Test the warning on a harmless cell
Use an authorised test copy. Attempt an edit in a warning range and read the prompt. Confirm that it can be dismissed and that the action can continue. A warning is useful when exceptions are expected and the user is trusted to decide. It is not the right control when a contributor should be unable to edit the field at all.
Test the restriction as an ordinary contributor
The owner’s ability to edit a protected cell does not establish what another editor can do. Have an authorised contributor test one input cell and one protected formula cell in an approved test copy or harmless row. Confirm they can complete the real task without needing to edit the locked area. Restore any test data afterward.
Previous section · Back to contents · Next section
Separate edit protection from access to the workbook
Protected ranges govern editing. Google explicitly cautions against using protection as a security measure. Review Drive sharing and the data actually present in the file. Someone who can open a shared workbook can generally see its visible data, even when a formula column cannot be edited by them. Depending on the account and settings, they may also be able to copy or export information.
A private column belongs in an approved separate source
Suppose a staff register contains an event schedule and personal contact information. The volunteers need the schedule. Protecting the contact column, or hiding it, does not create a separate data-sharing boundary. Prepare and share an approved source containing only the fields the volunteers should receive. Review the whole shared file and its permissions.
An internal worksheet can still need edit protection
Protection remains useful for preserving formulas, headings, validation rules and totals in a file shared with the intended audience. Set sharing to the people who should have access, then protect the editing surfaces that should remain stable. These are two different decisions and both can matter in a team workbook.
Previous section · Back to contents · Next section
Fix a rule that blocks the wrong edit
Open the protection sidebar and inspect all rules affecting the cell. Check the sheet name, range endpoints and any editable exception. A worksheet can have more than one protection rule. Find out which one applies before removing it. Keep unrelated rules that still serve the workbook.
A contributor cannot edit an input cell
Confirm that they have the appropriate file role first. If the sheet is protected, check whether the cell is inside an exception. If there is also a separate range protection, inspect that rule as well. Test the exact cell with the intended contributor after correcting the rule. A view-only sharing role is not fixed by adding an exception to a sheet rule.
A contributor can still edit a calculation
Check whether the rule uses a warning rather than a restriction. Confirm that the calculation is inside the protected range. Review whether the contributor was added to the allowed list. If they edited a copy, inspect that copy’s role and rule separately. Verify the test with the intended account and file.
The protection controls are unavailable
Google documents that some protection features are not available while editing a Microsoft Office file in the Office editing workflow. If appropriate and authorised, work with a native Google Sheets copy and review the resulting layout, formulas and sharing. Check your own editing role too. The correct response depends on the file type and your permissions.
Previous section · Back to contents · Next section
Maintain a protected workbook through new records and revised formulas
Choose one person or group responsible for changes to the calculations. Review new rows and columns when the template grows. If a formula is moved, check whether its new location is protected. If a new input column is added, confirm that ordinary contributors can use it. Open the rule list and verify the current boundaries after a layout change.
Keep a separate reporting copy when necessary
A fixed report can be shared as an approved PDF or values-only copy when the recipient does not need to edit the source. Check what data the output contains and who may receive it. Protection of the original does not determine the access policy of a new file. Verify the accepted output separately.
Practise with a small ledger
Create an authorised test with three input columns and one calculation column. Protect the formula, then try a permitted input and a restricted edit as the intended contributor. Add a row at the edge of the range and verify whether the rule covers it. Change the rule to a warning in the test copy and compare its behaviour. Use the settings that match the real workbook.
Previous section · Back to contents · Next section
Design a tracker with three editing roles
A community project tracks purchases in columns A through H. Volunteers record a date, item, quantity and quoted price. A coordinator enters the approval status. Two maintainers handle the computed cost and the total at the top. Start by writing down the fields each role changes during an ordinary week. Then mark the formula cells and approval fields on a copy. Use a warning for a field any editor may correct, and a restricted range for a field where only a defined role makes changes.
For this layout, protect the cost formula and total from ordinary input, then decide independently who can change the approval field. If the coordinator is already a spreadsheet editor, add the coordinator to the people permitted to edit the approval cells. If a new volunteer joins, check whether the person should be able to see all purchase records before granting access to the file. The protected formulas and approval cells answer only the editing question.
Run a paste test with a collaborator
Give the collaborator one realistic row and ask them to paste it the way they normally would. If a paste includes the protected formula area, have them paste only into the input columns and check the computed value after. If users enter records from a different table, align its input columns with the tracker so they can copy a single contiguous block without including protected output. This can prevent mistakes more reliably than a note explaining which cells to skip.
Ask a maintainer to test an intentional change to one formula in the copy and have an ordinary editor attempt the same action. Check that the approval field behaves differently if it belongs to a different role. Then check a blank input row near the bottom where new records will be added. This last test catches a rule that protects only the initial sample rows.
Previous section · Back to contents · Frequently asked questions
Frequently asked questions
Does protecting a range hide its values?
Protection controls editing. Review file sharing and use a separate approved source when an audience should not receive particular data.
Can I lock a whole sheet except one column?
Yes. Use sheet protection and configure the intended editable exceptions.
Why can I still edit the range?
Check whether you are an allowed editor or whether the rule is a warning. Test the intended role in the intended file.
Why is an input blocked?
Check the file role, sheet exceptions and any overlapping range rules.
Can I protect a formula without preventing data entry?
Protect its output range and keep the intended input cells outside that restriction.
Helpful reading
- Google Docs Editors Help on protecting, hiding and editing sheets
- How to Freeze Rows and Columns in Google Sheets
- How to Create and Share a Filter View in Google Sheets
- How to Use ARRAYFORMULA in Google Sheets
- How to Share a Google Drive File Without Access Problems
Verify the roles and the actual edit path
Check a permitted input, a restricted calculation and the boundaries of the rule before other people rely on the workbook.