No Excel formula can password-protect a worksheet: formulas calculate values, but they cannot set sheet permissions or require a password for editing. In Microsoft 365 for Windows, use Review > Protect Sheet instead; unlock cells people should edit first, then apply protection and optionally set a password.
Why a formula cannot protect a worksheet
A formula runs inside a cell to return a result. It can calculate a total, check whether an entry meets a condition, or display a warning, but it cannot change Excel’s protection settings. Typing a password into a cell, or writing a formula that compares a typed value with a password, does not create a reliable access restriction. Someone who can inspect the sheet may be able to see the formula or change the surrounding workbook.
This distinction matters because a formula can give the appearance of a password prompt without actually stopping someone from editing the worksheet. Data Validation, conditional formatting, and formulas that display messages can guide ordinary users, but they are not substitutes for Excel’s built-in protection features. If the goal is to prevent accidental changes to formulas while letting people enter information, worksheet protection is the appropriate tool.
What Protect Sheet does
Worksheet protection controls which actions are allowed on a particular sheet. You can leave cells locked so users cannot change them while the sheet is protected, unlock selected cells so users can enter data, and choose whether to permit actions such as sorting or formatting. A password can be required to turn protection off.
Excel cells are marked as locked by default, but that setting has no effect until you protect the sheet. This is why a useful setup normally has two parts: mark the cells that should remain editable as unlocked, then protect the worksheet. The password is optional; without one, protection can still prevent casual or accidental edits, but anyone can turn it off without entering a password.
Protection applies to the worksheet, not automatically to every sheet in the workbook. If a workbook has several tabs, review and protect each sheet that needs its own restrictions.
Protect a sheet in Excel for Microsoft 365 on Windows
To protect a worksheet, open it and use these steps:
- Choose which cells should remain editable. If users should only view the sheet, you can skip the unlocking step.
- Open the Review tab and select Protect Sheet.
- In the dialog, enter a password if you want one. Leaving the password box blank protects the sheet without a password.
- Review the list of permitted actions. For example, decide whether users may select locked cells, select unlocked cells, format cells, or sort data.
- Select OK. If you entered a password, Excel asks you to enter it again to confirm.
After protection is on, Excel blocks changes that are not allowed by the selected settings. To make structural or formula changes later, use Review > Unprotect Sheet and enter the password if Excel requests it. The exact available options can depend on the workbook and the Excel version.
Let users edit input cells but protect formulas
For a form, budget template, or calculation sheet, unlock the intended input cells before protecting the sheet. Select those cells, open Format Cells with Ctrl+1 on Windows, choose the Protection tab, clear the Locked checkbox, and select OK. Then turn on protection using Review > Protect Sheet. The cells that remain locked cannot be edited while protection is active, subject to the permissions you selected.
For example, suppose a small order sheet has quantity in B2, unit price in C2, and an extended price calculated in D2. With quantity in B2 and unit price in C2, enter this formula in D2:
=B2*C2
If B2 contains 3 and C2 contains 12, D2 returns 36. Unlock B2 and C2 for data entry, leave D2 locked, and protect the sheet. Users can change the quantity or price, while the calculation cell is protected from ordinary edits. The cell references and calculation still work normally; protection only restricts editing.
For a more convenient form, you can also consider the permitted-selection options in the Protect Sheet dialog. Allowing users to select unlocked cells, while disallowing selection of locked cells, can make it easier to move through input fields without clicking into formula cells.
Hide formulas, and understand the limitation
Locking a formula cell prevents users from replacing its formula while the sheet is protected. If you also want the formula not to appear in the formula bar when the cell is selected, select the formula cells, open Format Cells, choose Protection, and select Hidden. Then protect the sheet. The Hidden setting does not take effect on its own; it works only while worksheet protection is active.
Hiding is not encryption. It is intended to keep formulas out of ordinary view, not to make them impossible to discover or to protect confidential intellectual property. A user who can remove protection may be able to inspect the formulas, and users with access to the file may find other ways to examine its contents. Do not put secrets, credentials, or sensitive personal information in a worksheet on the assumption that hiding formulas secures them.
What worksheet protection does not secure
Microsoft describes worksheet protection as a way to prevent changes to locked cells, not as a security feature for confidential data. It can help prevent accidental edits or make a template harder to alter through normal use, but it should not be treated as strong protection against a determined person. A sheet password is not the same as encrypting the workbook file.
If the important requirement is that a person must not see the workbook’s contents without permission, use file-level encryption with a strong password rather than relying on Protect Sheet. On Windows, Excel for Microsoft 365 provides this under File > Info > Protect Workbook > Encrypt with Password. Encryption asks for the password to open the file; sheet protection instead governs editing after the workbook is open. Keep a secure record of an encryption password, because losing it can prevent access to the file.
For shared workbooks, also consider where the file is stored and who has access to it. File-sharing permissions in a service such as OneDrive or SharePoint can control who can open or edit a shared file, while sheet protection controls selected actions inside a worksheet. These controls serve different purposes and may be used together.
Worksheet protection versus workbook protection
Excel has different protection options for different jobs. Protect Sheet applies to a worksheet and restricts actions within that sheet. Protect Workbook, when used to protect workbook structure, can restrict actions such as adding, deleting, moving, or renaming sheets. Neither option is the same as encrypting the file so a password is needed to open it.
Choose the option that matches the problem you are trying to prevent:
- To keep formulas from being overwritten while allowing data entry, unlock the input cells and use Protect Sheet.
- To discourage users from changing the workbook’s sheet tabs, use workbook-structure protection.
- To require a password before the file can be opened, use file encryption.
- To share a file with only particular people, set appropriate access permissions where the file is stored.
These controls are not interchangeable. For example, protecting the workbook structure does not lock formula cells, and protecting a worksheet does not prevent someone from opening the file.
Common problems and how to fix them
If a supposedly locked cell can still be edited, check that the worksheet is actually protected. The Locked checkbox alone does not restrict changes. Also confirm that the cell was not unlocked, or that the dialog did not allow the action you are trying to prevent.
If users cannot type into intended input cells, select those cells, clear Locked in Format Cells > Protection, and then protect the sheet again. If the sheet is already protected, unprotect it first; Excel generally requires protection to be off before you can change the cell’s locked status.
If formulas still appear in the formula bar, verify that Hidden was selected for those cells before protecting the sheet. Hidden does not conceal formulas while the sheet is unprotected. If the goal is to keep formulas confidential, remember that this setting is not a security boundary.
If you forget a worksheet-protection password, Excel does not provide a normal password-recovery command that reveals it. Keep passwords in an approved password manager or another secure location, and retain an unprotected backup when appropriate. Do not assume that a password used for sheet protection is recoverable just because the workbook itself can still be opened.
Excel on Mac and Excel for the web
In Excel for Microsoft 365 on Mac, worksheet protection is also managed from the Review tab with Protect Sheet. To adjust cell locking, open Format Cells and use its Protection options; the Mac keyboard shortcut for Format Cells is Command+1. Dialog wording and the placement of options can differ slightly from Windows, but the basic process—unlock input cells, then protect the sheet—is the same.
Excel for the web has protection controls too, but the available options and interface can differ from the desktop app. In the web version, look on the Review tab for Manage Protection or the relevant sheet-protection controls. If an option you need is unavailable, open the workbook in the desktop app and configure protection there. When collaborating, confirm the behavior in the version your intended users will use.
Frequently Asked Questions
Can I use an IF formula to ask for a password before editing?
No. An IF formula can display a message or calculate a different result based on a cell’s value, but it cannot enforce worksheet permissions. Use Protect Sheet for editing restrictions.
Does Protect Sheet encrypt or hide the whole workbook?
No. It controls permitted edits on a worksheet after the file is open. Use file encryption if a password should be required to open the workbook.
Can people edit unlocked cells after I protect the sheet?
Yes. Clear Locked for the cells users should edit before turning on protection, then allow selection of unlocked cells in the protection options. Other actions depend on the permissions you choose.
How do I protect only formula cells?
Leave formula cells locked, unlock the input cells, and then protect the worksheet. Because cells are locked by default, the main task is usually selecting the input cells and clearing Locked before protection is applied.
Can I recover a forgotten sheet-protection password?
Excel has no standard feature that displays or recovers a forgotten sheet-protection password. Keep a secure record of passwords and an appropriate backup so you are not dependent on memory alone.
Related
- Excel Data Table Reference Format: A Practical Guide
- Excel Data Table Relationships and How to Create Them
- Excel Data Table Cycling Through Results? Fixes Explained
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Goal Seek Formula Alternatives That WorkExcel does not have a worksheet function called GOALSEEK; Goal Seek is a What-If Analysis command. The formula…
- Excel Slicer to Filter Data: How to Set Up and Use ItTo filter data with an Excel slicer, select a table cell and choose Table Design > Insert Slicer,…
- Excel Named Range in a Formula: How to Use It CorrectlyIn Excel, use a named range in a formula by typing its name where you would normally put…
- Excel Date Comparison: Formulas, Rules, and FixesWith dates in A2 and B2, compare them using =A2<B2: Excel returns TRUE if A2 is earlier than…
- Power Query Custom Column IF Statement: ExamplesTo write an IF statement in a Power Query custom column, use M’s if … then … else…
- Excel Copy Data Validation to Other Cells the Right WayTo copy only a cell’s data validation in Excel for Microsoft 365 on Windows, copy the cell, select…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.