Protect Formula Cells in a Shared Google Sheet

To protect formula cells in a shared Google Sheet while keeping input cells editable, put your inputs and formulas in separate blocks, then select Data > Protect sheets and ranges > Sheet, check Except certain cells, enter your input range (A2:D8 in this example), and set Restrict who can edit this range > Only you. Add a one-column formula check so you can spot an overwritten formula at a glance—range protection stops accidental edits, but it doesn’t hide your data.

My preferred spreadsheet tool is Google Sheets because a spreadsheet should be shareable, whether to collaborate with a team or communicate results. I also cover the browser steps for Excel for the web below, since many businesses run on Microsoft 365. In either tool, sharing works only when input cells and formula cells have different rules.

The Example: A Shared Commission Tracker

Here’s a made-up April 2026 commission tracker on a tab named Commission_Tracker. Three reps—Elena Vance, Marcus Reed, and Priya Nair—log their orders in columns A to D. Column E calculates an 8% commission. The rate itself lives in a single cell, L2, so nobody ever types 0.08 into a formula.

The headers go in row 1 and the orders in rows 2–8. Enter dates as real dates (for example, =DATE(2026,4,3)) and sales as plain numbers, then format them as dates and currency. Formatting won’t convert text that only looks like a date or a dollar amount, and the formulas below need real numbers.

Why Unprotected Formula Cells Break a Shared Tracker

When anyone can type into column E, three things can go wrong:

1. A typed-over number. Someone types 250 over E4, Priya’s $2,000 order that should earn $160. Her payout jumps from $260 to $350, and total commission goes from $1,120 to $1,210 (+$90, or +8.04%).
2. A “correct” number that goes stale. Someone types 160 into E4. It’s right today, so the total stays at $1,120. Later, D4 changes to $3,000. The commission should become $240 (total $1,200), but E4 still says $160, understating commission by $80 (−6.67%).
3. Edits that cancel out. Someone changes E2 to =ROUND(D2*$L$2, 2)+90 and E3 to =ROUND(D3*$L$2, 2)-90. Every cell is still a formula and the total is still $1,120, but Elena is overpaid by $90 and Marcus is underpaid by $90.

The second and third cases are why checking the total isn’t enough. Protection (Step 2) prevents these edits from collaborators, and the check in Step 3 catches any that come from people who can still edit, including you.

Step 1: Separate Inputs from Formulas

Build and test your formulas before you turn on protection. (In Excel for the web, protection blocks you from entering formulas in locked cells until you pause it.) Keep all inputs together on the left in A2:D8 and everything calculated to the right, following a workflow for separating raw inputs from calculated columns.

In K2, type Commission rate. In L2, enter 8%. Then enter this formula in E2 and fill it down to E8:

=IF(OR(A2="", B2="", NOT(ISNUMBER(C2)), NOT(ISNUMBER(D2)), D2<0), "CHECK INPUTS", ROUND(D2*$L$2, 2))

It returns CHECK INPUTS if a row is missing an order ID or rep, if the date or sales cell holds text like “TBD”, or if sales are negative. Otherwise, it multiplies sales by the rate in L2. The $ signs keep every row pointing at L2 when you fill down, so changing the rate later means editing one cell, not seven formulas.

Step 2: Lock Everything Except the Input Cells

Once the formulas work, lock the sheet so collaborators can edit only A2:D8. This also locks the rate in L2.

In Google Sheets

Per Google’s documentation on protecting sheets and ranges:

1. Select Data > Protect sheets and ranges and click + Add a sheet or range.
2. Select Sheet, choose Commission_Tracker, check Except certain cells, and enter A2:D8.
3. Click Set permissions, select Restrict who can edit this range, choose Only you (or Custom to add specific managers), and click Done.

Don’t choose Show a warning when editing this range. It lets people click past the warning and edit anyway. You and the file owner can still edit every cell; other editors can change only A2:D8.

In Excel for the Web

Per Microsoft’s worksheet protection guide, open Review > Manage Protection, turn on Protect sheet, and add A2:D8 under Unlocked ranges. Excel’s protection applies to everyone, including you: to change a formula, click Review > Pause Protection, make the edit, then click Resume Protection. Other users stay protected while you’re paused. (In desktop Excel, use Format Cells > Protection > Locked plus Review > Protect Sheet.)

Step 3 (Optional): Add a Check That Flags Overwritten Formulas

Protection doesn’t stop you, or anyone you’ve authorized, from overwriting a formula by accident. A one-column check flags it. In F1, type Formula Check. Then enter this in F2 and fill it down to F8:

=IF(NOT(ISFORMULA(E2)), "OVERWRITTEN", IF(E2="CHECK INPUTS", "CHECK INPUTS", IF(E2=ROUND(D2*$L$2, 2), "OK", "EDITED")))

ISFORMULA (Excel version) returns TRUE when E2 holds a formula, so a typed number shows OVERWRITTEN. But ISFORMULA can’t tell a correct formula from a tampered one, so the check also recalculates the expected commission from L2 and shows EDITED if E2 doesn’t match. Keep it one cell per row: if you pass a range to ISFORMULA in Google Sheets, it checks only the first cell.

Next, add a small summary next to the rate:

In L5:

=IF(COUNTIF(F2:F8, "OK")=ROWS(F2:F8), "OK: all formulas intact", "REVIEW: " & (ROWS(F2:F8)-COUNTIF(F2:F8, "OK")) & " row(s) flagged")

Here’s what the check shows for each failure case. Test them on a copy of the file, not the live tracker:

Notice that the total looks fine in the last two cases—only the status cell catches them. One limit: the check uses whatever rate is in L2, so it can’t tell you the rate itself is wrong. That’s why L2 sits inside the locked area.

What Sheet Protection Cannot Do

Keep range protection separate from file sharing and confidentiality:

• Range protection prevents accidental edits, not viewing. Google notes in Protect, hide, and edit sheets that protected sheets and ranges aren’t a security measure, because spreadsheets can be printed, copied, pasted, and exported.
• Google Drive sharing settings control file-level actions. In Drive’s Share > Settings (gear icon), described in Share and manage access to files and folders in Google Drive, owners decide whether editors can change permissions and share, and whether viewers and commenters can download, print, and copy.
• Visible cells can always be read. Even with downloading, printing, and copying turned off, people can read or photograph the screen. Run operational trackers with no additional tool spend in your existing office suite, but keep confidential payroll or margin data in a separate file—the same boundary you apply when deciding whether business data is safe to upload to external tools.

Troubleshooting

Frequently Asked Questions

Why not just compare the total to an expected total?

Because two of the three failure cases leave the total unchanged. A stale typed value matches today and drifts later, and offsetting edits move money between reps without changing the sum. A row-by-row check catches both.

What if my input columns and formula columns alternate across the sheet?

In Google Sheets, check Except certain cells and click Add another range for each input column (for example, A2:A8 and C2:C8). In Excel for the web, add each range under Manage Protection > Unlocked ranges. Grouping inputs in one block, as in this example, is simpler to maintain.

What to Do Next

Locking formula cells and adding a formula check keeps shared calculations intact, while using dropdowns to keep spreadsheet categories consistent (with invalid entries rejected) cuts down on bad typed entries in A2:D8. If totals still disagree across reports, walk through why business numbers don’t match across systems.