It may be difficult to understand how each formula relates to the others if you are working on a spreadsheet that contains many formulas. The ability to trace the data used in each calculation and immediately check your calculations for errors is made possible by Excel formulas that are shown rather than their outputs.
You’ll soon see that Microsoft Excel offers a wonderfully simple and efficient way to display formulas in cells.
You will discover a simple method to display formulas in Excel in this tutorial. You will also learn how to print formulas and why Excel will occasionally display a formula rather than a result in a cell.
This Tutorial Covers:
- Show Formulas in Excel Instead of the Values
- How to Print Formulas in Excel
- In Excel, display formulas rather than just the value of the currently selected cells
- How to Deal With Excel Displaying Formulas Rather Than Calculated Values
- Show Formulas in Excel Instead of the Valuescv
Step 1: The “Formulas” Tab will appear in the ribbon. Select “Show Formulas” from the “Formula Auditing” group.
The formulas in the worksheet will become visible as soon as you click Show Formulas.
Since it is a toggle button, you can click on it once more to have the computed result take the place of the formulas.
The Excel keyboard shortcut Control +’is another option (you will find this key in the top-left part of the keyboard, under the Escape key).
Note: This is a sheet level technique, please note. As a result, only the formulae in the active sheet will be displayed when you use the shortcut or option to Show Formulas. The rest of the worksheets won’t be impacted. You must access that worksheet and utilize this shortcut to display formulas in other worksheets (or ribbon button).
You might wish to display the formulas in every worksheet in a workbook if you have a lot of worksheets.
The following procedures will display all of Excel’s worksheets’ formulas:
Step 1: Select the “File” tab. Go to Office by clicking the Excel 2007 button.
Step 2: Toggle to “Options.”
Step 3: Select Advanced from the left pane. Scroll down to the “Display options for this worksheet” box on the right. Choose the worksheet you wish to display the formulae rather than the values from the drop-down menu.
Step 4: Select “Show formulas in cells instead of their calculated outcomes” from the drop-down menu. Select OK.
As previously stated, even though it may appear like there are more steps than a shortcut or the “Show Formulae” button in the ribbon, this method is helpful if you have numerous worksheets and want to display the formulas in each worksheet.
2. How to Print Formulas in Excel
The steps to print formulas in Excel are as follows:
Step 1: The “Formulas” Tab will appear in the ribbon. Select “Show Formulas” from the “Formula Auditing” group.
Step 2: Select the “File” tab then go to “Print” option.
The aforementioned steps would guarantee that the formulas and not the values are printed.
3. In Excel, display formulas rather than just the value of the currently selected cells
All of the worksheet’s formulas would be visible using the methods discussed so far.
However, you could simply want to display the formulas in a few certain cells.
As an Excel trainer, for instance, I frequently develop templates that display the formula in one cell and the result in another cell (as shown below).
The steps to only display formulas in particular cells in Excel are as follows:
Step 1: Choose the cell where you wish the formula to appear rather than the value.
Step 2: Go to the “Home” tab and select “Find & Select”. After that, choose “Replace” (keyboard shortcut – Control + H).
Step 3: Put = and ‘= in the “Find what” and “Replace with” fields, respectively, of the replace tab in the Find and Replace dialog box. Simply select Replace All.
This will display formulas in every cell in the selected cell while leaving the other cells alone.
Note: The space character is seen before the equal to sign when the formula is entered with a space before it to make it a text string. The apostrophe isn’t displayed in the cell when the equal to sign is preceded by an apostrophe, which turns the formula into a text string (it shows up only in the formula bar and in the edit mode).
4. How to Deal With Excel Displaying Formulas Rather Than Calculated Values
In Excel, the cells occasionally display the formula rather than the value.
There are a few causes for this possibility. They are:
- It’s possible that you accidentally pressed the Control + shortcut or that the “Show Formulas” feature is active. Simply use the shortcut once more or select the “Show Formula” option under the Formulas tab to make it inactive.
- It can be because the formula contains a space or an apostrophe before the equal to sign. When these are present before the equal to sign, the cell assumes a text format and the formula appears in place of the value. Simply take these out to manage this. To accomplish this, utilize find and replace.
- When you insert a formula into a cell that has “Text” formatting applied and press “Enter,” the formula is shown in place of the calculated value. Go to the Home tab and switch the formatting for the Number group to General to resolve this problem.
These are a few methods to show Excel formulas.
For ready-to-use Dashboard Templates:
