How To Display Formulas In Excel

  • Home
  • / 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 and the formula disappears from the cell and is replaced by the calculated result. So, basically, the question is how to show formulas in Excel? 

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. 

How to Display Formulas in Excel? 

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.

Let’s dig out different methods to show formulas in Excel; 

Method 1

In the Ribbon, select the Formulas Tab. Within the Formula Auditing Box, click on the option “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.

  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

    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 in Excel 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.

Displaying Calculated ResultsDisplaying Formulas

Displaying Formulas

Example: 

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

Printing Formulas:

Once the Excel Display Formulas 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. 

Column Width Increases 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.

Write your comment Here