Protect and Hide Your Formulas

Protect and Hide Your Formulas

When a cell contains a formula, the formula is visible for all to see. Just activate the cell and glance up at the Formula bar. In some cases, you many want to hide your formulas to give your worksheet a cleaner look--or to keep others from seeing how your calculations are done.

You can hide the formulas by setting the Hide option for the formula cells and protecting the sheet. But before you protect the sheet, you need to "unlock" all non-formula cells (by default, all cells are locked).

Here's how to hide the formula cells:

1. Select Edit, Go To (or press Ctrl-G or F5) to bring up the Go To dialog box.

2. Click the Special button to show the Go To Special dialog box.

3. Choose the Formulas option, and make sure the four check boxes are all checked.

4. Click OK, and Excel selects all cells that contain a formula.

5. Choose Format, Cells, and click the Protection tab.

6. Place a check mark next to the Hidden option, and make sure that there is a check mark next to the Locked option. Click OK.

Here's how to unlock the non-formula cells:

1. Repeat Steps 1 and 2 above to show the Go To Special dialog box.

2. Choose the Constants option, and make sure the four check boxes are all checked.

3. Click OK, and Excel selects all non-empty cells that don't contain a formula.

4. Choose Format, Cells, and click the Protection tab.

5. Remove the check mark next to the Locked option. Click OK.

At this point, the formula cells are set to Hide, and the non-formula cells are set to Unlock. But these settings have no effect unless the sheet is protected. Choose Tools, Protection, Protect Sheet to protect the worksheet. You can enter a password if you like.

After performing these steps, you can select any formula cell, and the formula will not be displayed in the Formula bar. Furthermore, the formula cannot be changed. But the cells that contain other information can be changed.

It's important to understand that protecting a worksheet--even if you use a password--is a very weak form of security. Password-cracking utilities are widespread on the Internet.