How to Prevent Formula from Being Deleted in Excel (3 Ways)

Sometimes, we need to use formulas to perform different calculations in our dataset. If we share the dataset with someone, there is a chance that the formulas will delete also. So, to overcome this, we need to prevent the formula from being deleted in Excel. Today, we will demonstrate 3 methods. These methods will let you store the formula. Also, you can edit cells that don’t contain any formula. So, without further ado, let’s start the discussion.

To explain the methods, we will use a dataset that contains information about the Sales Amount of some sellers for the first two months. Here, we have used the SUM function to calculate the total sales for each month. We will try to prevent the formulas in Cell C11 & D11 from being deleted.

1. Locking Cells to Prevent Formula from Being Deleted in Excel

In the first method, we will lock cells to prevent the formula from erasing in Excel. The process is easy but lengthy. So, follow the steps below carefully to learn the whole method.

STEPS:

• In the first place, select a cell in your dataset and press Ctrl + A to select all the used cells.

• Secondly, press Ctrl + 1 to open the Format Cells window.
• In the Format Cells window, select Protection and then, deselect the Locked field.
• Then, press OK.

• After that, go to the Home tab and click on ‘Find & Select’. A drop-down menu will occur.
• Select Go To Special from the drop-down menu.

• In the Go To Special window, select Formulas. Make sure Numbers, Text, Logicals & Errors are also selected.
• Click OK to proceed.

• After clicking OK, we can see the selected formula cells. Here, they are Cell C11 and D11. Don’t worry if you see any error signs.

• In the next step, press Ctrl + 1 to open the Format Cells window and select Protection from there.
• This time select Locked and then, click OK to proceed.

• After clicking OK, there will be no error like the picture below.

• Now, you need to protect the sheet.
• To do so, go to the Review tab and select Protect Sheet. It will open a dialog box.

• In the dialog box, enter a password and click OK. Make sure to select the ‘Protect worksheet and contents of locked cells’ field.

• Re-enter your password and click OK.

• Finally, if you try to delete the formula, a message box will pop up.

Read More: How to Protect Formulas Without Protecting Worksheet in Excel

2. Adding a Lock Cell Button in Quick Access Toolbar to Prevent Deleting

Another way to prevent the formula from being deleted is to add a lock cell button in the Quick Access Toolbar (QAT). In the previous method, we used the Format Cells window to lock cells. But here, you will be able to do the same task using the lock cell button. We will use the previous dataset to explain the method. So, without any delay, let’s jump to the steps.

STEPS:

• First of all, click on the Customize Quick Access Toolbar icon and select More Commands. It will open the Excel Options window.

• In the second step, select All Commands in the ‘Choose commands from’ field.
• Then, select Lock Cell and click on Add.
• Click OK to proceed.

• After clicking OK, a Lock Cell icon will appear like the picture below.

• Now, select a cell in the dataset and press Ctrl + A to select all used cells.

• After that, click on the Lock Cell icon to unlock the selected cells.

• In the following step, select the cells that contain the formula. Here, we have selected Cell C11 & D11.
• Click on the Lock Cell icon again to lock Cell C11 & D11.

• After locking Cell C11 & D11, you need to protect the sheet.
• To do so, go to the Review tab and select Protect Sheet.

• In the ‘Protect Sheet’ dialog box, enter a password and click OK. Make sure you select the ‘Protect worksheet and contents of locked cells’ field.

• Again, enter the password to confirm it and click OK.

• In the end, if you try to delete the formula, a message box will pop up like the picture below and you won’t be able to make any changes.

3. Preventing Formula from Being Deleted with Excel VBA

In the last method, we will use VBA to prevent formulas from being deleted in Excel. It is an easy process and saves time. Once again, we will use the same dataset. So, let’s follow the steps below to learn the method.

STEPS:

• Firstly, right-click on the sheet name in the Sheet Bar and select View Code from the Context Menu.

• Instantly, a Code window will appear.

• Now, type the code in the Code window:

Private Sub Worksheet_Change(ByVal Lock_Formula As Range)
If Intersect(Lock_Formula, Range("C11,D11")) Is Nothing Then Exit Sub
On Error GoTo ExitPoint
Application.EnableEvents = False
If Not IsDate(Lock_Formula(1)) Then
    Application.Undo
    MsgBox " You can't delete formulas from this range " _
    , vbCritical, "Microsoft Excel"
End If
ExitPoint:
Application.EnableEvents = True
End Sub

Here, our dataset contains formulas in Cell C11 & D11. So, we have written Intersect(Lock_Formula, Range(“C11,D11”)) inside the code. You need to edit this part before applying the code. For example, if your dataset contains formulas in the range D5:D9, then write Intersect(Lock_Formula, Range(“D5:D9”)). And if the dataset contains formulas in different cells, like in Cell C5, D4, and E6, then write Intersect(Lock_Formula, Range(“C5,D4,E6”)).

• Now, press Ctrl + S to save the code and close the Visual Basic window.
• Finally, if you try to remove formulas from Cell C11 & D11, a message box will occur like the picture below.

Things to Remember

There are certain things you need to remember when you are trying to prevent the formula from deleting in Excel.

• In Method-1 & 2, don’t forget to protect the sheet.
• In Method-3, insert the correct cells inside the code.

Download Practice Book

Download the practice book here.

Conclusion

In this article, we have demonstrated 3 easy ways to Prevent the Formula from Being Deleted in Excel. Here, we have also discussed a method with VBA. I hope these methods will help you to perform your tasks easily. Furthermore, we have also added the practice book at the beginning of the article. You can download it to test your skills. Last of all, if you have any suggestions or queries, feel free to ask in the comment section below.

Related Articles

• How to Protect Formula in Excel but Allow Input
• How to Protect Formulas but Allow Copy in Excel

<< Go Back to Excel Protect Formulas | Excel Formulas | Learn Excel