How to hide formulas in Excel. 2 ways to hide formulas in Excel

By default, in an Excel document, when you click on a cell in the formula bar, the formula that is used in the specified cell automatically appears. Sometimes it may be necessary to hide the formula used from prying eyes. The functionality of Excel makes it easy to do this.

Setting the display of formulas in an Excel table

For the convenience of working with tables and editing the contents of formulas, when you click on a cell, the full view of the formula that is indicated in it appears. It is displayed on the top line near the “F” character. If there is no formula, then the contents of the cell are simply duplicated. This makes it convenient to edit the table, but it is not always necessary for other users to be able to see the formulas used or even have access to certain cells. Excel features allow you to simply hide the display of formulas, and make it completely impossible for any interaction with the specified cells. Let’s consider both options.

Add sheet protection

When this option is enabled cell contents in formula bar stop showing. However, any interaction with formulas in this case will also be prohibited, so to make changes, you will need to deactivate sheet protection. Sheet protection is enabled like this:

1. Select the cells whose formulas you want to hide.
2. Right click on the highlighted area. In the context menu, go to the “Format Cells” item. Instead, you can use the keyboard shortcut “Ctrl+1”.

3. A window with cell format settings will open. Switch to the “Protection” tab.
4. Check the box next to Hide Formulas. If you also need to prohibit editing the contents of the cells, then check the box next to “Protected cell”. Click “OK” to apply the settings and close the window for changing the cell format.

5. Do not deselect cells. Switch to the “Review” tab, which is located in the top menu.
6. In the “Protect” tool group, click on “Protect Sheet”.
7. The sheet protection settings window will open. Think of a password and enter it in the appropriate field. Click “OK” to apply the password.

8. A password confirmation window will appear. Enter it there again and click OK.
9. As a result, the formulas will be successfully hidden. When you select protected rows, the formula entry bar will be empty.

Attention! To make changes to protected cells, you will need to unprotect the worksheet using the password you provided.

If you want other cells to be able to change values ​​and have them automatically taken into account in hidden formulas, do the following:

1. Select the required cells.
2. Right-click on the selection and go to Format Cells.
3. Switch to the “Protection” tab and uncheck the “Cell protection” item. Click “OK” to apply.
4. Now you can change the values ​​in the selected cells. New data will automatically be substituted into the hidden formulas.

Prevent cell selection

This option is used if you need not only to prohibit working with cells and hide the formula, but also to make it impossible to select them. In this case, it will not even work to change the design.

1. Select the desired range of cells. Right click on the highlighted area.
2. Switch to the “Protection” tab. Check if there is a checkmark next to “Protected cell”. If not, then install it.
3. Click “OK” to apply.
4. Switch to the Review tab. There, select the Protect Sheet tool.
5. The protection settings window will open. Uncheck the box next to “Highlight locked cells” and click “OK” to apply the settings.

6. Confirm the password by typing it again in the window that appears.
7. Now you can’t interact with the specified cells at all. This is very convenient if you are sending a document to someone and do not want the recipient to damage something in it.

Important! This option is not recommended if you are sending the document to another user who may need to make changes to it. The fact is that in documents where the cells are tightly interconnected, the recipient may not be able to make any adjustments to it at all.

Conclusion

When hiding formulas in cells in Excel, be prepared for content editing limitations. In the first option, they can be partially bypassed by taking additional steps. The second option implies the impossibility of making any changes to the cells whose formulas you decide to hide.