Leading the way in Microsoft Office Development

How to Hide your Formulas?

By default if a worksheet is protected the formulas will still be visible.

If you have created formulas that you do not want other people to see you can easily hide them by protecting the worksheet.

Protecting your formulas can prevent accidental editing and viewing

Before protecting the worksheet you must first indicate if you want the formulas to be visible or not.

By default all the cells on your worksheets are locked and all the formulas will be visible.

(Format > Cells) Dialog Box

Whether the formulas are visible or not is controlled by an option in the (Format > Cells) dialog box.

Display this dialog box and select the Protection tab and the following is the default setting.

Locked - Ticking this box means that the user will not be able to select the cells once the worksheet has been protected.

Hidden - Ticking this box means that the user will not see the cell formulas in the Formula Bar when the cell is selected.

Selecting the Cells

Therefore if you want to hide all the formulas on a particular worksheet you need to change the Hidden option for all the cells that contain formulas.

You could select the whole worksheet and change this option for every cell but a better approach would be just to change it for the cells that contain formulas.

You can quickly select all the cells that contain formulas by using the (Edit > GoTo > Special) and selecting the Formulas checkbox.

Select (Format > Cells)(Protection tab) and make sure the Hidden check box is selected.

You should also make sure that the Locked checkbox is also selected.

Protecting the Worksheet

Select (Tools > Protection > Protect Sheet) to display the Protect Sheet dialog box.

You can accept the default options otherwise you wont be able to select any of the cells.

The following is an example of a cell F2 which has been locked with its hidden property selected.

Things to Remember

You can quickly select all the cells that contain formulas by using the (Edit > GoTo > Special) and selecting the Formulas checkbox.

If you want to protect a few formulas without protecting the whole sheet select the cells and choose (Data > Validation), Select custom in the first window and type (="") in the second window.

If you only want your formulas visible in a few cells it is quicker to select the Hidden property for all the cells first and then just remove it from the cells whose formulas you want displayed.