Your spreadsheet formulas are always shown in the Formula Bar of Excel. This makes it easy to view and edit the formulas of a spreadsheet.
But what if we have created an Excel file and we do not want others to be able to view the formulas. Prevent formulas showing in the Formula Bar by setting them to hide and applying worksheet protection.
Before you apply worksheet protection however, you will need to specify the cells in which the formulas will be hidden.
Format the Cells to Hide the Formulas
- Select the cells where you want to prevent the formulas from being displayed.
- Click the Dialog Box Launcher arrow in the corner of the Number group on the Home tab, or press Ctrl + 1.
- Select the Protection tab.
- Check the Hidden box and click Ok.
Apply Protection to Prevent Formulas Showing in the Formula Bar
Now that we have selected which formulas we would like to hide, we need to enforce this rule by protecting the sheet.
- Click the Review tab on the Ribbon.
- Click the Protect Sheet button.
- The password is optional and its use depends on how restrictive you want to be. The list of checkboxes enables you to select what a user can or cannot do on this worksheet. Make the necessary changes and click Ok.
Now when you select a cell containing a formula. It will not be visible in the Formula Bar anymore.
Hi
I find your tutorials are easy to follow. Could you possibly put a tutorial on how to hide formulas and the same time enter data.
Thanks Stephen. I have a tutorial on this here – http://www.computergaga.com/blog/prevent-formulas-showing-in-the-formula-bar/
Hi,
Is this possible to protect formula as well as data entry option without unprotecting the sheet? In your showing option I can’t make data entry without unprotecting the whole sheet.
Sure. You need to unlock the cells which you need the data entry for first and then protect the sheet. Cells that are locked get protected, cells that are not don’t.
The tutorial shows using Format Cells to unlock cells before protecting a sheet.