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 similarly exit this mode 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 long 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 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).
The ThemiddleShow Formulas shortcut shows cell values and formulas. To get back a formula result, press the shortcut key again.
Note. Whichever method you use above, Microsoft Excel will display all formulas of the current workbook. To display the formulas in other worksheets and workbooks, you’ll need to repeat each page’s process.
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 hidden, and 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, use any of the three methods to display formulas in cells. Then print the spreadsheet as you would typically 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, 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 an equal sign precedes space or parenthesis, Excel treats the cell contents as text and does not evaluate any formulas in the cell. To fix this, 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 want to protect your formulas from overwriting or editing them and even hide them from view.