Excel Trick Playbook: How to Display or Fully Obscure Formulas Within Cells for Excel 2013

If you are working on an Excel worksheet with a lot of formulas in it, it may become difficult to follow and keep track of all your formulas. Excel provides a simple way of displaying formulas in the cells in addition to the formula bar.

This feature also displays the dependencies for each formula in the cells (when selected), so you can track the data being used in each calculation. Displaying formulas in cells helps you to find cells containing formulas and to quickly read through all your formulas and check for errors. You can also print the spreadsheet with the formulas in the cells to help check your work.

To display formulas in cells containing them, press the Ctrl + ` (the grave accent key). The formulas in each cell display as shown in the image above. The cells involved in the calculation are bordered in colors that match the cell references in the formula to help you track the data.

You can also click Show Formulas in the Formula Auditing section of the Formulas tab to display formulas in the cells.

Even if you don’t show formulas in the cells, when you click on a cell containing a formula, the formula displays in the formula bar. If you don’t want the formulas visible to users of your spreadsheet, you can hide them and protect the sheet. To do this, select the cells whose formulas you want to hide.

In the Cells section of the Home tab, click Format and select Format Cells from the drop-down menu.

The Format Cells dialog box displays. On the Protection tab, select the Hidden check box. Click OK.

To finish hiding the formulas, you must protect the sheet. Click Format in the Cells section of the Home tab again. This time, select Protect Sheet from the drop-down menu.

On the Protect Sheet dialog box, make sure the Protect worksheet and contents of locked cells check box is selected. Enter a password in the Password to unprotect sheet edit box that will allow you to unprotect the sheet and show the formulas again. In the Allow all users of this worksheet to list box, select the check boxes for the tasks you want to allow the users to perform. Click OK.

Enter your password again in the Reenter password to proceed edit box on the Confirm Password dialog box and click OK.

Now, you’ll notice that when you select a cell containing a formula, the formula bar is empty.

To show the formulas in the formula bar again, click Format in the Cells section of the Home tab and select Unprotect Sheet from the drop-down menu.

Enter your password on the Unprotect Sheet dialog box and click OK.

All your formulas will be visible again when those cells are selected in the worksheet.