Lock Cells in Google Sheets: Protect Formulas from Edits

To stop editors changing formula cells in Google Sheets, select the cells, choose Data > Protect sheets and ranges, then set who can edit them. If by “lock cells in a formula” you mean keep a reference from shifting when you copy a formula, add dollar signs, such as $A$1. These are two different features: protection controls editing, while dollar signs control how a formula reference changes.

This guide covers both options in Google Sheets in a web browser. You can use them together: protect a formula cell from edits and make its references absolute so copying the formula does not alter the intended input.

What You’ll Need

A Google account with access to the spreadsheet. You need edit access to create or change protected ranges.
A browser and a clear idea of which cells contain formulas, which contain inputs, and who should be allowed to edit each area.
If the sheet is shared, coordinate with its owner or other editors before changing protections. The owner and people granted access may be able to manage protection settings.

Step-by-Step

Identify what you want to lock. Decide whether you need to prevent someone from overwriting a formula, keep a cell reference fixed when copying a formula, or do both. A protected range does not change formula behavior. Dollar signs do not prevent people from editing or deleting a cell.

For example, protect a column of calculated totals if other people should fill in input cells but not replace the calculations. Separately, use an absolute reference if each row’s calculation should keep pointing to the same tax rate, exchange rate, or other fixed input.
Make a copy or note the current setup. Before changing protections in a shared or important sheet, use File > Make a copy if you are allowed to, or record the existing range and permissions. This gives you a way to compare or restore the setup if the wrong cells become protected. If you do not own the file, ask the owner before changing how collaborators can edit it.
Protect formula cells or a range. Click and drag to select the cells you want to protect. To protect a whole column, click its column letter; to protect only part of it, select the exact cells. Then open Data > Protect sheets and ranges. A panel opens on the right. Choose Add a sheet or range if the panel does not already show a new protection rule.

In the panel, confirm the range shown, or enter the range directly, such as C2:C100. Add a description such as Formula cells — totals if that will help you or other editors recognize the rule later. Descriptions are optional, but useful when a spreadsheet has several protected areas.
Choose whether to warn or restrict editing. Select Set permissions. To actually prevent most editors from changing the selected cells, choose Restrict who can edit this range and choose Only you or Custom, then select the permitted editors and save the permissions. The exact choices available depend on your account and the spreadsheet’s ownership and sharing settings.

Show a warning when editing this range is different: it warns an editor but does not stop the edit. Use a warning when accidental changes are the concern and collaborators should retain freedom to edit. Use restricted permissions when the formula needs stronger protection from routine edits.
Check the protection. Return to the sheet and confirm the correct cells are selected in the protection panel. If practical, ask a collaborator who is not on the allowed-editor list to try editing one protected cell. They should see a warning or be prevented from changing it, depending on the option you selected. Do not test by removing your own access or changing an important formula without a recoverable copy.
Lock a reference inside a formula, if needed. A dollar sign fixes the part of a cell reference that follows it. With quantities in B2:B4 and a fixed unit rate of 20 in E1, enter this in C2: =B2*$E$1. The layout assumes B2 contains 5 and E1 contains 20, so the result in C2 is 100. When you fill the formula down, B2 changes to B3 and B4, while $E$1 stays fixed.

Use $E$1 to fix both the column and row, E$1 to fix only the row, and $E1 to fix only the column. For example, if the same example formula is copied one column to the right, a reference written as $E1 stays in column E but its row can change. A reference written as E$1 stays in row 1 but its column can change. These mixed references are useful in copied formulas across a grid.
Copy the formula and verify the results. Use the fill handle at the bottom-right corner of the formula cell, or copy and paste it into the intended cells. Look at a copied formula to confirm that the relative references moved and the dollar-locked references did not. For the example above, if B3 contains 3 and E1 remains 20, the copied formula returns 60. Protect the formula range after the formulas are in place, or adjust the protected range to include newly added formula cells.
Protect a whole sheet while leaving input cells editable, if appropriate. Open Data > Protect sheets and ranges, choose Add a sheet or range, and select Sheet. Choose the sheet, then use Except certain cells to specify input ranges that should remain editable. Set permissions for the protected sheet. Check the exceptions carefully: a mistaken exception can leave formulas editable, while a missing exception can prevent collaborators from entering data.

Google Sheets formulas use the same dollar-sign reference idea as Excel, but protection controls are configured differently. In Excel for Windows, worksheet protection is managed through Review > Protect Sheet; the Google Sheets Data menu steps above do not apply to Excel.

Common Mistakes

Confusing a fixed reference with a protected cell. The dollar signs in $E$1 only tell Sheets how to adjust a reference when a formula is copied. They do not lock E1 against editing. To protect E1 from changes, create a protected range for E1 as well.

Choosing a warning instead of a restriction. The warning option is a reminder, not an access rule. Editors can proceed after seeing it. If your goal is to stop ordinary editors from overwriting formulas, choose restricted editing permissions and confirm who is allowed.

Protecting the wrong range. A range such as C2:C100 does not protect formulas outside those rows or in other columns. A whole-sheet rule may also block collaborators from entering data unless you set the intended exceptions. Read the range displayed in the side panel before saving.

Expecting protection to make a spreadsheet secret or tamper-proof. Protected ranges are editing controls, not a password or a way to hide sensitive information from people who can access the file. Sharing permissions determine who can view the spreadsheet. Keep confidential data out of files shared with people who should not see it.

Tips

Separate inputs from calculations. Put user-entered values in clearly labeled cells and formulas in a distinct area or column. Then protect the formula range while leaving input cells available. Color or label the areas to make the intended workflow obvious, but remember that formatting alone does not protect anything.

Use absolute references only where needed. If every row should refer to its own value in column B, keep that reference relative. Lock just the fixed rate or assumption cell. Overusing dollar signs can make a copied formula point to the same row or column when it should move.

Use descriptive protection names. A note like “Do not edit — monthly totals” is more useful than an unnamed rule when you revisit the sheet. In the protection panel, review existing rules if an editor reports that a cell is unexpectedly blocked.

Plan for new rows. If formulas are added below the protected range, expand the range to cover them. For a sheet that grows regularly, protecting a larger intended area can reduce maintenance, but make sure it does not block input cells people need to use.

Keep a recovery route. Before changing formulas or access rules, make a copy when possible. If a formula gets overwritten, use Edit > Undo promptly or check File > Version history > See version history to review and restore an earlier version. Undo may not be available later or after changes by other collaborators.

Troubleshooting

You cannot open Protect sheets and ranges or change a rule. Check that you have edit access. In a shared file, the owner or another authorized editor may control the existing rule. Ask the owner to grant the needed access or make the protection change; do not assume that being able to view a sheet means you can manage its protections.

Someone can still edit the cell. Reopen Data > Protect sheets and ranges and check whether the rule is set to show a warning rather than restrict editing. Confirm that the edited cell is inside the specified range and that the person is not among the permitted editors. Owners and people with sufficient file-level authority may have broader control than a regular editor.

A collaborator cannot enter data. Inspect the protected range and any whole-sheet rule. The input cell may have been included by mistake, or it may be missing from Except certain cells. Edit the rule to allow the intended input range or give that collaborator permission, then ask them to refresh the sheet and try again.

A copied formula uses the wrong cell. Compare the original and copied formulas. If the reference that should stay fixed has no dollar signs, add them to its column, row, or both. If a reference should move but has dollar signs, remove the unnecessary dollar sign. Recalculate a couple of rows by hand to verify the references before filling the formula farther.

The formula cell is protected, but someone changed the result. Check whether the result depends on editable input cells. A protected formula can recalculate when those inputs change; that is normal. Protect the relevant input too only if it should not be changed, and consider whether the intended users still need to use the sheet.

The formula range stopped being protected after adding rows. Open the protection panel and expand the range to include the new formula cells. Also check whether inserted rows inherited the expected rules. Test the new area using an account that is not permitted to edit it, if you can do so safely.

Frequently Asked Questions

Can I lock only the formula cells and leave the rest editable?
Select just the formula cells, add a protected range, and restrict who can edit that range. Keep input cells outside it. If formulas are scattered, create separate rules for their areas or protect the sheet and define input-cell exceptions.

Does putting a dollar sign before a cell reference protect it?
No. A dollar sign fixes a row or column reference when a formula is copied. It does not restrict editing. Use Data > Protect sheets and ranges to manage who can change cells.

How do I keep a cell reference fixed when I copy a formula?
Put a dollar sign before both the column letter and row number, as in $E$1. If only one part needs to stay fixed, use a mixed reference such as $E1 or E$1.

Can I protect a formula from the spreadsheet owner?
Do not treat range protection as an unchangeable security boundary. The owner and people with sufficient permissions can manage access and protections. If a file must not be altered, limit sharing and retain an independent copy.

Can I password-protect a range in Google Sheets?
Google Sheets protected ranges use editor permissions rather than a separate password for each range. Control who can access the file through its sharing settings, and use protected ranges to limit editing by collaborators.

Will protection stop a formula from updating?
No. A protected formula still recalculates when its referenced inputs change. Protection stops unauthorized edits to the formula cell; absolute references control which cells the formula points to when it is copied.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.