The way to lock formulas in Excel and allow users to enter data is achieved by protecting worksheets and cell formatting. 1. First select the area of ??cells that need the user to enter data, right-click to select "Set Cell Format", and unlock the "Lock" check in the "Protect" tab to unlock these cells; 2. Then click "Review" → "Protect Worksheet", set the password and make sure "Selected Unlocked Cell" and "Edit Object" are checked; 3. Common questions include forgetting to unlock the cell first, not correctly selecting allows operations, accidentally locking the entire workbook, and forgetting passwords, pay attention to the order of operation and setting details.

In Excel, this is actually a very common requirement if you want to lock formulas but also allow users to enter data in certain cells. The key to implementation is to protect the worksheet and cell formatting together. Let me explain in detail how to operate it.

1. First unlock the cells that need to enter data

By default, all cells in Excel are "locked", which means that once the worksheet is protected, all cells cannot be edited.
So the first step is to "unlock" the cells you want the user to enter data first.

• Select the area where you want the user to enter data (such as A1:A10)
• Right-click to select "Set Cell Format"
• Switch to the "Protection" tab
• Remove the hook in front of "Lock" → Click OK

In this way, these cells will not be locked by protection, while other cells that have not been moved will remain locked.

2. Protect the worksheet to lock the formula

Now you need to officially enable the worksheet protection function so that the formula can be truly not modified.

• Click "Review" on the menu bar → "Protect Worksheet"
• In the pop-up window, you can set a password (optional). It is recommended to set a password that is convenient for you to remember.
• In the operation that allows this worksheet, at least "Select Unlocked Cell" and "Edit Object" must be checked.
• Click OK to complete the protection

After this, only the cells you unlocked before can be edited, and other places, including formulas, can only be viewed but not modified.

3. Frequently Asked Questions and Precautions

Sometimes when setting it up, it doesn't work. There may be several reasons:

• Forgot to unlock the data input area first: Remember to unlock it first and then protect the worksheet
• No allowed actions are correctly selected: Make sure that "Edit Unlocked Cells" is checked when protecting the worksheet
• Incorrectly locked the entire workbook: If you accidentally protect the entire workbook instead of a single worksheet, it will also affect the effect
• What to do if your password is lost: Excel cannot directly retrieve the password. If you forget, you can only rely on third-party tools or re-made tables

Basically that's it. The whole process is not complicated, but there are several key points that are easy to ignore, especially the order of "unlock first and then protect". As long as you follow the steps step by step, you can successfully achieve the effect of "locking the formula to allow data input".

The above is the detailed content of how to lock formulas in excel but allow data entry. For more information, please follow other related articles on the PHP Chinese website!