Protect Formulas in Google Sheets Without Blocking Data Entry

A colleague needs to correct the hours in a work log, and the fee should follow automatically. Leave the hours available for editing and protect the formula that calculates the amount. The contributor can then correct their time without replacing a calculation with a number; a rate change goes to the person maintaining the form.

Give the contributor Editor access to the file, then use Data → Protect sheets and ranges on a computer to reserve the calculated cells. Sharing the whole file as view-only would block the hours entry too. Work on a copy first, and repair any formula already overwritten by a number before protecting its cell.

Let people change the inputs that drive the calculation

A formula cell cannot keep its calculation when someone replaces its contents with a typed number. Give the contributor a separate input cell and have the formula refer to it. This also makes the intended workflow visible: change hours to change the calculated fee.

Consider this illustrative work log. Rates and amounts are in Korean won; hours and rates are entered as numbers, without typing the unit names into the cells.

Row	A: Task	B: Hours	C: KRW/hour	D: Amount, KRW	E: Notes
2	Organizing material	2	15,000	30,000	Input
3	Proofreading	3	20,000	60,000	Input
4	Total	—	—	90,000	—

Enter =B2*C2 in D2, =B3*C3 in D3, and =SUM(D2:D3) in D4. Google’s formula instructions explain entering calculations with an equals sign and referring to other cells.

The contributor changes B2:B3 and E2:E3; the maintainer controls rates and formulas. Changing B2 from 2 to 4 should produce KRW60,000 in D2 and KRW120,000 in D4. Typing 60000 directly into D2 would look correct initially, but the next hours change would no longer update that cell.

If the business needs manual adjustments, give those adjustments their own input and calculation rule. Decide whether a requested change means different hours, a new rate, or an additional fee before opening the calculated amount for editing.

Protect the form, then exempt its four input cells

For this small form, protect the sheet and leave B2:B3 and E2:E3 unprotected. That keeps the task names, rates, formulas, and total under the maintainer’s control.

1. On a computer, open Data → Protect sheets and ranges.
2. Choose Add a sheet or range → Sheet, then select the work-log tab.
3. Enable Except certain cells and specify B2:B3 and E2:E3. Check the tab name as well as the addresses.
4. Select Set permissions → Restrict who can edit this range.
5. Choose Only you if you alone maintain the protected cells, or Custom for selected maintainers, then finish.

These are the sheet exceptions described in Google’s protection guide. Do not add every contributor to the protected area’s allowed-editor list: doing so would let those people change the cells you intended to reserve.

Choose exception boundaries deliberately. Exempting an entire column could also expose a heading or total placed there later. A short exception such as B2:B3 needs reviewing when the form grows. When adding a task row, check its input exception, formula, and inclusion in the total. Protection does not populate missing calculations.

Label editable columns “Hours input” and “Notes input,” and use a consistent fill color if helpful. Those visual cues help colleagues find the right cells; the protection settings determine what they can actually change.

A protected formula range leaves the surrounding cells available

For a flexible worksheet with only a few calculations, protect D2:D4 instead. In the protection sidebar choose Range, specify the cells, and set their editing permissions. Google’s collaboration guide describes choosing a range or an entire sheet through these controls.

Protecting D does not reserve the rates in C. A contributor could change a rate and alter the result without touching a formula. Protect C2:C3 separately if rates also belong to the maintainer, or use the whole-sheet configuration above.

Need	Configuration	What to check
A fixed form with four inputs	Sheet protection with exceptions	Only the intended input cells are exempt.
A few reserved calculations	Restricted formula range	Other important inputs, such as rates, may need protection too.
A reminder before intentional edits	Warning on edit	The user can confirm and continue.

The warning option does not block an edit. If a colleague confirms the message and overwrites a formula, that is consistent with the setting. Use an editor restriction when the colleague must not change the protected range.

A warning can still be useful for an owner who wants a prompt before changing a calculation. Owners retain editing rights, so an owner’s own accidental edits call for a reminder and recoverable versions rather than an expectation of being locked out.

Test with the account that will enter the data

The file owner can still edit protected cells. A non-owner editor who chooses “Only you” also cannot remove the owner’s access. Trying a formula edit solely through the owner’s account therefore does not establish whether contributor access is correctly restricted.

Check the contributor’s email in the sharing dialog and their file role. They need Editor access to fill the input cells. Viewer or Commenter access will not become editable just because the cells are exempt from protection.

Ask the intended contributor to use their own account in a disposable copy containing no live business entries. Have them change B2 from 2 to 4, try changing the formula in D2, and try changing the rate in C2. With the whole-sheet setup, the first action should work while the latter two are restricted.

If B2 is blocked, inspect the exception address and file permission. If D2 can be changed, inspect the protection mode and permitted editors. Check the signed-in account before changing more permissions.

Check the calculation with a second hours change. Moving B2 from 4 to 3 should give KRW45,000 in D2 and KRW105,000 in D4. If someone previously replaced D2 with the number 60000 and then protected it, the first four-hour check could appear correct by coincidence. If D2 still reads KRW60,000 after the next change, have the maintainer inspect the formula bar, repair the calculation using the recovery steps below, and repeat the check. An editable input and a working calculation are separate results to verify.

Also try the paste operation used in the real workflow. A block copied for the hours column should not include rate and formula columns. Read the resulting inputs and formulas in the test copy instead of assuming how a paste spanning protected cells will be handled.

An Excel file may lack the Google Sheets protection controls

A spreadsheet opened in the browser can still be an .xlsx file. Office editing saves changes in the original Office format; protected ranges require conversion to a native Google Sheets file.

Open the Excel file from Drive and choose File → Save as Google Sheets. Google’s conversion instructions confirm that the original Excel file remains. Configure protection in the new Sheets file and use that file’s link for ongoing input.

Recheck the converted formulas and results before handing it over. Google’s Office compatibility notes state that locked and hidden cells in protected Excel workbooks and worksheets become viewable and editable when transferred to Sheets. Do not assume the old Excel protection has become the new access setup.

If you are looking for the setup controls on an iPad, use a computer to configure protection, as Google’s mobile help directs. Then check the exempt cells using the account and device intended for data entry.

Repair an overwritten formula before protecting it

If D2 already contains the number 30000, protection will preserve that number. Select the cell and inspect the formula bar. In this example it should contain =B2*C2, so a change to hours can still affect the amount.

Right-click the affected cell and choose Show edit history. Google’s change-history documentation notes that some changes are absent, including row or column additions and deletions, formatting changes, and changes produced by formulas. An empty history is not proof that the current calculation is correct.

If necessary, open File → Version history → See version history, inspect a known-good version, and use its More menu to make a copy. Compare the relevant formula with the current layout, then repair the necessary cell in the current file. Restoring the entire earlier version can also roll back valid entries made by colleagues afterward.

Check references before reusing an old formula: a column may have moved since that version. After repairing D2, change its hours input, verify the amount and total, and review the intended protection again.

Protection does not freeze input formatting or conceal contents

Google Sheets does not let this protection feature lock a cell’s formatting while allowing users to edit its value. Check the number and date formats of editable input columns when reviewing completed entries, including in any report you distribute.

Protection also does not make formulas or rates secret. Google’s protection documentation explicitly allows for copies and exports, and editors can reveal hidden tabs. Keep information that recipients must not see out of the shared file rather than relying on a protected or hidden area.

For the example, leave contributors a concrete instruction: enter hours in B and notes in E; ask the maintainer for rate changes. Assign the maintainer responsibility for new task rows, their formulas, and their input exceptions. That gives the next person a usable form and a clear route for changes outside it.