Show or Hide Formulas in Excel and Google Sheets

01
of 01

Show/Hide Formulas in Excel and Google Sheets Using Shortcut Keys

Show Formulas in Excel and Google Spreadsheets Using Shortcut Keys
Show Formulas in Excel and Google Spreadsheets Using Shortcut Keys. © Ted French

Normally, cells containing formulas in Excel and Google Sheets display the answers to all formulas and functions located in the worksheet.

In large worksheets, clicking around with the mouse pointer to find the cells containing these formulas or functions can be a hit or miss operation.

Show All Formulas Shortcut

Remove the guesswork when finding formulas by using a shortcut key combination to show all formulas in Excel and Google Sheets:

Ctrl + `  (grave accent key)

On most standard keyboards, the grave accent key is located next to the number 1 key on the top left corner of the keyboard. It looks like a backward apostrophe.

This key combination works as a toggle key, which means you press the same key combination again to hide the formulas when you are finished viewing them.

Steps to Showing All Formulas

  1. Press and hold down the Ctrl key on the keyboard.
  2. Press and release the grave accent key on the keyboard without releasing the Ctrl key.
  3. Release the Ctrl key.

The worksheet should display all formulas in their worksheet cells rather than the formula results.

Re-Hiding the Formulas

To show the results again instead of the formulas, press the Ctrl + ` keys once more.

About Show Formulas

  • Show formulas does not change the worksheet contents, only the way they are displayed.
  • Show formulas makes it easy to find cells containing formulas.
  • It allows you to quickly read through all the formulas to check for errors.
  • The feature can also be activated by selecting:
    • Formulas > Show Formulas on the ribbon in Excel
    • View > All Formulas option in the menus in Google Sheets
  • Printing worksheets with Show Formulas turned on can make it easier to search for hard to find errors.

Show Individual Worksheet Formulas

Rather than viewing all formulas, it is possible to view formulas one at a time simply by:

  • Double-clicking on the cell containing the formula with the mouse.
  • Clicking once on the cell containing the formula with the mouse and then pressing the F2 key on the keyboard.

Both of these actions place the program—either Excel or Google Sheets—into edit mode, which displays the formula in the cell and outlines in color the cell references used in the formula. This makes it easier to trace data sources used in a formula.

Hide Formulas in Excel Using Protect Sheet

Another option for hiding formulas in Excel is to use worksheet protection, which includes an option to prevent formulas in locked cells from being displayed in these locations:

  • In the cell where they are located
  • In the formula bar when a cell containing a formula is clicked on
  • When Show Formulas is activated either using the keyboard shortcut or through the ribbon.

Hiding formulas, like locking cells, is a two-step process that involves identifying the range of cells you want to hide and then applying worksheet protection.

Select the Cell Range to Hide

  1. Select the range of cells containing the formulas to be hidden.
  2. On the Home tab of the ribbon, click on the Format button to open the drop-down menu.
  3. In the menu, click on Format Cells to open the Format Cells dialog box.
  4. In the dialog box, click on the Protection tab.
  5. On this tab, select the Hidden check box.
  6. Click OK to apply the change and close the dialog box.

Apply Worksheet Protection

  1. On the Home tab of the ribbon, click on the Format option to open the drop-down menu.
  2. Click on Protect Sheet option at the bottom of the list to open the Protect Sheet dialog box.
  3. Check or uncheck the desired options.
  4. Click OK to apply the changes and close the dialog box.

At this point, the selected formulas should be hidden from view in the formula bar. Until the second step is carried out, the formulas remain visible in the worksheet cell and in the formula bar.

Format
mla apa chicago
Your Citation
French, Ted. "Show or Hide Formulas in Excel and Google Sheets." ThoughtCo, Jul. 12, 2017, thoughtco.com/show-hide-formulas-in-excel-and-google-spreadsheets-3123884. French, Ted. (2017, July 12). Show or Hide Formulas in Excel and Google Sheets. Retrieved from https://www.thoughtco.com/show-hide-formulas-in-excel-and-google-spreadsheets-3123884 French, Ted. "Show or Hide Formulas in Excel and Google Sheets." ThoughtCo. https://www.thoughtco.com/show-hide-formulas-in-excel-and-google-spreadsheets-3123884 (accessed January 24, 2018).