Which of the following keyboard shortcuts displays formulas instead of results?

Almost everybody knows that pressing the F9 key in Excel recalculates all of the worksheets within all of the workbooks that a user has open. What many people don’t know, however, is that F9 can also be used to understand and debug formulas.

In Excel there are typically many ways to perform the same task, so it should be no surprise that there are multiple ways to evaluate formulas.

The quickest way to evaluate a formula in Excel is to press CTRL + ~ (tilde). This toggles the display of the current worksheet, allowing you to switch views between cell values and cell formulas.  This is great for simply understanding how a formula works or for finding out the positioning of a #REF! error – especially over an area containing multiple cells.

If you require additional insight into how your formula or subset of your formula is working, you can evaluate the formula on a more granular level using Excel’s Evaluate Formula feature (Alt + M + V).  This allows you to evaluate the formula on a cell by cell basis.  However, there are two key limiting factors to this feature: (i) you can only evaluate the formula from left to right, and (ii) edits cannot be made directly within the Evaluate Formula dialog box.

To overcome these limitations, you can use the F9 key to evaluate your formula or subset of your formula.  To do this, you must first press the F2 key on your desired cell to activate “Edit” mode (see our prior keyboard shortcut on the Double F2 key).

Once you are in “Edit” mode, you have the flexibility to highlight any part of your formula to evaluate it, so long as it could have been evaluated as a stand-alone formula.  Once highlighted, pressing the F9 key calculates the highlighted portion and simply displays its value or result.  This can be helpful for understanding how complex formulas are working, debugging why complex formulas are not working as expected, or for determining the source positioning of any error value.

Keep in mind two key things when evaluating a formula with “Edit” mode and the F9 key:

• Be sure to highlight parts of your formula while in “Edit” mode before pressing F9, otherwise the entire formula will be simplified to its calculated value
• When you are finished evaluating your formula:
  • Pressing the Escape key will exit “Edit” mode and return the cell to its original formula
  • Pressing the Enter key substitutes any of the calculated values as hardcodes into the original formula

Choosing a method to evaluate your formulas depends entirely upon your desired outcome.  Evaluating a formula using “Edit” mode and the F9 key not only allows you to understand and debug the formula on a granular level, but also gives you the added flexibility to evaluate any part of the formula in any order of your choosing.  By using “Edit” mode and the F9 key to evaluate your formulas, you should be able to further minimize the number of errors contained in your spreadsheets – increasing user confidence in your models… and you.

To see an example of how to utilize “Edit” mode and the F9 key to evaluate a formula, continue reading below.

There are a number of different ways that you can display formulas in Excel. This can be useful when investigating why a certain formula isn’t working correctly or when grading your students’ worksheets to ensure they are using formulas correctly.

Display formulas using the Show Formulas command

To show formulas in Excel, you can click on the Formulas tab and then click Show Formulas.

This will show all formulas in the Excel worksheet.

Toggle formulas on and off

You can also use a keyboard shortcut command to toggle formulas on and off. On your keyboard, typing the shortcut command Ctrl+`, will toggle on (or show) the formulas. (Note that the ` in this shortcut is the grave accent mark, usually located to the left of the 1 key on your keyboard.) Typing the shortcut command Ctrl+` again will toggle off the formulas.

Display formulas using the FORMULATEXT function

Occasionally you might want the formula to be displayed as a text string next to the formula. This can be especially useful when you want to see both the result of the formula and the formula on the same worksheet, or when introducing formulas to students. In these cases, you can use the FORMULATEXT function.

The FORMULATEXT function returns a formula as a text string, as seen in the screenshot below. In that example, if you wanted to show the formula of cell D3 in cell F3, you would input the following formula: =FORMULATEXT(D3).

There are a couple of important items to be aware of when using the FORMULATEXT. First, note that you can use it to reference a cell in another worksheet or workbook, but the workbook must be open. Otherwise, Excel will return an #N/A error value.

Excel will also return an #N/A error value if you use FORMULATEXT on a cell that doesn’t contain a formula, or if the formula can’t be displayed due to worksheet protection.

— Wendy Tietz, CPA, CGMA, Ph.D., is a professor of accounting at Kent State University in Kent. Ohio; Jennifer Cainas, CPA, DBA, is an instructor of accountancy at the University of South Florida in Tampa; and Tracie Miller-Nobles, CPA, is an associate professor of accounting at Austin Community College in Austin, Texas. See their site AccountingIsAnalytics.com for resources they have developed for teaching data analytics in introductory accounting. To comment on this article or to suggest an idea for another article, contact senior editor Courtney Vien at [email protected].

What is the shortcut key to show formulas?

What is the keyboard shortcut to display or hide formulas?

Why does Excel show formulas instead of results?