Saquib Ahmad Shuvo
In this article, we’ll discuss how to hide formulas in excel until data is entered. In our daily calculations, 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. Let’s follow the complete guide to learn all of this.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article. Here, for unprotected the excel sheet, you can use the password 1055.
2 Methods to Hide Formulas in Excel until Data Is Entered
1. Hide All Formulas in Excel
Once we hide the cells containing formulas, no one won’t be able to see and edit the cells containing formulas.
Here, I am going to use a data set containing formulas in Cell C11 and Cell C12.
In the following screenshot, you can see the average in Cell C12.
To hide the formula in Cell C11 and Cell C12, you can use the following steps.
Steps
- First of all, Right-click the cell or range of cells, then Select Format cells or press Ctrl+1. When the Format Cells dialog box appears, click on the Protection tab and after that check, the boxes tab locked and hidden, then click on OK.
- Next, Go to the Review tab in the excel ribbon, Click on Protect Sheet. This step is very important in hiding the formula because the above formula remains ineffective until you protect the sheet.
- When the Protect Sheet dialog box appears, type the password to protect the sheet. Once you protect the worksheet, no one except the real user can unprotect the sheet and make changes to it and then click on OK.
- When the Confirm Password dialog box appears, re-enter the password, which will help you to prevent an error in the password from locking the spreadsheet forever. Click on OK.
- If the sheet is protected, the formula of the active cell will not be shown up in the formula bar. Thus the formulas in the cells are protected cause neither can be seen or edited. In the following screenshot, we can’t see the formula of Cell C11.
- In the following screenshot, you can’t see the formula of Cell C12.
- Whenever you try to make changes to the protected sheet, you will see the following message.
The user who has no password can click on the cell but can not make changes in the content of the sheet because you protect the worksheet. First, make the protected sheet unprotected to make changes in the cells.
Read More: How to Hide Formula in Excel without Protecting Sheet (2 Methods)
Similar Readings
- How to Hide Formula in Excel Using VBA (4 Methods)
- How to Hide Formulas from Other Users in Excel (2 Ways)
2. How to Hide Formulas Only in Excel Keeping Other Cells Editable
In the previous method, we learn how to protect the entire worksheet. Now, we will learn how to protect the cell containing formulas and make sure that other cells are editable. As a result, the user will be able to edit the other cells’ values.
Now, I am going to use a data set containing formulas in Cell C11 and Cell C12.
Now, you can see the Total from the screenshot:
Here, you can see the Average from the screenshot:
To only hide formulas where other cells are editable, you can use the following steps.
Steps
- Firstly, by selecting all the cells in the worksheet in excel, click on the Home tab, then click on the Find & Select option. after that, Click on the “Go to Special” option. When the Go to Special dialogue box appears, click on the Formulas option which will select all the cells containing formula in them.
- Right-click the cell or range of cells. Select Format cells or press Ctrl+1.Once the Format Cells dialog box appears, click on the Protection tab and check the boxes for locked and hidden, then click on OK.
- Then select the other cells except the cells containing formulas.
- Then again right-click the cell or range of cells. Select Format cells or press Ctrl+1.Once the Format Cells dialog box appears, click on the Protection tab and uncheck the boxes for tab locked and hidden, then click on OK.
- Go to the Review tab in the Excel ribbon. Click on Protect Sheet (similar to method 1). This step is very important in hiding the formula because the above formula remains ineffective until you protect the sheet.
- When the Protect Sheet dialog box appears (similar to method1), type the password to protect the sheet. Once you protect the worksheet, no one except the real user can unprotect the sheet and make changes to it and then click OK.
- When the Confirm Password dialog box appears, re-enter the password, which will help you to prevent an error in the password from locking the spreadsheet, and click on OK.
- Now you will be able to change the cells data except for formulas cells. Therefore, you will have to change Cell C11 and Cell C12 values By editing the other cells. Here, we can see that when we change the value of Cell C7, the value of Cell C12 automatically changed.
Read More: How to Hide Formulas and Display Values in Excel (2 Ways)
Conclusion
That’s the end of today’s session. I strongly believe from now you may hide the formulas in excel until data is entered. If you have any queries or recommendations, please share them in the comments section below.
Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions.
Keep learning new methods and keep growing!
