Microsoft Excel Unprotected Formula Error – Fix It Now!

While handling Excel spreadsheets for managing & analyzing data, it is common to encounter errors, which can be frustrating. One such message that users often encounter is – “This cell contains a formula and is not locked to protect it from being changed inadvertently.”. It arises when you try to apply or edit the formulas like in an Excel spreadsheet cell. However, it is important to fix this Microsoft Excel unprotected formula error to ensure the integrity of your data. From causes to solutions, all we have covered in this blog.

Screenshot of the error:

Common Situations to Encounter Unprotected Formula Excel Error

There are mainly two possible situations where you might encounter this error:

• Trying to modify the formula, the error appears with a green triangle.
• When trying to evaluate Excel errors in the box Error Checking.

Why Am I Getting “This Cell Contains a Formula and Is Not Locked” Error?

There are two foremost reasons for the occurrence of MS Excel unprotected formula errors, such as:

1. Incorrect Cell Referencing- If a formula references cells that aren’t accurately protected, it can result in inappropriate calculations & data inconsistencies.
2. Lack of Proper Protection Settings- Sometimes, the protection settings in MS Excel aren’t configured properly, leading to such formula errors.

Also Read: Microsoft Has Blocked Macros from Running Excel!

Fixes to Solve Microsoft Excel Unprotected Formula Error

As you have known the major factors for this Excel error now, it’s time to troubleshoot it by applying the best fixes discussed below:

Fix 1: Lock the Cell

Follow the steps to lock the cells of the worksheets:

• Tap the Error Checking button present next in your Excel spreadsheet cells.
• Now from the opened box of Error Checking, hit the Lock Cell.

By protecting the cells having formulas you can prevent it from being modified and this will avoid many errors also.

Well, cell locking is just the initial step towards fixing this error. Additionally, you need to protect your Excel workbook by setting the password.

To know how this task is to be done read this article:  How To Lock And Unlock Cells, Formulas In Microsoft Excel 2016?

Fix 2: Turning Off the Excel Error Checking Feature to Fix Microsoft Excel Unprotected Formula Error

When an error is found, that cell gets flagged with a small green color triangle. This green color triangle appears in the upper-left corner side of the cell. To remove unprotected formula error in Excel, you need to disable the Excel error-checking feature.

Here is how to turn off this feature:

• If you are a Mac user then tap to the Excel menu > Preferences > Error Checking.
• Or if you are a Windows user then in your Excel application: hit File > Options > Formulas

• Within the formula tab, you will see the Error Checking section.
• In this section, there is an option to Enable background error checking. If it is checked then uncheck it.

• Now move to the below section of Excel checking rules section. Here you will see the following Excel error checking rules:

1. Cells containing formulas that result in an error:
2. Inconsistent calculated column formula in tables
3. Cells containing years represented as 2 digits
4. Numbers formatted as text or preceded by an apostrophe
5. Formulas inconsistent with other formulas in the region
6. Formulas that omit cells in a region
7. Unlocked cells containing formulas
8. Formulas referring to empty cells
9. Data entered in a table is invalid

• Uncheck the Excel checking rule boxes which you don’t want to apply.
• After making such changes, you will see that the unprotected formula error will disappear, and excel stops making any checking for the errors.

Fix 3: Remove Protection And Unhide Formulas In Excel

Another way through which you can remove ‘this cell contains a formula and is not locked to protect it from being changed inadvertently’ error is by unprotecting the hidden or protected formula.

To show the hidden formula again in the Excel formula bar. Or to unprotect the protected formula follow the below steps:

• Go to the Home tab and now from the Cells group tap to the format option.
• From the drop-down menu choose the Unprotect Sheet.

• After that, enter the password that you have assigned at the time of protecting your Excel spreadsheet
• Hit the OK button.

• Or else, you can go to the Review After that, hit the Unprotect Sheet button.

Also Read: Unveiled: Solving “Excel File Locked for Editing” Error

Exclusive FAQs:

What Does Excel Unprotected Formula Mean?

When this formula error occurs, it means the cell holding the formula that is not locked for protection.

How to Unprotect Formulas in Excel?

To unprotect formulas in MS Excel, you have to select the Protection tab >> uncheck the Locked box. After that, click OK.

How to Stop Unprotected Formula Error?

To stop unprotected formula Excel error, you have to follow these steps:

• Choose the Error Checking button just next to the cell.
• Under the opened Error Checking box, choose ‘Lock Cell’.

How Do You Protect Cells So Formula Cannot Be Changed?

To protect cells from formula modifications in Excel, you have to:

• Select the cells you need to protect.
• Go to Protection tab >> check the Locked check box >> click OK to close the popup.

Verdict time

Protecting your Excel formulas is essential for ensuring accurate calculations. By following the steps mentioned in this blog, you can easily fix Microsoft Excel unprotected formula error and ensure a smooth spreadsheet experience. Well, I have tried my best to give the best-suited solutions. So, try out all the above fixes and share your experiences with us through our social media FB and Twitter pages.