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.
- First, we will unlock all the cells. For that press Ctrl+A to select the whole worksheet.
- Then, go to the Format Cells window by pressing Ctrl+1.
- Uncheck the Locked option from the Protection tab. Finally, press the OK button.
- 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.
- Select Formulas from the Go To Special window. Then, press OK.
- 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.
- 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.
- 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.
- 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.
- We enter the VBA window. Choose the Module option from the Insert tab.
- 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
- 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.
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.