Protect an Excel Sheet with a Formula? What Actually Works

Formulas & functions · Updated November 21, 2026

No Excel worksheet formula can turn sheet protection on or lock cells; protection is a command, not a formula. To protect formulas, leave formula cells locked, unlock only the cells people should edit, and then choose Review > Protect Sheet.

How worksheet protection works

Excel separates a cell’s protection setting from the act of protecting a worksheet. Cells are marked Locked by default, but that setting has no effect until you protect the sheet. Once protection is active, Excel can prevent changes to locked cells while allowing edits in cells you deliberately unlocked.

This is useful for a template with calculated totals, for example, where users should enter quantities and prices but should not overwrite the formulas that calculate the result. It is also useful for limiting accidental edits to headings or instructions. It does not make a worksheet read-only for every purpose, nor does it encrypt the workbook or provide strong protection for confidential information.

A formula can calculate a value, display a warning, or check whether required entries are present. It cannot change the Locked setting, enable sheet protection, or set a protection password. Those actions are controlled through Excel’s protection features, or through automation such as VBA—not through a normal cell formula.

Prepare the cells you want people to edit

Before protecting a worksheet, decide which cells are inputs and which should remain fixed. In a typical form, input cells are unlocked, while formulas, labels, and instructions stay locked. For example, a budget template might allow edits to monthly expense entries but protect the cells that total each category.

  1. Select the cells users are allowed to change. To select separate ranges, hold Ctrl while selecting each range in Excel for Windows. On a Mac, hold Command while selecting additional ranges.
  2. Open Format Cells. On Windows, press Ctrl+1; on Mac, press Command+1. You can also right-click the selection and choose Format Cells.
  3. Open the Protection tab and clear the Locked check box. Choose OK to apply the change.
  4. Leave formula cells locked. If you want to conceal a formula from the formula bar as well, select those cells, open Format Cells > Protection, and select Hidden. The Hidden setting takes effect only after you protect the worksheet.

Unlocking the input cells first is important because Excel’s default is to mark cells as locked. If you protect the sheet without changing that default, users may find that they cannot enter anything. A good practice is to test the finished sheet with a normal input: confirm that an unlocked cell accepts an edit and a locked formula cell does not.

Protect the worksheet in Excel for Microsoft 365

After preparing the cells, turn on protection in the desktop version of Excel for Microsoft 365 on Windows:

  1. Open the worksheet you want to protect.
  2. Select Review > Protect Sheet.
  3. In the Protect Sheet dialog box, optionally enter a password. A password is not required to enable protection, but without one, a user can remove protection without entering a password.
  4. Review the options under “Allow all users of this worksheet to.” Keep only the actions you want users to be able to perform. For a basic input form, you might allow selecting unlocked cells and leave other actions disabled.
  5. Choose OK. If you entered a password, Excel asks you to confirm it. Enter it again and choose OK.

Try editing both an input cell and a formula cell to verify the result. If you need to make changes to locked cells later, choose Review > Unprotect Sheet and enter the password if Excel requests it. Menu placement and dialog details can vary slightly between Excel updates. Excel for Mac has the corresponding sheet-protection controls on the Review tab; the Format Cells shortcut differs as noted above. Excel for the web has a more limited set of protection controls, so use the desktop app when you need to configure cell locking and formula hiding precisely.

What formulas can and cannot do

A formula can help explain whether a worksheet is ready for use, but that message is not protection. For example, suppose the required entries go in B2:B10 and the check formula is placed in C1. With all nine input cells filled, this formula displays “Ready to protect”; if any are blank, it displays “Complete all fields”:

With required entries in B2:B10 and the status formula in C1, the result is “Ready to protect” when all nine cells are nonblank:

=IF(COUNTA(B2:B10)=9,"Ready to protect","Complete all fields")

The formula only reports the status. It does not protect the worksheet, and a user who can edit the input range can still change those entries. The protection command must still be applied separately. Also, COUNTA counts any nonblank cell, including one containing a formula that returns an empty string, so this check is appropriate only if that behavior matches your layout.

Other worksheet formulas can validate inputs or flag unusual values, but they do not replace protection. Data Validation can restrict ordinary entries through Data > Data Validation, and conditional formatting can highlight values that need attention. Neither feature is a security barrier: for example, pasting data can bypass some data-entry restrictions. Use these features to guide users, and use sheet protection to reduce accidental changes to locked cells.

Choose protection options that fit the worksheet

The Protect Sheet dialog offers permissions for particular actions, such as selecting locked or unlocked cells, formatting cells, inserting rows, sorting, or using AutoFilter. Do not enable every option automatically. Choose only what the worksheet’s workflow requires, because allowing an action can make more of the protected layout changeable.

For a simple form, a practical setup is to unlock the intended input cells, keep formulas locked, and allow users to select unlocked cells. If users need to filter a protected table, enable the relevant filtering permission and set up the filter before protection. Test the exact tasks people need to perform: protection options can affect operations such as sorting when the selected range includes locked cells.

Hidden formulas are a separate convenience. Marking a cell Hidden and protecting the sheet suppresses its formula from the formula bar when that cell is selected; it does not erase the formula or conceal the calculated result shown in the cell. If someone can access and copy the workbook’s contents, worksheet protection should not be treated as a reliable way to keep proprietary formulas secret.

Passwords, workbook protection, and security limits

Protect Sheet applies to a worksheet. Protect Workbook is a different feature: it can restrict changes to workbook structure, such as adding, deleting, moving, or renaming sheets. Neither setting is the same as encrypting a file so that a password is required to open it.

In Excel for Microsoft 365 on Windows, file encryption is available through File > Info > Protect Workbook > Encrypt with Password. Use that for access control when the goal is to require a password before opening the file, and keep the password somewhere safe. A lost password can prevent access to an encrypted workbook. For worksheet protection, also remember that a password is optional; omitting it makes it easier for someone to unprotect the sheet.

Use sheet protection mainly to prevent unintended edits and preserve a form’s structure. If information is sensitive, limit who can access the file and use suitable file-level security rather than relying on a protected sheet. A protected worksheet can still expose its visible data, and worksheet protection is not a substitute for access permissions.

Common problems and fixes

  • Users cannot type anywhere: Check that the intended input cells were unlocked before selecting Review > Protect Sheet. Locked cells remain uneditable while protection is on.
  • A formula can still be overwritten: Confirm that its cell is marked Locked, then protect the worksheet. The Locked check box alone does not prevent edits on an unprotected sheet.
  • The formula is visible in the formula bar: Select the formula cell, choose Format Cells > Protection, select Hidden, and protect the sheet. The formula is not hidden until protection is active.
  • Users can change formatting or sort data: Review the allowed actions in the Protect Sheet dialog. Uncheck permissions that are not needed, then test the workflow.
  • The password is not accepted: Check capitalization and keyboard layout, and make sure you are entering the sheet password rather than a file-opening password. Excel does not display a forgotten protection password for you, so avoid using a password that cannot be recovered from your own records.

Frequently Asked Questions

Can I protect an Excel sheet with an IF formula? No. IF and other worksheet functions return values; they cannot turn on sheet protection or change cell permissions. Use Review > Protect Sheet after unlocking the cells that should remain editable.

How do I lock only formula cells in Excel? Keep formula cells marked Locked, unlock the input cells through Format Cells > Protection, and then protect the worksheet. Because Excel marks cells Locked by default, the key step is unlocking the inputs before protection is enabled.

Can I hide a formula without protecting the sheet? Not with the worksheet’s Hidden cell setting. Select Hidden in Format Cells > Protection and then protect the sheet; otherwise, the formula can still appear in the formula bar.

Does Protect Sheet encrypt or secure my data? No. It mainly controls editing within a worksheet and helps prevent accidental changes. Use file encryption or appropriate access controls if you need to restrict who can open or view sensitive workbook contents.

Can I protect a sheet without a password? Yes. Choose Review > Protect Sheet and leave the password field blank. Protection still limits the actions permitted by the selected options, but a user can remove that protection without knowing a password.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

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

Spotted an error in this guide? Let us know.