Many users have encountered such a problem, when working with tables it may be necessary to view cells containing formulas to calculate data. While in regular mode, you can view the calculation result of this formula. To do this, the program has two ways to view formulas, that is the ability to view all formulas at once, then instead of a numeric value in cells, the formulas will be displayed and You can also set the formula to display as a string in a separate cell. In this step by step tutorial with pictures, I will show you how to display formulas in Microsoft Excel 2013.
How to display all formulas in Microsoft Excel
By default, when you enter a formula in a cell and press the Enter key, Excel immediately displays the calculated result. To display all formulas in the cells that contain them, use one of the following methods.
1. Option ” Show Formulas ” in Excel
Consider showing all formulas on Microsoft Excel 2013 worksheet. Start by going to the Formulas tab > Formula Auditing and clicking the Show Formulas button.
In this mode, instead of the values you will be shown all entered formulas. You can exit this mode in a similar way by clicking the Show Formulas button of Formula Auditing .
2. Options ‘ Show formulas in cells instead of their results ‘
In Excel 2010, Excel 2013, and Excel 2016, go to File> Options . In Excel 2007, click Office Button> Excel Options.
Select Advanced On the left pane, scroll down to the Display options for this worksheet and select the Show formulas in cells option instead of their results.
At first glance, this might seem like a longer way, but you may find it useful when you want to display formulas in several Excel worksheets, in currently open workbooks. In this case, you just need to select the sheet name from the drop-down list and check the option Display options for this worksheet for each sheet.
3. Excel keyboard shortcut for displaying formulas
The fastest way to see every formula in your Excel spreadsheet is to press the following shortcut:Ctrl + ` (The accent key ( ` ) is the furthest key to the left next to the number 1 key).
ThemiddleShow Formulas shortcutshows cell values and formulas. To get back a formula result, simply press the shortcut key again.
Note. Whichever method you use above, Microsoft Excel will display all formulas of current workbook. To display the formulas in other worksheets and workbooks, you’ll need to repeat the process for each individual page.
If you want to see the data used in formula calculations, use any of the above methods to display the formula in cells, then select the cell containing the formula in question and you will see The results are similar to the following:
Note. If you click on a cell with a formula, but the formula is not visible in the formula bar, the formula is most likely hiddenand the worksheet is protected.
How to print formulas in Excel
If you want to print formulas in your Excel spreadsheet instead of printing the calculated results of those formulas, just use any of the 3 methods todisplay formulas in cells, then print spreadsheet as you would normally print your Excel files ( File> Print).
The reason Excel displays formulas, not results
Have you ever happened to you that you enter a formula in a cell, press the Enter key, and Excel still displays the formula instead of the result? Don’t worry, your Excel is fine and we will fix that issue immediately.
In general, Microsoft Excel can display formulas instead of calculated values for the following reasons:
- You may have inadvertently activated the Show Formulas mode by clicking the corresponding button or pressing the CTRL + ` shortcut . To get back the calculated result, simply turn off the Show Formulas button or press CTRL + `again.
- You may have accidentally typed a space or single quotes ( ‘) before the equal sign in the formula:
When a space or parenthesis is preceded by an equal sign, Excel treats the cell contents as text and does not evaluate any formulas in the cell. To fix this, just remove the leading or single quote spaces.
- Before entering the formula in a cell, you may have set the cell’s format to Text . In this case, Excel also treats the formula as a regular text string and does not calculate it.
To fix this error, select the cell, go to Home tab > Number and set the cell’s format to General,and while in the cell, press F2 and ENTER.
Here’s how you display formulas in Excel. A piece of cake, right? On the other hand, if you plan on sharing your spreadsheets with other users, you may wantto protect your formulasfrom overwriting or editing them and even hide them from view.