How to Hide Formulas in Microsoft Excel

 

Hide Formulas

There are two things you need to do to hide a formula. First, you must apply the “Hidden” setting to the cell and then protect the worksheeet.

Select the cell for which you want to hide the formula and right-click on the selected cell. Select “Format Cells” from the popup menu.

NOTE: You can select multiple cells and hide the formulas for all the selected cells.

Click the “Protection” tab on the “Format Cells” dialog box, and select the “Hidden” check box so there is a check mark in the box. Click “OK” to accept the change and close the dialog box.

The formulas won’t be hidden until you protect the sheet. To do this, click the “Review” tab and click “Protect Sheet” in the “Changes” section.

The “Protect Sheet” dialog box displays. To prevent other users from unhiding the formulas, enter a password in the “Password to unprotect sheet” edit box. Specify the actions you want to allow users to perform on the worksheet by selecting check boxes in the “Allow all users of this worksheet to” list. Click “OK” to accept your changes and close the dialog box.

NOTE: The password is not required, but we recommend you enter one if you don’t want other users to be able to unprotect the worksheet.

A dialog box displays asking you to confirm your password.

The formulas in the selected cells do not display in the Formula Bar, but the results of the formulas remain visible in the cells. If you entered a password when protecting the sheet, other users will not be able to use the “Show Formulas” button on the “Formulas” tab to display all the formulas in the worksheet.

To unhide the formulas, click “Unprotect Sheet” in the “Changes” section of the “Review” tab.

If you entered a password when protecting the sheet, the “Unprotect Sheet” dialog box displays, prompting you for the password. Enter the password in the “Password” edit box and click “OK”. If you didn’t enter a password when protecting the sheet, the sheet will be unprotected with no further prompts.

Select the cells for which you hid the formulas, right-click on the cells, and select “Format Cells” from the popup menu. Deselect the “Hidden” option on the “Protection” tab of the “Format Cells” dialog box so there is NO check mark in the box.

Another way to hide formulas from other users is to convert the formulas to static values and save the workbook with a different filename. Then, distribute this new workbook to the users.