How to Protect Formula in Excel but Allow Input (2 Methods)

We protect Excel files or sheets so that other users or the recipients cannot make any changes. But special cases arise sometimes. We may need to share our file with editing permission with no change of the formula cells. Because of the formula changes, we will not get desired output. So, we are going to show how to protect the formula in an Excel sheet but allow input.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


2 Methods to Protect Formula in Excel but Allow Input

We will discuss two methods that describe how to protect formulas in Excel allowing input. One of them is a VBA macro.

We have a dataset of names of employees with their salaries and cost. Now, input the salary and cost and calculate the savings. We can not touch the savings column.

After inserting values in the Salary and Cost column, we get the saving automatically.

We also keep some empty cells. When new people come, we will insert their information and determine savings. Without the formula cells of the Savings column, other columns will remain editable.


1. Protect Formula Cells Only

We can protect the cells with formulas allowing data entry. First, lock the formula cells and then protect the sheet. Follow the below steps for details.

Steps:

  • First, we will unlock all the cells. For that press Ctrl+A to select the whole worksheet.

Protect Cell Formatting But Only Allow Data Entry By Protecting Formula

  • Then, go to the Format Cells window by pressing Ctrl+1.
  • Uncheck the Locked option from the Protection tab. Finally, press the OK button.

Protect Cell Formatting But Only Allow Data Entry By Protecting Formula

  • There is no locked cell on the worksheet now.

  • Press the F5 button and enter the Go To window.
  • Choose the Special button from that window.

Protect Cell Formatting But Only Allow Data Entry By Protecting Formula

  • Select Formulas from the Go To Special window. Then, press OK.

Protect Cell Formatting But Only Allow Data Entry By Protecting Formula

  • All cells containing formulas are marked here.

  • Again, enter the Format Cells window.
  • Now, check the Locked option and then press OK.

Cells containing formulas are locked now.

  • Go to the Review tab.
  • Click the Protect Sheet option from the Protect group.

Protect Cell Formatting But Only Allow Data Entry By Protecting Formula

  • We will get Protect Sheet. Here, will get the option for password protection.
  • And also show a list of allowed options for the user. We check the first two options, then press OK.

Protect Cell Formatting But Only Allow Data Entry By Protecting Formula

  • Our work is now complete. We can input elements in any cell without the formula cells. Like, we input Allisa on Cell B9.

  • But if we want to input in formula cells, we will get a warning. Here, we click on Cell E7 and the warning is showing.


2. Use an Excel VBA Code to Protect Formula Cells and Allow Input in Other Cells

In this section, we will use a VBA code that will protect the formula cells allowing other cells editable.

Steps:

  • Go to the Sheet Name section at the bottom of each sheet.
  • Press the right button of the mouse. Choose View Code from the Context Menu.

Excel VBA to Protect Formula Cells

  • We enter the VBA window. Choose the Module option from the Insert tab.

Excel VBA to Protect Formula Cells

  • This is the VBA module. We will write VBA code here.

  • Now, copy and paste the following VBA code on the module.
Option Explicit
Sub Protect_Formula_Cells()
    Dim pass As String, w_sheet As Worksheet
    Dim f_cells As Range
    pass = "123"
    Set w_sheet = ActiveSheet
    w_sheet.Unprotect pass
    On Error Resume Next
    Set f_cells = w_sheet.Cells.SpecialCells(xlCellTypeFormulas)
    If f_cells Is Nothing Then Exit Sub
    w_sheet.Cells.Locked = False
    f_cells.Locked = True
    w_sheet.Protect pass
End Sub

Excel VBA to Protect Formula Cells

  • After that, press the F5 button to run the code.

We locked the formula cells successfully.

  • We can input on any cells rather than the formula cells. Look, we can input Cell B10.


Conclusion

In this article, we described 2 methods of how to protect formulas in Excel and allow input. We added a VBA code that works very fast the applying fewer steps. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo