Hiding formulas in Excel

When working with Excel spreadsheets, for sure, many users may have noticed that if a cell contains a formula, then in a special formula bar (to the right of the button “Fx”) we will see it.

Quite often there is a need to hide formulas on a worksheet. This may be due to the fact that the user, for example, does not want to show them to unauthorized persons. Let’s see how this can be done in Excel.

Method 1. Turn on sheet protection

The result of the implementation of this method is to hide the contents of the cells in the formula bar and prohibit their editing, which fully corresponds to the task.

1. First we need to select the cells whose contents we want to hide. Then right-click on the selected range and the context menu that opens, stops at the line “Cell Format”. Also, instead of using the menu, you can press the key combination Ctrl + 1 (after the desired area of ​​cells has been selected).
2. Switch to tab “Protection” in the format window that opens. Here, check the box next to the option “Hide Formulas”. If our goal is not to protect cells from changes, the corresponding checkbox can be unchecked. However, in most cases, this function is more important than hiding formulas, so in our case, we will also leave it. Click when ready OK.
3. Now in the main program window, switch to the tab “Review”, where in the tool group “Protection” choose a function “Protect Sheet”.
4. In the window that appears, leave the standard settings, enter the password (it will be required later to remove the sheet protection) and click OK.
5. In the confirmation window that appears next, enter the previously set password again and click OK.
6. As a result, we managed to hide the formulas. Now, when you select protected cells, the formula bar will be empty.

Note: After activating sheet protection, when you try to make any changes to protected cells, the program will issue an appropriate informational message.

At the same time, if we want to leave the possibility of editing for some cells (and selection – for method 2, which will be discussed below), marking them and going to the formatting window, uncheck “Protected cell”.

For example, in our case, we can hide the formula, but at the same time leave the ability to change the quantity for each item and its cost. After we apply sheet protection, the contents of these cells can still be adjusted.

Method 2. Disable cell selection

This method is not as commonly used as compared to the one discussed above. Along with hiding information in the formula bar and prohibiting editing of protected cells, it also implies a prohibition on their selection.

1. We select the required range of cells in relation to which we want to perform the planned actions.
2. We go to the formatting window and in the tab “Protection” check if the option is checked “Protected cell” (should be enabled by default). If not, put it and click OK.
3. The tab “Review” click on the button “Protect Sheet”.
4. A familiar window will open for selecting security options and entering a password. Uncheck the box next to the option “highlight blocked cells”, set the password and click OK.
5. Confirm the password by typing it again, then click OK.
6. As a result of the actions taken, we will no longer be able not only to view the contents of the cells in the formula bar, but also to select them.

Conclusion

Thus, there are two methods to hide formulas in an Excel spreadsheet. The first involves protecting cells with formulas from editing and hiding their contents in the formula bar. The second one is more strict, in addition to the result obtained using the first method, it imposes a ban, in particular, on the selection of protected cells.