extra-credit-header-2018

Various ways to display formulas in Excel

By Wendy Tietz, CPA, Ph.D.; Jennifer Cainas, CPA, DBA,; and Tracie Miller-Nobles, CPA

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.

show-formulas


This will show all formulas in the Excel worksheet.

fill-table


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.

on-off


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

formulatext
filltable-2


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 Courtney.Vien@aicpa-cima.com.

SPONSORED WHITE PAPER

Preparing the statement of cash flows

This instructive white paper outlines common pitfalls in the preparation of the statement of cash flows, resources to minimize these risks, and four critical skills your staff will need as you approach necessary changes to the process.

RESOURCES

Keeping you informed and prepared amid the COVID-19 crisis

We’re gathering the latest news stories along with relevant columns, tips, podcasts, and videos on this page, along with curated items from our archives to help with uncertainty and disruption.