How to Prevent Formula from Being Deleted in Excel (3 Easy 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.


Download Practice Book

Download the practice book here.


3 Ways to Prevent Formula from Being Deleted in Excel

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. Lock Cells to Prevent Formula from Being Erased 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.

Lock Cells to Prevent Formula from Being Erased in Excel

  • 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.

Lock Cells to Prevent Formula from Being Erased in Excel

  • 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.

Lock Cells to Prevent Formula from Being Erased in Excel

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

Lock Cells to Prevent Formula from Being Erased in Excel

  • 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.

Lock Cells to Prevent Formula from Being Erased in Excel

  • 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.

Lock Cells to Prevent Formula from Being Erased in Excel

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

Lock Cells to Prevent Formula from Being Erased in Excel

  • 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.

Lock Cells to Prevent Formula from Being Erased in Excel

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

Lock Cells to Prevent Formula from Being Erased in Excel

  • Re-enter your password and click OK.

Lock Cells to Prevent Formula from Being Erased in Excel

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

Lock Cells to Prevent Formula from Being Erased in Excel


2. Add 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 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.

Add Lock Cell Button in Quick Access Toolbar to Prevent Formula from Being Deleted

  • 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.

Add Lock Cell Button in Quick Access Toolbar to Prevent Formula from Being Deleted

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

Add Lock Cell Button in Quick Access Toolbar to Prevent Formula from Being Deleted

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

Add Lock Cell Button in Quick Access Toolbar to Prevent Formula from Being Deleted

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

Add Lock Cell Button in Quick Access Toolbar to Prevent Formula from Being Deleted

  • 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.

Add Lock Cell Button in Quick Access Toolbar to Prevent Formula from Being Deleted

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

Add Lock Cell Button in Quick Access Toolbar to Prevent Formula from Being Deleted

  • 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. Prevent 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.

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.

Mursalin

Mursalin

Hi there! This is Mursalin. I am an Excel and VBA content developer at ExcelDemy. I am always motivated to gather knowledge from different sources and find solutions to problems in easier ways. I am currently working and doing research on Microsoft Excel. Here I will be posting articles related to Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo