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 a serious error 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.

How to Hide Formula but Allow Input in Excel


Download Practice Workbook

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


2 Methods to Hide Formula but Allow Input in Excel

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

  • You want to allow users to input data in 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. Hide Formula but Allow Input with Protecting Excel Worksheet

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

Hide Formula but Allow Input with Protecting Excel Worksheet

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

Hide Formula but Allow Input with Protecting Excel Worksheet

  • 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 Formula in Excel without Protecting Sheet (2 Methods)


Similar Readings


2. Hide Formula but Allow Input with Excel VBA

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.

Hide Formula but Allow Input with Excel VBA

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

Read More: How to Hide Formula in Excel Using VBA (4 Methods)


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.

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. You can also visit our ExcelDemy blog to find solutions to more excel related problems. Stay with us and keep learning.


Related Articles

Md. Shamim Reza

Md. Shamim Reza

Hello there! This is Md. Shamim Reza. Working as an Excel & VBA Content Developer at ExcelDemy. We try to find simple & easy solutions to the problems that Excel users face every day. Our goal is to gather knowledge, find innovative solutions through them and make those solutions available for everybody. Stay with us & keep learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo