# 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.

- Switch between displaying formulas and their values on a worksheet
- Copy a formula to another cell to be displayed as text
- Prevent a formula from being displayed in the formula bar
- Show formulas that were previously hidden by removing protection

## 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.

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

## 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.

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

## Prevent a formula from being displayed in the formula bar

This procedure also prevents the formula from being edited.

- 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.
- On the
**Home**tab, in the**Cells**group, click**Format**, click**Format Cells**, and then click the**Protection**tab.

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

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

## Show formulas that were previously hidden by removing protection

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

- Select the range of cells whose formulas you want to unhide.
- On the
**Home**tab, in the**Cells**group, click**Format**, click**Format Cells**, and then click the**Protection**tab.

- Clear the
**Hidden**check box.