• ARTICLES
SEARCH

How-To Geek

How to Show Formulas in Cells and Hide Formulas Completely in Excel 2013

00_lead_image_cell_and_formula

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.

02_clicking_show_formulas

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.

03_selecting_cells

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

04_selecting_format_cells

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

05_selecting_hidden

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.

06_selecting_protect_sheet

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.

07_protect_sheet_dialog

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

08_confirm_password_dialog

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

09_no_formula_showing

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.

10_selecting_unprotect_sheet

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

11_unprotect_sheet_dialog

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

Lori Kaufman is a freelance technical writer who likes to write geeky how-to articles to help make people's lives easier through the use of technology. She loves watching and reading mysteries and is an avid Doctor Who fan.

  • Published 11/22/13

Get Free Articles in Your Inbox!

Join 134,000 newsletter readers

Email:

Go check your email!