How to hide a formula in Microsoft Excel and show only its value

Your Excel worksheet may contain important formulas for calculating many values. Additionally, Excel worksheets may be shared with many people. So anyone with an Excel worksheet can click on the cell that contains the formula, and in the text preview field at the top, they can easily see the formula. This is definitely not recommended due to security and confidentiality concerns. So is there a way to easily hide a formula and only show its value to anyone who has Excel? Well, of course there is, and we're here to talk about it.

In this article, we'll detail how to easily lock and protect formulas in an Excel document so others can't view or edit them. We will set a password to protect the formula cells. If you want a specific person to be able to view and edit a formula, you can simply pass the password to that person so he/she can edit it. So let’s jump right into the article, shall we?

Step 1: In the sample table shown below, if we click on the cell containing the formula, the formula will be visible on the text preview bar at the top. We will learn how to hide formulas when clicking on a cell.

Step 2: First, click the AllSelect button at the intersection of the column label and the row label. This will select the entire worksheet. Alternatively, you can press the CTRL A keys simultaneously to select the entire worksheet.

Step 3: Once the entire worksheet is highlighted, just right-click anywhere on the worksheet, Then click on the "Format Cells" option from the right-click context menu.

Step 4: In the "Set CellFormat" window, click "Protect"Tab. Now, uncheck the checkbox corresponding to the option Locked and click on the OK button at the bottom.

Step 5: If you now click on any cell that contains a formula, you will be able to see a hover message saying This cell contains a formula and is not locked to prevent accidental changes. Don't worry about this news, let's get on with locking in your formulas.

Step 6: Now let’s find all the cells containing formulas and lock them.

To do this, press the CTRL G keys simultaneously to launch the "Go to" window. At the bottom of the window, click the Special button.

Step 7: Now, select the radio button corresponding to option formula, and Click the OK button.

Step 8: If you look at the Excel worksheet now, you can see that all cells containing formulas are now automatically Check .

Step 9: Next, right-click anywhere within the selected range of cells, and then Click the "CellFormat" option from the right-click context menu.

Step 10: When the "Set Cell Style" window opens, click the "# on the far right ##Protect”tab.

Next, make sure that the checkboxes corresponding to options

Locked and Hidden are enabled. Click the OK button when finished.

Step 11: Now, let’s lock your document. To do this, click the Review tab at the top and then click the Protect Sheet button below.

Step 12: In the "Protect Sheet" window, select "Allow this Under the "Access to all users of the worksheet" section, the "Select locked cells" and "Select unlocked cells" options are automatically selected.

You only need to enter the password under the Password to unprotect worksheet field. You can continue even if the password is empty. But this is not recommended as it defeats the whole purpose of locking the document. After entering the password, click the OK button.

NOTE: Remember, you need to write down the password you enter because this password will be needed to unlock your document if you need to.

Step 13: In the subsequent window, confirm the password you entered in the previous step.

After completion, click the OK button to continue.

Step 14: That’s it. If you now click on the cell containing the formula, the formula will not be visible in the text preview field above. Additionally, if you double-click a cell to edit its value, you will receive a warning message stating that you cannot edit a protected worksheet.

Step 15: If at some point in the future you want to unprotect the worksheet and allow others to have formula viewing and editing permissions, You can do this easily too.

To do this, on the top ribbon, click the Review tab again.

Then click the Unprotect Worksheet option.

Step 16: You will be asked to enter the password you previously used to lock the document. Enter password and click the OK button.

Step 17: If you go back to the Excel worksheet, you can see that the cell is now unlocked. You can continue to view and edit them normally.

The above is the detailed content of How to hide a formula in Microsoft Excel and show only its value. For more information, please follow other related articles on the PHP Chinese website!