How to Hide Formulas in Excel until Data Is Entered (2 Methods)

Method 1 – Hide All Formulas in Excel

We’ll use a data set containing formulas in Cell C11 and Cell C12.

Hide All Formulas in Excel

In the following screenshot, you can see the average in Cell C12.

 calculate average to Hide Formulas in Excel until Data Is Entered

Steps

  • 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.
  • Check Locked and Hidden, then click on OK.

  • Go to the Review tab in the Excel ribbon and click on Protect Sheet.

 Protect the Sheet to Hide Formulas in Excel until Data Is Entered

  • 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 make changes to it.
  • Click on OK.

 Type password to Hide Formulas in Excel until Data Is Entered

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

Reenter password to Hide Formulas in Excel until Data Is Entered

  • If the sheet is protected, the formula of the active cell will not be shown 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.

Hide the total value

  • 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 to the content of the sheet because you protect the worksheet. First, make the protected sheet unprotected to make changes in the cells.


Method 2 – Hide Formulas Only in Excel Keeping Other Cells Editable

We’re going to use a data set containing formulas in Cell C11 and Cell C12. You can see the Total from the screenshot:

How to Hide Formulas Only in Excel Keeping Other Cells Editable

You can see the Average from the screenshot:

calculate the average

Steps

  • Select all the cells in the worksheet in Excel.
  • Click on the Home tab, then click on the Find & Select option.
  • 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 the formula in them.

  • Right-click the cell or range of cells and 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.

locked the cell

  • Select the other cells except the cells containing formulas.

keep the Other Cells Editable

  • Right-click the cell or range of cells and select Format cells or press Ctrl + 1.
  • Once the Format Cells dialog box appears, click on the Protection tab and uncheck the boxes Locked and Hidden, then click on OK.

uncheck locked option

  • Go to the Review tab in the Excel ribbon.
  • Click on Protect Sheet (similar to Method 1).

  • When the Protect Sheet dialog box appears (similar to method 1), type the password to protect the sheet. Once you protect the worksheet, no one except the real user can unprotect the sheet make changes to it.
  • Click OK.

enter password to Hide Formulas Only in Excel Keeping Other Cells Editable

  • When the Confirm Password dialog box appears, re-enter the password and click on OK.

  • You will be able to change the cells data except for formulas cells. We can see that when we change the value of Cell C7, the value of Cell C12 automatically changes.

show the output

Read More: How to Hide Formulas and Display Values in Excel


Download the Practice Workbook


Related Articles


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

Get FREE Advanced Excel Exercises with Solutions!
Saquib Ahmad Shuvo
Saquib Ahmad Shuvo

Saquib Ahmad Bhuiyan, holding a BSc degree in Naval Architecture & Marine Engineering from Bangladesh University of Engineering and Technology, skillfully integrates engineering expertise with a passion for Excel. He provides solutions to complex issues as an Excel & VBA Content Developer for ExcelDemy Forum. He demonstrates an enthusiastic mindset and adeptness in managing critical situations with finesse, showcasing his commitment to excellence. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel, Rhinoceros,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo