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

This article illustrates how to hide a formula in Excel but allow input from users. This will enable you to protect the formulas from serious errors or data loss caused by any accidental and unexpected changes by the users. The following picture highlights the purpose of this article. Have a quick look through it to learn how to do that.

Imagine you have a bank interest calculator Excel sheet as shown in the following picture.

• You want to allow users to input data in the range C4:C7. But you don’t want to allow them to make any changes to the formulas in cells C9 to C10.

• Then you can apply either of the following methods to be able to do that.

1. Protecting Excel Worksheet to Hide Formula but Allow Input

You can protect a sheet or workbook to make only specific cells editable by users. Follow the steps below to do that.

📌 Steps

• First, you need to select the range C4:C10 where the formulas are applied. Alternatively, you can select all the formulas in your Excel sheet using the Go To Special command. Press F5 >> Special >> Formulas >> OK to do that.

• Then press CTRL+1 to open the Format Cells dialog box. Next, check the Locked and Hidden boxes. After that hit the OK button.

• Now select the cells which you want to keep editable for the users i.e. you want to allow users to input data in those cells (C4:C7).
• Next press CTRL+1 again and go to the Protection tab. This time keep the Locked and Hidden boxes unchecked. Then hit the OK button.

• After that, select Protect Sheet from the Review tab.

• You can enter a password but it is not compulsory. Check Select locked cells and Select unlocked cells. Then hit the OK button.

• Now if you select the cells containing formulas, you won’t see any formula in the formula box.

• Now try to edit those cells. Then you will see the following error.

• But you can easily input values in the range C4:C7. And this will change the results in cells C9:C10.

Read More: How to Hide Formulas from Other Users in Excel

2. Using VBA Macro to Hide Formula but Allow Input

An alternative way to get the same result as in the earlier method is by using VBA in Excel.

• First, press ALT+F11 to be able to do that. This will open the Microsoft VBA window.
• Then press Insert >> New Module to open a new blank module as shown below.

• After that, copy the following code using the copy button.

Sub HideFormulaButAllowInput()

ActiveSheet.Protect Userinterfaceonly:=True, AllowFiltering:=True

Range("C4:C7").Locked = False

End Sub

• Now paste the copied code on the blank module. Then press F5 to run the code.

• Finally, you will get the same result as in the earlier method.

Things to Remember

• You can unprotect the sheet from the Review tab if you need to make changes to the formulas. But you won’t be able to do that if you forget the password.
• Protection only works on locked cells. Therefore make sure the desired cells are locked before using sheet protection.

Download Practice Workbook

You can download the practice workbook from the download button below.

Conclusion

Now you know 2 ways to hide a formula but allow input in Excel. Please let us know if this article has helped you to solve your problem. Use the comment section below for further queries or suggestions. Stay with us and keep learning.

Related Articles

• How to Hide Formula in Excel without Protecting Sheet
• Hide Formulas and Display Values in Excel
• How to Hide Formulas in Excel Until Data Is Entered

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