Protect formula cells in Google Sheets: an edit test

Protect formula cells in Google Sheets by restricting the formula range while leaving intended inputs editable. Prepare a small rule and a record that separates expected behavior from a test you have actually run.

Last updated: 18 October 2026.

What does protecting formula cells actually solve?

Protect formula cells in Google Sheets when collaborators should enter values without overwriting a calculation. Imagine a small studio using a quantity column and a total column. Staff need to update quantities, but changing the expression that calculates the total should be limited to the people maintaining the tracker.

This is an editing-control exercise, not a confidentiality feature. Google explicitly says sheet protection should not be used as a security measure. Copies and exports remain possible. Do not share a sensitive rate or personal record on the assumption that protecting its cell prevents somebody from learning or taking the information.

The example below uses invented values. It is not Anar's own experiment or a client result. No Sheets interface, permission save, second-account attempt or reopening sequence was executed for this article. All those steps are NOT RUN. The expected outcomes are acceptance criteria for readers to test, not observations to copy into a completed report.

If you are unfamiliar with spreadsheet structure, keep this exercise small: one file, two input cells and one calculated cell. You do not need a script, AI assistant or paid integration. For broader workflow planning, the Sheets automation guide covers a separate job.

The finish line has two parts. The same suitable collaborator should be able to edit the intended input and be prevented from directly editing the protected formula. Blocking everything would not meet that requirement. Neither would displaying a warning that the collaborator can simply confirm and continue past.

Build a calculation that is easy to check manually

In your browser, open sheets.google.com and choose Blank to create a native Google spreadsheet you own. Name it Formula protection practice. In row one, put Quantity in B1, Unit value in C1 and Total in D1. Enter 4 in B2, 6 in C2 and =B2*C2 in D2.

The supplied arithmetic expectation is 24. That is mathematical reasoning, not a result observed in a running spreadsheet. Inspect the displayed value and formula bar in your own file. If D2 contains the number 24 rather than an expression beginning with an equals sign, you have not prepared the intended formula test.

Protection controls may be missing when a Microsoft Office file is open in Office editing mode. Google's help distinguishes that situation from working in a native Sheets file. Starting with a new practice spreadsheet avoids making file conversion another variable in this small test.

When converting an existing workbook, keep its original and check the functions your real workflow needs separately. A successful conversion cannot be assumed from the presence of one menu. Likewise, protecting a mistaken formula would preserve a mistake rather than validate the calculation.

If data originally comes from a document, the table export check concerns that earlier stage. Here you type a few invented values directly so that the source, expression and expected arithmetic remain easy to understand.

Choose a restriction, not a warning you can bypass

Select D2. Open Data → Protect sheets and ranges, then Add a sheet or range. Choose Range and inspect the actual reference. It should identify the D2 cell on the sheet you prepared, not an unrelated tab or a whole input column. Give the rule a descriptive name such as Practice total formula.

Choose Set permissions. The warning option displays a confirmation message but does not prevent editing. For the blocking exercise, select Restrict who can edit this range instead. A warning-only rule and an editor restriction are different operating choices, even if both appear in the same settings area.

Because you own this practice file, Only you provides a simple intended setup. If someone other than an owner creates a rule, that choice does not remove the owner's rights. With Custom, inspect the selected people carefully. The separate test account must not be an allowed editor of the formula range.

1. Check the reference. Expected output: a rule covering D2.
2. Check the mode. Expected output: restricted editors, not warning-only.
3. Check permitted identities. Expected output: a known list that excludes the test account.
4. Save or choose Done, then reopen the rule and compare it with your note.

Google also offers sheet protection with selected cells excepted. That can suit a larger template, but it is unnecessary for this one-cell exercise. Avoid building several overlapping rules before you can explain the first. More rules can make a failed input edit harder to diagnose.

Do not describe a striped display or a saved rule as a completed behavior test. These can help identify settings, but the separate collaborator attempt still needs to happen before you record that it was observed.

Test an editable input and a restricted formula separately

File-level roles and range-level permissions are different. A Viewer cannot edit either the input or the formula. Testing with that role would not demonstrate the required split. You need another account that can edit the file but is excluded from the protected formula range.

In the fictional file, choose Share, enter your suitable second account’s full address, select Editor and confirm. Keep General access Restricted and exclude that account from D2’s allowed editors. Open it in a separate browser profile and inspect the active identity. Do not remain signed in as the owner and call the result a collaborator test. No public link or real customer information is needed.

With that editor account, try changing B2 from 4 to 7. Then attempt to overwrite the formula in D2. The expected distinction is an allowed input edit and a blocked direct formula edit. Check the expression separately. A formula recalculating after an input change is not the same as somebody changing the expression itself.

Write what happened after each attempt. Do not prefill Passed from the expected behavior in this guide. If you do not have a suitable separate account, write Second-account test: NOT RUN. A saved rule is useful preparation, but it is not evidence that the intended restriction was actually observed.

The Sheets edit notification guide deals with learning that another person changed a file. Receiving an email does not prevent a formula edit. This exercise needs no notification setup, message delivery or Apps Script event.

Agree on the test cell and role. Keep account details private.

Explain a mismatch before changing every setting

If the editor can change both cells, inspect the protection mode first. A warning that allows confirmation is not a restriction. Then check the range reference, the selected editors and the identity currently signed in. An owner testing their own file is not evidence about an excluded collaborator.

If the editor cannot change either cell, check the file role. Viewer and Commenter roles do not meet the input-edit requirement. Also inspect whether another rule covers the whole sheet or the intended input. Write which rule applies to which range rather than deleting settings until something happens.

Change one factor at a time and record it. Keep unclear outcomes unresolved.

Hiding a sheet is not an alternative to this restriction. Google explains that editors can unhide sheets. Nor does range protection create a password barrier or prevent all copying of information. Use it to manage editing in a trusted collaboration, not to conceal proprietary formulas from recipients.

Assistance inside a spreadsheet is also a separate matter. The Gemini in Sheets guide concerns analysis support, not proof that another account is unable to edit a protected cell. An assistant's confident statement cannot replace inspection of the actual role, range and attempted behavior.

The same separation applies to formula correctness. An unchanged expression may still be wrong for your business. Keep arithmetic review and edit restriction as different checks in the handover note.

Keep a bounded record and revisit changed ranges

Plan a short practice session around one file, one formula and two edit attempts. Fifteen minutes is a proposed timebox, not measured completion evidence. If account access is unavailable, you can still prepare the file and permission specification while leaving the collaborator observation open.

File name and address:
Owner and test account role:
Input cell / protected formula range:
Restriction mode and allowed editors:
Input before and after / observation:
Formula overwrite attempt / observation:
Saved formula expression:
NOT RUN steps and unresolved checks:
Next action and owner:

Create a note in Windows Notepad via Start, or Mac TextEdit via File > New and Format > Make Plain Text. Fill the template. Save formula-check.txt in Documents using Windows File > Save As or Mac File > Save; avoid overwriting. Close and reopen that note. Record the spreadsheet URL, close its tab and reopen that exact URL under the intended identity. Record only observed outcomes. When adding new rows later, do not assume a rule created for D2 covers every new calculated cell. Review the actual protected reference and test scope again.

Your earlier observation applies to the file, identities and range you recorded. Changing one of those inputs can require a new check. Avoid copying an old Passed label into a new template simply because its layout looks similar.

If the broader use involves AI, the artificial intelligence guide provides context, but no model is required here. The useful output is a small editing agreement supported by honest evidence, not a security guarantee or an invented efficiency result.