Display Formulas 1

How to display formulas in excel ,Formulas entered in a cell are typically displayed in the formula bar and within the cell itself. However, as soon as the formula is complete , You hit enter the formula disappears from the cell and is replaced by the calculated result.

 When working with a vast array of formulas that are embedded within one another ,It can be difficult to keep track of what formulas are used or find errors within the formulas. Excel has a feature to display the formulas in cells versus showing the calculated results that will ease the development of a multi-formulated worksheet.

  Method 1 

In the Ribbon, select the Formulas Tab. Within the Formula Auditing Box click on Show Formulas. This will automatically turn on all formulas entered within the worksheet making them visible without having to click on the cell they are entered in. To hide the formulas and show the calculated results, click Show Formulas again to turn them off.

Display Formulas 2

  Method 2 

  1. In the File Tab click Options.

  2. Go to the Advanced Options Menu and scroll down to Display options for this worksheet.

  3. If not already displayed, select the worksheet from the drop-down menu that you wish to format.

  4. Check the box next to Show Formulas in cells instead of their calculated results.

  5. Click OK

This method is best when dealing with multiple worksheets within a workbook as you can select each worksheet from the drop-down menu and turn on the Display Formulas feature in each one. Displaying the formulas in several worksheets is highly useful if you are using formulas across worksheets. For example, worksheet 1 may draw the SUM from worksheet 2 into an IF Statement. In this case you can toggle back to worksheet 2 and easily double check your formulas to ensure the formula in worksheet 1 is properly entered.

  Method 3

The fastest way to Display Formulas is using the keyboard shortcut Ctrl + `  that is holding down the Control Key and the accent key at the same time. Once again, all formulas will be displayed rather than their calculated results. To turn the Display Formulas feature off, use the shortcut keys again.

Display Formulas  3

Displaying Calculated Results

Display Formulas 4

Displaying Formulas

Example: 

If the Display Formula is turned off you would have to click in each cell to view the formula in the formula bar. Displaying the Formulas allows you to quickly scan Row 9 to check for errors, add formulas, embed formulas or adjust the formula. 

Printing Formulas:

Once the Display Formula feature has been turned on you can print the worksheet or workbook and any of the formulas displayed will print. When dealing with a heavily formulated worksheet it may help to print one copy with the formulas displayed and one with the calculated results to help explain how the results were generated.

Formulas not being displayed:

Make sure the worksheet is not protected by selecting the Review Tab from the menu and clicking Unprotected Sheet. In the future if you do not want the formulas hidden when Protecting the Sheet, right click the cell with the formula entered and select Format Cells from the menu. Go to the Protection Tab and unselect Hidden. You may now Protect the worksheet and display the formulas. 

Note about column width increase from displaying formulas

When you display formulas the columns expand, it’s important to remember that this is just for display purposes so you can see the formulas. Excel isn’t actually expanding the columns so don’t think you need to re-adjust the column widths.