How to show formulas in Excel

This tutorial shows you how to easily display formulas in Excel (versions 2016, 2013, 2010, and older). Learn how to print formulas and troubleshoot why Excel sometimes displays a formula instead of the result.

Working with spreadsheets containing numerous formulas can be confusing. Displaying formulas instead of results helps track data usage in calculations and quickly identify errors. Excel offers simple ways to achieve this.

Methods to Display Formulas:

1. Using the Excel Ribbon:

Navigate to the Formulas tab > Formula Auditing group, and click the Show Formulas button. This instantly toggles between displaying formulas and results.

2. Excel Options:

In Excel 2010 and later, go to File > Options (Excel 2007: Office Button > Excel Options). Select Advanced, scroll to "Display options for this worksheet," and check Show formulas in cells instead of their calculated results. This method is useful for multiple sheets within a workbook; select the sheet from the dropdown to apply the setting individually.

3. Keyboard Shortcut:

The quickest method is using the shortcut: Ctrl ` (the grave accent key, located next to the 1 key). This toggles the display between formulas and results.

Note: These methods affect only the current worksheet. Repeat for other sheets. Selecting a cell with a visible formula will display its components, as shown below:

Tip: If a formula doesn't appear in the formula bar, it might be hidden and the sheet protected. Unhide and remove protection to view it.

Printing Formulas:

To print formulas instead of results, use any of the above methods to display formulas, then print the worksheet as usual (File > Print).

Troubleshooting: Formula Displayed Instead of Result:

If Excel shows a formula instead of its result:

1. Show Formulas Mode: Check if "Show Formulas" is accidentally activated (ribbon button or Ctrl ` shortcut). Toggle it off.
2. Leading Space or Single Quote: A space or single quote (') before the equals sign (=) treats the cell as text. Remove the leading character.

3. Text Formatting: If the cell is formatted as "Text," Excel interprets the formula as text. Select the cell, go to Home > Number, set the format to General, press F2, and then ENTER.

This concludes the guide on displaying formulas in Excel. For information on protecting and hiding formulas, stay tuned for the next article!

The above is the detailed content of How to show formulas in Excel. For more information, please follow other related articles on the PHP Chinese website!