How to Hide Formulas and Display Only their Values in Microsoft Excel

Your Excel sheet might contain important formulas that you use to calculate many values. Also, an Excel sheet might be shared with many people. So anyone who has the Excel sheet can click on a cell containing a formula and the formula would be easily visible to them on the text preview field at the top. Definitely, this is not recommended at all, considering the security and confidentiality fronts. So is there a way how you can easily hide a formula and display only its value to anyone who has the Excel? Well, of course there is, and we are here to talk all about it.

In this article, we cover in detail how you can easily lock and protect a formula in your Excel document such that it cannot be viewed or edited by anyone else. We will be setting a password to protect the formula cells. If you would like someone specific to be able to view and edit the formula, you can simply pass the password to that person so that he/she can edit. So let’s dive straight into the article, shall we?

Step 1: In the example table shown below, if we click on a cell containing a formula, the formula is visible on the text preview bar at the top. We are going to learn how will hide the formula when a cell is clicked.

 

1 Initial Table Min



 

Step 2: Firstly, at the intersection of column labels and row labels, click on the Select All button. This will select the entire worksheet. Alternatively, you can press the keys CTRL + A at the same time to select the entire sheet.

 

2 Select All Min



 

Step 3: Once the complete sheet is highlighted, simply right click anywhere on the sheet and then click on the Format Cells option from the right click context menu.

 

3 Format Cells Min

 

Step 4: On the Format Cells window, click on the Protection tab. Now, uncheck the checkbox corresponding to the option Locked and hit the OK button at the bottom.

 

4 Unlocked Min

 

Step 5: If you now click on any of the cells containing a formula, you will be able to see the hover message saying This cell contains a formula and is not locked to protect it from being changed inadvertently. Don’t worry about this message, let’s go ahead and lock your formula.

 

5 Unprotected Warning Min

 

Step 6: Let’s now find all the cells containing a formula, to lock them.

For that, press the keys CTRL + G simultaneously to launch the Go To window. At the bottom of the window, click on the Special button.

 

6 Special Min

 

Step 7: Now, choose the radio button corresponding to the option Formulas and hit the OK button.

 

7 Formula Min

 

Step 8: If you look at the Excel sheet now, you can see that all the cells containing a formula are now automatically selected.

 

8 Auto Selected Min

 

Step 9: As next, on the selected range of cells, right click anywhere and then click on the Format Cells option from the right click context menu.

 

9 Format Cells Min

 

Step 10: When the Format Cells window launches open, click on the Protection tab at the very right.

As next, make sure the checkboxes corresponding to the options Locked and Hidden are enabled. Hit the OK button once you are done.

 

10 Locked Min

 

Step 11: Now, let’s lock your document. For that, click on the REVIEW tab at the top and then click on the Protect Sheet button underneath.

 

11 Protect Sheet Min

 

Step 12: On the Protect Sheet window, under the section Allow all users of this worksheet to, the options Select locked cells and Select unlocked cells will be automatically checked.

You just need to enter a password under the Password to unprotect sheet field. You can proceed even with a blank password. But this is not recommended as it defeats the entire purpose of locking the document. Hit the OK button once you type in a password.

Note: Please remember that you need to keep note of the password you enter, as this password is required to unlock your document if you ever need to unlock it.

 

12 Lock Min

 

Step 13: On the window that follows, confirm the password you entered in the previous step.

Once you are all done, hit the OK button to proceed.

 

13 Confirm Pw Min

 

Step 14: That’s it. If you now click on a cell containing a formula, the formula will not be visible on the text preview field above. Also, if you double click on the cell to edit its value, you will get a warning message saying that you cannot edit a protected sheet.

 

14 Warning Min

 

Step 15: Some time in the future, if you would like to unprotect the sheet and allow others to have the formula view and edit access, you can easily do so too.

For that, on the top ribbon, click on the REVIEW tab once again.

Then click on the Unprotect Sheet option.



 

 

 

15 Unprotect Min

 

Step 16: You will be asked to enter the password using which you locked the document earlier. Enter the password and hit the OK button.

 

16 Enter Password Min

 

Step 17: If you go back to your Excel sheet, you can see that the cells are now unlocked. You can continue viewing them and editing them normally.

 

17 Unlocked Min

 

Please tell us in the comments section whether you found the article beneficial. Also, do let us know if you have questions regarding any of the steps.

Thank you!