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.
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.
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.
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.
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.
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.
Step 7: Now, choose the radio button corresponding to the option Formulas and hit the OK button.
Step 8: If you look at the Excel sheet now, you can see that all the cells containing a formula are now automatically selected.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
Here to enlighten, and to be enlightened, with the amazing world of tricks, tips, how-tos, and hacks.