Protect or audit formulas

For security or privacy reasons, you may want to hide a formula from users of your worksheet. Or you may be auditing formulas and you need to see some or all of the formulas on the worksheet.

See the topics Overview of formulas and Create or delete a formula to learn more about creating and working with formulas.

What do you want to do?


Switch between displaying formulas and their values on a worksheet

You can easily inspect all the formulas on a worksheet at the same time by switching between a view that shows formulas in their cells and the default view that shows the values of formulas in their cells. This affects all formulas on the worksheet.

  1. Press CTRL + ` (grave accent).
  2. To switch back, press CTRL + ` again.

Top of Page Top of Page

Copy a formula to another cell to be displayed as text

You can "comment" a formula by copying it to an adjacent cell and inserting an apostrophe at the beginning of the formula (commenting is a practice that is widely used in programming to explain the purpose of one or more lines of code). An apostrophe at the beginning of a cell forces Excel to recognize its contents as a text string and, in the case of a formula, removes its functionality.

The following picture shows a cell (C2) containing a formula that is preceded by an apostrophe. The formula uses the IF function to evaluate the value in cell A2 (87), and determine if it passes a test (>=70). The actual formula is contained in cell B2 and displays "P" because the value passes the test that is specified in the formula.

An apostrophe deactivates the formula

To learn how to copy a formula to another cell on a worksheet, see Move or copy a formula.

Top of Page Top of Page

Prevent a formula from being displayed in the formula bar

This procedure also prevents the formula from being edited.

  1. Select the range of cells whose formulas you want to hide. You can also select nonadjacent ranges (nonadjacent selection: A selection of two or more cells or ranges that don't touch each other. When plotting nonadjacent selections in a chart, make sure that the combined selections form a rectangular shape.) or the entire sheet.
  2. On the Home tab, in the Cells group, click Format, click Format Cells, and then click the Protection tab.

Excel Ribbon Image

  1. Select the Hidden check box.
  2. Click OK.
  3. On the Home tab, in the Cells group, click Format, and then click Protect Sheet.

Excel Ribbon Image

  1. Make sure that the Protect worksheet and contents of locked cells check box is selected.

Top of Page Top of Page

Show formulas that were previously hidden by removing protection

  1. On the Review tab, in the Changes group, click Unprotect Sheet.

Unprotect Sheet command in the Changes group

  1. Select the range of cells whose formulas you want to unhide.
  2. On the Home tab, in the Cells group, click Format, click Format Cells, and then click the Protection tab.
  1. Clear the Hidden check box.

Top of Page Top of Page

 
 
Applies to:
Excel 2007