You are reading the article How To Show Formulas In Excel Instead Of The Values updated in October 2023 on the website Cersearch.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested November 2023 How To Show Formulas In Excel Instead Of The Values
As soon as you type a formula in Excel and hit enter, it would return the calculated result, and the formula would disappear.
That’s how it’s supposed to work.
But what if you want to show formulas in the cells and not the calculated values.
In this Excel tutorial, I will cover the following topics:
How to Show Formulas in Excel instead of the values.
How to Print the formulas in Excel.
How to Show Formulas in Excel in Selected Cells Only.
What to Do when Excel Shows Formulas Instead of the Calculated Values.
Show Formulas in Excel Instead of the ValuesHere are the steps to show formulas in Excel instead of the value:
You can also use the Excel keyboard shortcut – Control + ` (you will find this key in the top-left part of the keyboard, under the Escape key).
Note: This is a sheet level technique. This means that when you use the Show Formulas option or the shortcut, it will only show the formulas in the active sheet. All the other worksheets will be unaffected. To show formulas in other worksheets, you will have to go to that sheet and use this shortcut (or ribbon button).
In some cases, you may have a lot of worksheets and you want to show the formulas in all the worksheets in the workbook.
Here are the steps that will show the formulas in all the worksheets in Excel:
Go to the ‘File’ tab. If you’re using Excel 2007, go to Office button.
In the left pane, select Advanced.
On the right, scroll down to the ‘Display options for this worksheet’ section.
From the drop down, select the worksheet in which you want to show the formulas instead of values.
Check the option – ‘Show formulas in cells instead of their calculated results’.
As mentioned, while this may seem to have more steps as compared to a shortcut or the ‘Show Formulas’ button in the ribbon, it’s useful when you have multiple worksheets and you want to show the formulas in all these worksheets.
Also read: Why Are Formulas Not Working In Excel How to Print Formulas in ExcelHere are the steps to print formulas in Excel:
Go to Formula tab.
The above steps would ensure that it prints the formulas and not the values.
Show Formulas in Excel Instead of the Value in Selected Cells OnlyThe above methods covered so far would show all the formulas in a worksheet.
However, you may want to show the formulas in some selected cells only.
For example, as an Excel trainer, I often create templates where I show the formula in one cell and its result in another cell (as shown below).
Here are the steps to show formulas in Excel in selected cells only:
Select the cell where you want to show the formula instead of the value.
In the Find and Replace dialog box, within the replace tab, enter = in the ‘Find what’ field and ‘= in the ‘Replace with’ field.
This will show formulas in all the selected cell while the remaining cells would remain unchanged.
Note: Entering a space before the formula makes it a text string and the space character is visible before the equal to sign. On the other hand, using an apostrophe before the equal to sign make the formula a text string, however, the apostrophe isn’t visible in the cell (it shows up only in the formula bar and in the edit mode).
How to Handle Excel Showing Formulas Instead of Calculated ValuesSometimes, you may find that the cells in Excel are showing the formula instead of the value.
There are a couple of reasons why this may happen:
It could be due to the presence of a space character or apostrophe before the equal to sign in the formula. The presence of these before the equal to sign makes the cell format as text and the formula shows up instead of the value. To handle this, simply remove these. You can use find and replace to do this.
If a cell has ‘Text’ formatting applied to it and you enter the formula and hit enter, it will continue to show the formula instead of the calculated value. To fix this issue, go to the Home tab and with the Number group, change the formatting to General.
You May Also Like the Following Excel Tutorials:
You're reading How To Show Formulas In Excel Instead Of The Values
Update the detailed information about How To Show Formulas In Excel Instead Of The Values on the Cersearch.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!